pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedSQLSTATE not updated in ecpg program.
10+ messages / 3 participants
[nested] [flat]
* SQLSTATE not updated in ecpg program.
@ 2006-12-28 04:26 Ken Jennings <ken_jennings@bellsouth.net>
2006-12-28 12:34 ` Re: SQLSTATE not updated in ecpg program. Michael Meskes <meskes@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: Ken Jennings @ 2006-12-28 04:26 UTC (permalink / raw)
To: pgsql-interfaces
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 ?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 04:26 SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
@ 2006-12-28 12:34 ` Michael Meskes <meskes@postgresql.org>
2006-12-28 13:07 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Meskes @ 2006-12-28 12:34 UTC (permalink / raw)
To: Ken Jennings <ken_jennings@bellsouth.net>; +Cc: pgsql-interfaces
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!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 04:26 SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
2006-12-28 12:34 ` Re: SQLSTATE not updated in ecpg program. Michael Meskes <meskes@postgresql.org>
@ 2006-12-28 13:07 ` Ken Jennings <ken_jennings@bellsouth.net>
2006-12-28 14:25 ` Re: SQLSTATE not updated in ecpg program. Michael Meskes <meskes@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: Ken Jennings @ 2006-12-28 13:07 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 04:26 SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
2006-12-28 12:34 ` Re: SQLSTATE not updated in ecpg program. Michael Meskes <meskes@postgresql.org>
2006-12-28 13:07 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
@ 2006-12-28 14:25 ` Michael Meskes <meskes@postgresql.org>
0 siblings, 0 replies; 10+ messages in thread
From: Michael Meskes @ 2006-12-28 14:25 UTC (permalink / raw)
To: Ken Jennings <ken_jennings@bellsouth.net>; +Cc: pgsql-interfaces
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!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
@ 2006-12-28 15:42 ken_jennings@bellsouth.net
2006-12-29 00:01 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 10:01 ` Re: SQLSTATE not updated in ecpg program. Michael Meskes <meskes@postgresql.org>
0 siblings, 2 replies; 10+ messages in thread
From: ken_jennings@bellsouth.net @ 2006-12-28 15:42 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: 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 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 15:42 Re: SQLSTATE not updated in ecpg program. ken_jennings@bellsouth.net
@ 2006-12-29 00:01 ` Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 02:51 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
1 sibling, 1 reply; 10+ messages in thread
From: Ken Jennings @ 2006-12-29 00:01 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 15:42 Re: SQLSTATE not updated in ecpg program. ken_jennings@bellsouth.net
2006-12-29 00:01 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
@ 2006-12-29 02:51 ` Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 10:02 ` Re: SQLSTATE not updated in ecpg program. Michael Meskes <meskes@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: Ken Jennings @ 2006-12-29 02:51 UTC (permalink / raw)
To: pgsql-interfaces
[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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 15:42 Re: SQLSTATE not updated in ecpg program. ken_jennings@bellsouth.net
2006-12-29 00:01 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 02:51 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
@ 2006-12-29 10:02 ` Michael Meskes <meskes@postgresql.org>
2006-12-29 12:33 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Meskes @ 2006-12-29 10:02 UTC (permalink / raw)
To: Ken Jennings <ken_jennings@bellsouth.net>; +Cc: pgsql-interfaces
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!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 15:42 Re: SQLSTATE not updated in ecpg program. ken_jennings@bellsouth.net
2006-12-29 00:01 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 02:51 ` Re: SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 10:02 ` Re: SQLSTATE not updated in ecpg program. Michael Meskes <meskes@postgresql.org>
@ 2006-12-29 12:33 ` Ken Jennings <ken_jennings@bellsouth.net>
0 siblings, 0 replies; 10+ messages in thread
From: Ken Jennings @ 2006-12-29 12:33 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SQLSTATE not updated in ecpg program.
2006-12-28 15:42 Re: SQLSTATE not updated in ecpg program. ken_jennings@bellsouth.net
@ 2006-12-29 10:01 ` Michael Meskes <meskes@postgresql.org>
1 sibling, 0 replies; 10+ messages in thread
From: Michael Meskes @ 2006-12-29 10:01 UTC (permalink / raw)
To: ken_jennings@bellsouth.net; +Cc: pgsql-interfaces
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!
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-12-29 12:33 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-28 04:26 SQLSTATE not updated in ecpg program. Ken Jennings <ken_jennings@bellsouth.net>
2006-12-28 12:34 ` Michael Meskes <meskes@postgresql.org>
2006-12-28 13:07 ` Ken Jennings <ken_jennings@bellsouth.net>
2006-12-28 14:25 ` Michael Meskes <meskes@postgresql.org>
2006-12-28 15:42 Re: SQLSTATE not updated in ecpg program. ken_jennings@bellsouth.net
2006-12-29 00:01 ` Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 02:51 ` Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 10:02 ` Michael Meskes <meskes@postgresql.org>
2006-12-29 12:33 ` Ken Jennings <ken_jennings@bellsouth.net>
2006-12-29 10:01 ` Michael Meskes <meskes@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox