Re: SQLSTATE not updated in ecpg program.

> 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 string and 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 duration of the program.

int db_connect( char * dbname, char * username ) just copies the pointers provided (from argv) to host variable names that ecpg will know:
EXEC SQL BEGIN DECLARE SECTION ;
char * target = dbname;
char * user = username;
EXEC SQL 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 the pointers from argv which won't change. Is it necessary that the host variables used in the CONNECT TO be static for the duration of the program?

More interesting news. We dug up a fourth server, installed 8.2.0, and built the test program. It runs -- sqlstate is being 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 line 98.

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