Re: SQLSTATE not updated in ecpg program. - Mailing list pgsql-interfaces

> From: Michael Meskes 
> On Thu, Dec 28, 2006 at 08:07:09AM -0500, Ken Jennings wrote:
> > This is an alternate version of the program with debugging where I dump the 
> > contents of all the useful elements of sqlca.  The file output.txt shows the 
> > output of a couple runs (on 8.2.0):
> > http://www.kenjennings.cc/st/prg/ux/test_pgc_new_src.tar.gz
> 
> And this is run in a all 8.2 environment? I just tested with 8.2 ecpglib
> against 8.1 database and got some useful information:

Yes, It was originally written for 8.1.5 and ran perfectly fine there on all our systems.
The output.txt file in the tarfile shows a couple runs when built/run for 8.2.0.
It appears sqlca.sqlstate is either not being updated or is stomped on by something.

> sqlstate[0] =0
> sqlstate[1] =8
> sqlstate[2] =0
> sqlstate[3] =0
> sqlstate[4] =1
> 
> Using the 8.1 libs I seem to get a memory overrun:
> 
> ./testpgc test1234 postgres
> ...
> sqlstate[0] =1
> sqlstate[1] =2
> sqlstate[2] =3
> sqlstate[3] =4
> 
> Yes, if I use test12 as db name sqlstate[2] and sqlstate[3] are empty.

Interesting.   The program uses command line arguments for the database connection information.  argv[1] is the dbname
stringand and argv[2] is the user or user/password string. db_connect( argv[1], argv[2] );  
 
So, there shouldn't be a scope problem.  Argv[] strings should stay where they are in memory and not change for the
durationof the program.
 

int db_connect( char * dbname, char * username )   just copies the pointers provided (from argv) to host variable names
thatecpg will know:   EXEC SQL BEGIN DECLARE SECTION ;       char * target = dbname;       char * user   = username;
EXECSQL END DECLARE SECTION ;
 

and then uses them:   EXEC SQL CONNECT TO :target USER :user ;

The target and user host variables themselves will lose scope when the function ends, but the actual values are still
thepointers from argv which won't change.  Is it necessary that the host variables used in the CONNECT TO be static for
theduration of the program?   
 

More interesting news.   We dug up a fourth server, installed 8.2.0, and built the test program.  It runs -- sqlstate
isbeing populated, here with "23505" for an intentionally initiated error:
 
struct  0x804b008
{       sqlcode =-403               sqlerrml =78               sqlerrmc ='duplicate key violates unique constraint
"const_unique_serialno"'in line 98.       sqlerrd[1] =0       sqlerrd[2] =0       sqlwarn[0] =       sqlwarn[1] =
sqlwarn[2]=       sqlstate[0] =2       sqlstate[1] =3       sqlstate[2] =5       sqlstate[3] =0       sqlstate[4] =5
 
}
INSERT AGAIN expect 23505, result is  err 23505 - 'duplicate key violates unique constraint "const_unique_serialno"' in
line98.
 

The difference is that this fourth box is an Intel Pentium 4.  All the other systems that cannot build/run the sample
programuse Athlon XP or Athlon MP CPUs.  Weird.