Re: BUG #1862: ECPG Connect, host variable trailing blanks

>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(at)fuhr(dot)org>
To
09/07/2005 12:15 James Gray <jim(dot)gray(at)bull(dot)com>
AM cc
pgsql-bugs(at)postgresql(dot)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