Re: ECPG Problem (Bug?) - Mailing list pgsql-interfaces

> 1.)
> The first problem started while I was trying to use a cursor. The docs show an 
> ECPG example to use cursors like this:
> 
> 
> 4.4. Running SQL Commands
> [...]
> Select using Cursors:
> 
> EXEC SQL DECLARE foo_bar CURSOR FOR
>     SELECT number, ascii FROM foo
>     ORDER BY ascii;
> EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;

This is a bug in the docs. In PGSQL there is no open command yes, but
embedded sql wants and needs it. In fact ecpg only copies the declare to
the position of the open command.

> 4.11. For the Developer
> 
> Note that not all SQL commands are treated in this way. For instance, an open 
> cursor statement like
> 
> EXEC SQL OPEN cursor;
> 
> is not copied to the output. Instead, the cursor's DECLARE command is used 
> because it opens the cursor as well.

That is correct. The DECLARE is used at the position of the OPEN to open
the cursor.

> 2.)
> The docs say:
> 
> 
> 4.5. Passing Data
> 
> [...]
> 
> The special types VARCHAR and VARCHAR2 are converted into a named struct for 
> every variable.
> 
> 
> But I observed that 
> 
>     EXEC SQL BEGIN DECLARE SECTION;
>     VARCHAR name;
>     EXEC SQL END DECLARE SECTION;
> 
> works well, but
> 
>     EXEC SQL BEGIN DECLARE SECTION;
>     VARCHAR2 name;
>     EXEC SQL END DECLARE SECTION;
> 
> gives:
> pg.pgc:8: ERROR: invalid datatype 'VARCHAR2'

VARCHAR2 does not exist anymore.

> 3.)
> String truncation in case of too short buffer.
> In this example
>     EXEC SQL WHENEVER sqlwarning sqlprint;
>     EXEC SQL WHENEVER sqlerror sqlprint;
> is used.
> 
> If a VARCHAR field has a long enough buffer, every thing works fine (no error, 
> correct field 'fn' content):
> > ./a.out
> sqlca.sqlcode: 0
> sqlca.sqlwarn[1]:
> fn: gaze0000027524/gaze0000027525.pgm
> 
> Now if the VARCHAR field is too short to hold the data from the query, I get:
> > ./a.out
> sql error
> sqlca.sqlcode: 0
> sqlca.sqlwarn[1]: W
> fn: gaze00000275(ïÿ¿Hïÿ¿í¹@
> 
> Two issues:
> 1.)
> The sqlprint gives me only the message "sql error", but no further information 
> as it does in case of other errors. I would be nice to have some output like 
> "VARCHAR field truncated" here.

sqlprint is normally used for errors. No one ever implemented warnings
in there.

> 2.)
> It seems, that the truncated string in 'fn' is not null-terminated (it prints 
> like gaze00000275(ïÿ¿Hïÿ¿í¹@). Wouldn't it be much safer, if the following 
> truncation rule would be used?:
> Given a buffer 'buf' with n characters and query data 'dat' with m >= n 
> characters, copy the first n-1 charaters 'dat' to 'buf' and set buf[n] = 0. 
> This would make the handling of truncated data much easier.

I beg to disagree as varchar has an additional length entry it does not
have to NULL terminated.

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!