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

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