Обсуждение: ECPG Problem (Bug?)
Dear Gurus,
(I wasn't really sure, if this is the right list, but here we go...)
Friday I had my first contact with ECPG using a "normal" 7.3.1 installation on
SuSE Linux 8.0. I hit a few difficulties, so I thought this writing might be
of some value to the right people... (also because I found ECPG to be really
useful!)
Version:
> ecpg --version
ecpg (PostgreSQL 7.3.1) 2.10.0
I have three issues shown below:
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;
...
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;
So my program looked like this:
int main (int argc, char ** argv)
{ EXEC SQL BEGIN DECLARE SECTION; int id; EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER sqlwarning sqlprint; EXEC SQL WHENEVER sqlerror sqlprint;
EXEC SQL CONNECT TO tschwarz@rtulmx0101 USER tschwarz USING xxx;
EXEC SQL BEGIN; EXEC SQL DECLARE cur CURSOR FOR SELECT image_id FROM image; /* EXEC SQL OPEN cur; */ /*
uncommentingthis line solves the problem */
while (1) {EXEC SQL FETCH cur INTO :id;if (sqlca.sqlcode == ECPG_NOT_FOUND) break; /* no more rows */printf("%d\n",
id); } EXEC SQL CLOSE cur; EXEC SQL END;
EXEC SQL DISCONNECT;
}
Than I ran my first teeny weeny ECPG program (connecting to the database and
executing above example) but all I got was:
> ecpg pgproblem.pgc; gcc -g pgproblem.c -lecpg
> ./a.out
sql error WARNING: PerformPortalFetch: portal "cur" not found
sql error WARNING: PerformPortalClose: portal "cur" not found
id: -1073746136
After digging around about an hour, staring at the example and my code, I
started heavy googling - finally found a posting telling me that I have to
open the cursor. So I put in the line
EXEC SQL OPEN cur;
and everything worked fine:
> ./a.out
id: 8708115
Now what puzzles me is, that in the example the OPEN statement is not shown.
In addition, the docs also say:
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.
But that doesn't fit my observation, because in the generated code without the
OPEN statement the DECLARE statement was simply commented out (and I got the
complaint 'sql error WARNING: PerformPortalFetch: portal "cur" not found').
I don't know, what's the correct behaviour, but something seems not correct to
me...
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'
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.
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.
Enough for now ... thanks for the good work!
Tilo
> 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!
Michael, would you make the mentioned doc corrections? Thanks. --------------------------------------------------------------------------- Michael Meskes wrote: > > 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! > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- 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, Pennsylvania19073
On Thu, Sep 04, 2003 at 11:56:30PM -0400, Bruce Momjian wrote: > > Michael, would you make the mentioned doc corrections? Thanks. Done. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!