Re: 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