Thread: SQLSTATE not updated in ecpg program.
Hello. I looked at the interfaces, bugs, and general archives for the past month and could not find anything similar to the problem I'm having. I'm having issues with ecpg compiled programs built on several 8.2.0 installs I've done (all on suse linux kernel 2.6.13-15.12). The same program had no issues built/run on postgres 8.1.5 on the same computers. ( I'm not using the postgres in the suse distro packages -- I built everything for 8.1.5 and 8.2.0 from the source on a postgresql ftp mirror. ) In all cases the configure, make, make check, and install all appear to work properly. I'm not having connection issues -- psql works locally and connects to remote servers. pgadminIII on a Windows box also works talking to the servers. A simple test program compiled with ecpg worked fine for 8.1.5 which can be found here: http://www.kenjennings.cc/st/prg/ux/test_pgc.tar.gz The program does simple error checking using sqlca.sqlstate which the postgres docs (31.11.2) suggest is preferable to sqlca.sqlcode. When rebuilt for 8.2.0 it appears to not work anymore. After a lot of digging and adding extra debugging I discovered that all the EXEC SQL are actually executing just fine. The problem is that the contents of sqlca.sqlstate[5] is not changing, and as sqlstate perpetually contains garbage which doesn't match "00000" the program thinks everything fails. sqlca.sqlcode is being updated, as are sqlca.sqlerrm.sqlerrml and sqlca.sqlerrm.sqlerrmc[70]. I've done a dozen installs and reinstalls of 8.2.0 on these servers using different configuration options over the past few days (and I'm getting pretty darned good at it). Everything seems to install perfectly each time. The original configure line used for 8.2.0 for two of the three systems is the same as used for 8.1.5: CONFIGURE = '--with-tcl' '--with-perl' '--with-openssl' '--enable-integer-datetimes' '--enable-thread-safety' One of the systems does not have the --with-openssl option, since it is missing a dependency. Removing the openssl option from the build on the other systems does not change the behavior of ecpg compiled programs on those systems. Does anyone have any idea of something I might be doing wrong with the install ?
On Wed, Dec 27, 2006 at 11:26:09PM -0500, Ken Jennings wrote: > A simple test program compiled with ecpg worked fine for 8.1.5 > which can be found here: http://www.kenjennings.cc/st/prg/ux/test_pgc.tar.gz > The program does simple error checking using sqlca.sqlstate which the postgres > docs (31.11.2) suggest is preferable to sqlca.sqlcode. Could you please upload a tarball that contains the sources and the makefile and not the RCS archive which this one seems to be? I will have a look at it asap. 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@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Thursday 28 December 2006 07:34, Michael Meskes wrote: > On Wed, Dec 27, 2006 at 11:26:09PM -0500, Ken Jennings wrote: > > A simple test program compiled with ecpg worked fine for 8.1.5 > > which can be found here: > > http://www.kenjennings.cc/st/prg/ux/test_pgc.tar.gz The program does > > simple error checking using sqlca.sqlstate which the postgres docs > > (31.11.2) suggest is preferable to sqlca.sqlcode. > > Could you please upload a tarball that contains the sources and the > makefile and not the RCS archive which this one seems to be? I will have > a look at it asap. I extracted all the source files: http://www.kenjennings.cc/st/prg/ux/test_pgc_src.tar.gz 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 Thanks, Ken Jennings
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: 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. 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@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
> 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.
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@bellsouth.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@jabber.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@jabber.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