Thread: BUG #1862: ECPG Connect, host variable trailing blanks
The following bug has been logged online: Bug reference: 1862 Logged by: James Gray Email address: jim.gray@bull.com PostgreSQL version: 8.0.3 Operating system: Red Hat Enterprise Linux AS release 4 (Nahant Update 1), Kernel 2.6.9-11.EL on an ia64 Description: ECPG Connect, host variable trailing blanks Details: We are processing a COBOL/SQL program, and feeding the results to ECPG as C/SQL (since Postgres does not yet have a Cobol/Sql preprocessor). The problem that we are having involves a connect statement with host variables: EXEC SQL CONNECT TO :target AS :user Our problem is that we are passed Cobol strings which are blank padded. Our string strategy works fine for Oracle, but not for Postgres CONNECTs. For example, if we are trying to connect to: - database: demo - user: scott - password: tiger the strings must be "demo", "scott" and "tiger". With trailing blanks user "scott" will not match user "scott ", which is what we will present if the user had defined the Cobol variable as PIC X(10). There does not seem to be an option for Postgres to ignore trailing blanks in the CONNECT host variables. This only applies to CONNECT host variables, since trailing blanks in a CHAR column are ignored in comparisons for all other interactions with Postgres. Since this is inconsistent behavior, and also doesn't match Oracle's behavior, we are requesting a fix or an option.
On Tue, Sep 06, 2005 at 09:02:47PM +0100, James Gray wrote: > The problem that we are having involves a connect statement with host > variables: > EXEC SQL CONNECT TO :target AS :user > > Our problem is that we are passed Cobol strings which are blank padded. > Our string strategy works fine for Oracle, but not for Postgres CONNECTs. > > For example, if we are trying to connect to: > - database: demo > - user: scott > - password: tiger > the strings must be "demo", "scott" and "tiger". > > With trailing blanks user "scott" will not match user "scott ", > which is what we will present if the user had defined the Cobol variable as > PIC X(10). In PostgreSQL, "scott" and "scott " are distinct identifiers, and both are valid. See "Identifiers and Key Words" in the "SQL Syntax" chapter of the documentation, especially the part that discusses quoted identifiers: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS Although creating databases, users, tables, etc., with trailing spaces is probably a bad idea, PostgreSQL does allow such names, and the trailing spaces are significant. > This only applies to CONNECT host variables, since trailing blanks in a > CHAR column are ignored in comparisons for all other interactions with > Postgres. > > Since this is inconsistent behavior, and also doesn't match Oracle's > behavior, we are requesting a fix or an option. Identifiers are NAME types, not CHAR types; the difference in behavior is no more inconsistent than that between VARCHAR and CHAR. If the strings have trailing spaces but the identifiers on the server side don't, then strip the spaces on the client side. -- Michael Fuhr
>Identifiers are NAME types, not CHAR types From the URL reference that you gave: UPDATE MY_TABLE SET A = 5; The tokens MY_TABLE and A are examples of identifiers. But I don't think it is legal SQL to state this as: UPDATE :V1 SET :V2 = 5; In other words, host variables are never considered identifiers in normal DML statements. According to the documentation at: http://www.postgresql.org/docs/8.0/static/ecpg-connect.html EXEC SQL CONNECT TO :target USER :user; The last form makes use of the variant referred to above as character variable reference. To be consistent with other uses of host variables, we request that host variables within a connect statement act like host variables in DML statements. Oracle seems to think this is the correct approach, and also "the format of the connection target is not specified in the SQL standard". Michael Fuhr <mike@fuhr.org> To 09/07/2005 12:15 James Gray <jim.gray@bull.com> AM cc pgsql-bugs@postgresql.org Subject Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks On Tue, Sep 06, 2005 at 09:02:47PM +0100, James Gray wrote: > The problem that we are having involves a connect statement with host > variables: > EXEC SQL CONNECT TO :target AS :user > > Our problem is that we are passed Cobol strings which are blank padded. > Our string strategy works fine for Oracle, but not for Postgres CONNECTs. > > For example, if we are trying to connect to: > - database: demo > - user: scott > - password: tiger > the strings must be "demo", "scott" and "tiger". > > With trailing blanks user "scott" will not match user "scott ", > which is what we will present if the user had defined the Cobol variable as > PIC X(10). In PostgreSQL, "scott" and "scott " are distinct identifiers, and both are valid. See "Identifiers and Key Words" in the "SQL Syntax" chapter of the documentation, especially the part that discusses quoted identifiers: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS Although creating databases, users, tables, etc., with trailing spaces is probably a bad idea, PostgreSQL does allow such names, and the trailing spaces are significant. > This only applies to CONNECT host variables, since trailing blanks in a > CHAR column are ignored in comparisons for all other interactions with > Postgres. > > Since this is inconsistent behavior, and also doesn't match Oracle's > behavior, we are requesting a fix or an option. Identifiers are NAME types, not CHAR types; the difference in behavior is no more inconsistent than that between VARCHAR and CHAR. If the strings have trailing spaces but the identifiers on the server side don't, then strip the spaces on the client side. -- Michael Fuhr
On Wed, Sep 07, 2005 at 12:06:10PM -0700, Jim.Gray@Bull.com wrote: > According to the documentation at: > http://www.postgresql.org/docs/8.0/static/ecpg-connect.html > > EXEC SQL CONNECT TO :target USER :user; > The last form makes use of the variant referred to above as character > variable reference. The complete text is: The last form makes use of the variant referred to above as character variable reference. You will see in later sections how C variables can be used in SQL statements when you prefix them with a colon. Note "C variables" -- it's talking about the C character type, not the SQL character type. > To be consistent with other uses of host variables, > we request that host variables within a connect statement > act like host variables in DML statements. They *do* act the same way: the value is passed to the server, which handles it according to the appropriate type's semantics. If the host variable's value is used in a CHAR context then trailing spaces aren't significant in comparisons; if the value is used in other contexts like VARCHAR, TEXT, or NAME, then trailing spaces *are* significant. The server makes the decision, not the ECPG preprocessor or library, which simply passes the value to the server. If you think this behavior should be changed then propose it on pgsql-hackers and discuss it with the developers. -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> If you think this behavior should be changed then propose it on
> pgsql-hackers and discuss it with the developers.
I doubt we'll consider changing it. If I haven't missed any of the
details, the critical points here are
(1) User names containing trailing (or embedded) spaces are legal.
Not a very good idea maybe, but legal both per SQL spec and per
our historical practice.
(2) Making ECPG strip trailing spaces would render it unable to
connect as such a user.
I don't think ECPG has any business deciding that trailing spaces are
insignificant. If that's the case in your application environment,
fine: strip the spaces at the application level. But don't expect a
general-purpose library to make that policy choice for you.
regards, tom lane
What do you think of an Postgres option that would enable stripping of trailing blanks from connect host variables when turned ON? This would allow current behavior to be supported by ECPG and the Postgres server, but allow applications that need the behavior (like a Cobol/SQL preprocessor for Postgres), to make use of it. When I searched the mail archives, I found that others had run into this same problem, in this case with an ODBC driver: http://archives.postgresql.org/pgsql-interfaces/1998-08/msg00000.php Tom Lane <tgl@sss.pgh.pa.us> 09/08/2005 09:15 PM To Michael Fuhr <mike@fuhr.org> cc Jim.Gray@Bull.com, pgsql-bugs@postgresql.org, Ken.Rosensteel@Bull.com Subject Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks Michael Fuhr <mike@fuhr.org> writes: > If you think this behavior should be changed then propose it on > pgsql-hackers and discuss it with the developers. I doubt we'll consider changing it. If I haven't missed any of the details, the critical points here are (1) User names containing trailing (or embedded) spaces are legal. Not a very good idea maybe, but legal both per SQL spec and per our historical practice. (2) Making ECPG strip trailing spaces would render it unable to connect as such a user. I don't think ECPG has any business deciding that trailing spaces are insignificant. If that's the case in your application environment, fine: strip the spaces at the application level. But don't expect a general-purpose library to make that policy choice for you. regards, tom lane
On Fri, Sep 09, 2005 at 11:02:31AM -0700, Jim.Gray@Bull.com wrote: > This would allow current behavior to be supported by ECPG and the Postgres > server, > but allow applications that need the behavior (like a Cobol/SQL > preprocessor for Postgres), > to make use of it. Which preprocessor are you using? I wonder if such a logic should better be implemented in the Cobol-C transition instead. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
We are working in a rather complex environment, where COBOL/SQL on a
legacy
mainframe has its SQL portions sent via a high speed link to a Unix box.
The SQL is translated to a C/SQL environment in the process and the
Postgres
ECPG preprocessor is used on the Unix side for interfacing with Postgres.
We are already doing a translation from trailing blank Cobol strings
to C null terminated strings in this process for the connection host
variables.
The reasons for requesting a Postgres option to strip trailing blanks in
this case are:
1) we are not the only project to run into the problem of Postgres being
oriented towards C strings
2) other RDBMS systems use the expected trimming of Cobol trailing blanks
3) when porting Oracle Cobol applications to Postgres, this option would
be helpful
4) it might be better to strip the trailing blanks in the application if
only one such application were involved,
but when there are several such applications, centralizing the
behavior in a Postgres option is preferrable
Michael Meskes <meskes@postgresql.org>
09/12/2005 04:06 AM
To
Jim.Gray@Bull.com
cc
Tom Lane <tgl@sss.pgh.pa.us>, Ken.Rosensteel@Bull.com, Michael Fuhr
<mike@fuhr.org>, pgsql-bugs@postgresql.org
Subject
Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks
On Fri, Sep 09, 2005 at 11:02:31AM -0700, Jim.Gray@Bull.com wrote:
> This would allow current behavior to be supported by ECPG and the
Postgres
> server,
> but allow applications that need the behavior (like a Cobol/SQL
> preprocessor for Postgres),
> to make use of it.
Which preprocessor are you using? I wonder if such a logic should better
be implemented in the Cobol-C transition instead.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot
(De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Jim.Gray@Bull.com wrote: > What do you think of an Postgres option that would enable stripping of > trailing blanks from connect host variables when turned ON? I would not support such an option. If we added flags for every single thing that someone wanted, the system would be unusable. > This would allow current behavior to be supported by ECPG and the > Postgres server, but allow applications that need the behavior (like > a Cobol/SQL preprocessor for Postgres), to make use of it. > > When I searched the mail archives, I found that others had run into > this same problem, in this case with an ODBC driver: > http://archives.postgresql.org/pgsql-interfaces/1998-08/msg00000.php Just because Oracle does it doesn't mean we should. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, Sep 16, 2005 at 12:00:59PM -0400, Bruce Momjian wrote: > Jim.Gray@Bull.com wrote: > > What do you think of an Postgres option that would enable stripping of > > trailing blanks from connect host variables when turned ON? > > I would not support such an option. If we added flags for every single > thing that someone wanted, the system would be unusable. [snip] > Just because Oracle does it doesn't mean we should. Does Oracle really munge data on the client side? Or does it, like PostgreSQL, pass the host variable's value as-is to the server, and the server considers trailing spaces significant or not depending on the context? Is it the client-side behavior or the server-side behavior that's different between PostgreSQL and Oracle? If Oracle strips trailing spaces on the client side, is that a configurable option? How would you insert significant trailing spaces into a VARCHAR column if the client library strips them? -- Michael Fuhr
>If Oracle strips trailing spaces on the client side, is that a >configurable option? How would you insert significant trailing >spaces into a VARCHAR column if the client library strips them? The Oracle precompiler (PROC) has an option: CHAR_MAP=CHARZ that allows retaining cobol's trailing blanks.
On Mon, Sep 26, 2005 at 01:12:40PM -0700, Jim.Gray@Bull.com wrote: > >If Oracle strips trailing spaces on the client side, is that a > >configurable option? How would you insert significant trailing > >spaces into a VARCHAR column if the client library strips them? > > The Oracle precompiler (PROC) has an option: CHAR_MAP=CHARZ > that allows retaining cobol's trailing blanks. That's because the Oracle precompiler doesn't ship with the source code, so if you want the feature you can't just hack it up. That's not the case with Postgres -- you have the source, so you can change it if you want. More power to you! -- Alvaro Herrera Architect, http://www.EnterpriseDB.com "Changing the world ... one keyboard at a time!" (www.DVzine.org)