Content-Type: text/plain;
  charset="iso-8859-15"
From: Tilo Schwarz <mail@tilo-schwarz.de>
To: <pgsql-interfaces@postgresql.org>
Subject: ECPG Problem (Bug?)
Date: Thu, 14 Aug 2003 23:55:12 +0200
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200308142355.12630.mail@tilo-schwarz.de>

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=20
SuSE Linux 8.0. I hit a few difficulties, so I thought this writing might b=
e=20
of some value to the right people... (also because I found ECPG to be reall=
y=20
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=20
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; */  /* uncommenting this line solves the problem =
*/

    while (1) {
	EXEC SQL FETCH cur INTO :id;
	if (sqlca.sqlcode =3D=3D 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 an=
d=20
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=
=20
started heavy googling - finally found a posting telling me that I have to=
=20
open the cursor. So I put in the line=20
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=
.=20
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 op=
en=20
cursor statement like

EXEC SQL OPEN cursor;

is not copied to the output. Instead, the cursor's DECLARE command is used=
=20
because it opens the cursor as well.


But that doesn't fit my observation, because in the generated code without =
the=20
OPEN statement the DECLARE statement was simply commented out (and I got th=
e=20
complaint 'sql error WARNING:  PerformPortalFetch: portal "cur" not found').

I don't know, what's the correct behaviour, but something seems not correct=
 to=20
me...


2.)
The docs say:


4.5. Passing Data

[...]

The special types VARCHAR and VARCHAR2 are converted into a named struct fo=
r=20
every variable.


But I observed that=20

    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 err=
or,=20
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 ge=
t:
> ./a.out
sql error
sqlca.sqlcode: 0
sqlca.sqlwarn[1]: W
fn: gaze00000275(=EF=FF=BFH=EF=FF=BF=ED=B9@

Two issues:
1.)
The sqlprint gives me only the message "sql error", but no further informat=
ion=20
as it does in case of other errors. I would be nice to have some output lik=
e=20
"VARCHAR field truncated" here.

2.)
It seems, that the truncated string in 'fn' is not null-terminated (it prin=
ts=20
like gaze00000275(=EF=FF=BFH=EF=FF=BF=ED=B9@). Wouldn't it be much safer, i=
f the following=20
truncation rule would be used?:
Given a buffer 'buf' with n characters and query data 'dat' with m >=3D n=
=20
characters, copy the first n-1 charaters 'dat' to 'buf' and set buf[n] =3D =
0.=20
This would make the handling of truncated data much easier.


Enough for now ... thanks for the good work!

	Tilo