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.

On Thursday 28 December 2006 10:42, ken jennings wrote:
> > From: Michael Meskes
> > On Thu, Dec 28, 2006 at 08:07:09AM -0500, Ken Jennings wrote:
...
> 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.
>
> 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?

I've tried every alteration I can think of to the host variables in the
DECLARE SECTION and in every case sqlca is being stomped on by something. The
data overwriting sqlca appears to be a copy of (part of) the target/dbname
value. (Or more generally, some of the data in the ECPG* parameters.)

Things I've tried:

Changed the declarations of target and user to const per the online docs.
Declared them static.
Moved the DECLARE SECTION outside the db_connect() function, making them
global.
Moved them outside the function and made them static.
Performed strdup() of the function arguments and assigned the new strings to
target and user.
Declared target and user as actual char arrays and copied the contents of the
dbname/username to them.
I looked at the C output of ecpg and replaced the EXEC SQL for the CONNECT
with the same C code: { ECPGconnect(__LINE__, 0, target , user , NULL ,
NULL, 0); }. Still doesn't work.
I replaced target and user with literal strings in the function call above and
in the EXEC SQL CONNECT version and it still doesn't work.
I added the "AS" clause to the CONNECTION thinking maybe the library was
confused about something if a parameter is null, and it still doesn't work.

sqlca is being partially overwritten during EXEC SQL calls. It's beginning to
look like ecpg is broken in some environments. Or there's something that
should have been done differently during .configure .

This problem happens with 8.2.0 installed on our Athlon systems:
Linux wolverine 2.6.13-15.12-default #1 Thu Aug 24 11:23:58 UTC 2006 i686
athlon i386 GNU/Linux
Linux cerebro 2.6.13-15.12-smp #1 SMP Thu Aug 24 11:23:58 UTC 2006 i686 athlon
i386 GNU/Linux
We found this problem does not occur when the program is built and run on a
system using an actual Pentium 4:
Linux nawm 2.6.13-15.12-default #1 Thu Aug 24 11:23:58 UTC 2006 i686 i686 i386
GNU/Linux

[snip]
> sqlca is being partially overwritten during EXEC SQL calls. [...]

> This problem happens with 8.2.0 installed on our Athlon systems:
> Linux wolverine 2.6.13-15.12-default #1 Thu Aug 24 11:23:58 UTC 2006 i686
> athlon i386 GNU/Linux
> Linux cerebro 2.6.13-15.12-smp #1 SMP Thu Aug 24 11:23:58 UTC 2006 i686
> athlon i386 GNU/Linux

> We found this problem does not occur when the program is built and run on a
> system using an actual Pentium 4:
> Linux nawm 2.6.13-15.12-default #1 Thu Aug 24 11:23:58 UTC 2006 i686 i686
> i386 GNU/Linux

It turns out this is merely a case of stupid administrator tricks. ldd
reported none of the libraries loaded for the testpgc program came
from /usr/local/pgsql/lib. Poking around we discovered a previous
administrator had tried installing the SuSE package for postgreSQL a long
while ago. So, the libraries for the old install were being loaded instead
of version 8.2.0. It only works on the Pentium box, because we just built
that ourselves without any unnecessary packages. Duh.

On Thu, Dec 28, 2006 at 10:42:07AM -0500, ken_jennings(at)bellsouth(dot)net wrote:
> > Using the 8.1 libs I seem to get a memory overrun:
> > ...

Sorry, this was my fault. I didn't notice that some files weren't
rebuild. Using 8.2 libs with 8.1 sqlca include file does not work
because the error message buffer size was increased.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

On Thu, Dec 28, 2006 at 09:51:37PM -0500, Ken Jennings wrote:
> It turns out this is merely a case of stupid administrator tricks. ldd
> reported none of the libraries loaded for the testpgc program came
> from /usr/local/pgsql/lib. Poking around we discovered a previous
> administrator had tried installing the SuSE package for postgreSQL a long
> while ago. So, the libraries for the old install were being loaded instead
> of version 8.2.0. It only works on the Pentium box, because we just built
> that ourselves without any unnecessary packages. Duh.

So I take it all works well for you now, right?

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

On Friday 29 December 2006 05:02, Michael Meskes wrote:
> On Thu, Dec 28, 2006 at 09:51:37PM -0500, Ken Jennings wrote:
> > It turns out this is merely a case of stupid administrator tricks. ldd
> > reported none of the libraries loaded for the testpgc program came
> > from /usr/local/pgsql/lib. Poking around we discovered a previous
> > administrator had tried installing the SuSE package for postgreSQL a long
> > while ago. So, the libraries for the old install were being loaded
> > instead of version 8.2.0. It only works on the Pentium box, because we
> > just built that ourselves without any unnecessary packages. Duh.
>
> So I take it all works well for you now, right?

Perfectly. sqlca.sqlstate is happy once again.

Thanks.
Ken Jennings