Inserting using SQL descriptors in ECPG in different versions of Linux
Anders Nilsson <[email protected]> Thu, 28 Sep 2017 13:14:39 -0500
| Newsgroups | gmane.comp.db.postgresql.interfaces |
|---|---|
| Message-ID | <CAEx11v5ScGENAcDVfMVuKMaBMMGkdrdqtSgAmfF_NaBihKkY+g@mail.gmail.com> |
--089e0823f844becb7b055a43e15e
Content-Type: text/plain; charset="UTF-8"
Hi all,
I'm working on migrating a project from Centos-6 Linux (gcc 4.4.7) to
Centos-7 (gcc 4.8.5) Linux. The postgreSQL version is staying the same, at
9.2.x. Everything is going fine, except for one section of our ECPG/C code
which uses SQL descriptor areas to insert a dynamic number of columns into
a table. Demonstration code can be found below.
The following code compiles and runs fine without error messages on the
Centos-6 box, but on the Centos-7 box, the execution of the prepared query
with descriptor returns successful but without inserting any records and
displaying the "No record actually inserted" message. Set PGUSER/PGDATABASE
accordingly to run.
Any idea what I might be doing wrong?
Thanks,
Anders
(P.S. The presence of that descriptor placeholder variable is to get it to
compile. I'm not exactly sure if that is the best way of going about it. )
---------------------------------------------------------------------
descriptor.pgc
---------------------------------------------------------------------
// Includes
#include <stdlib.h>
int main ( int argc, char *argv[] )
{
// Declared variables
exec sql begin declare section;
long column; // Column index
char insert_desc[32]; // Descriptor placeholder variable
int is_null; // NULL indicator
long number_columns; // Number of table columns
char statement[256]; // Query statement
long value_long ; // Test value
double value_double ; // Test value
exec sql end declare section;
// Initialize variables
insert_desc[0] = 0 ; // Dummy placeholder variable for descriptor name
number_columns = 3 ;
// Connect to database
exec sql connect to default;
if ( sqlca.sqlcode ) { printf( "Unable to connect to database:%s\n",
sqlca.sqlerrm.sqlerrmc ); }
// Create scratch table to test on
sprintf ( statement, "create temporary table scratch ( value1 integer, "
"value2 double
precision, "
"value3 double
precision )" );
exec sql execute immediate :statement;
if ( sqlca.sqlcode ) { printf( "Unable to create temporary table
scratch:%s\n", sqlca.sqlerrm.sqlerrmc ); }
// Prepare statement
strcpy ( statement, "insert into scratch ( value1, value2, value3 ) "
"values ( ?, ?, ? )" );
exec sql prepare sid1 from :statement;
if ( sqlca.sqlcode ) { printf( "Unable to prepare statement
\"%s\":%s\n", statement, sqlca.sqlerrm.sqlerrmc ); }
// Allocate descriptor
exec sql allocate descriptor insert_desc;
if ( sqlca.sqlcode ) { printf( "Unable to allocate descriptor:%s\n",
sqlca.sqlerrm.sqlerrmc ); }
// Set number of columns
exec sql set descriptor insert_desc COUNT = :number_columns;
if ( sqlca.sqlcode ) { printf( "Unable to set descriptor size:%s\n",
sqlca.sqlerrm.sqlerrmc ); }
// Populate columns
column = 1;
is_null = 0;
value_long = 60799;
exec sql set descriptor insert_desc VALUE :column
DATA = :value_long,
INDICATOR = :is_null ;
if ( sqlca.sqlcode ) { printf( "Unable to set column %ld descriptor
data:%s\n", column, sqlca.sqlerrm.sqlerrmc ); }
column++;
is_null = 0;
value_double = -45.78;
exec sql set descriptor insert_desc VALUE :column
DATA = :value_double,
INDICATOR = :is_null ;
if ( sqlca.sqlcode ) { printf( "Unable to set column %ld descriptor
data:%s\n", column, sqlca.sqlerrm.sqlerrmc ); }
column++;
is_null = 0;
value_double = 559997.4;
exec sql set descriptor insert_desc VALUE :column
DATA = :value_double,
INDICATOR = :is_null ;
if ( sqlca.sqlcode ) { printf( "Unable to set column %ld descriptor
data:%s\n", column, sqlca.sqlerrm.sqlerrmc ); }
// Insert record
exec sql execute sid1 using descriptor insert_desc;
if ( sqlca.sqlcode ) { printf( "Unable to insert record:%s\n",
sqlca.sqlerrm.sqlerrmc ); }
// Checking record count
if ( sqlca.sqlerrd[2] == 0 ) { printf( "No record actually inserted\n"
); }
// Free allocated memory
exec sql deallocate descriptor insert_desc;
exec sql free sid1;
// Disconnect from database
exec sql disconnect;
if ( sqlca.sqlcode ) { printf( "Unable to disconnect from
database:%s\n", sqlca.sqlerrm.sqlerrmc ); }
// Done
exit ( 0 );
}
------------------------------------------------------------------
Makefile:
------------------------------------------------------------------
PG=/usr
PG_LIB=$(PG)/lib64
PGM=descriptor
.SUFFIXES: .o .pgc .c
.pgc.c:
<tab>$(PG)/bin/ecpg -t $<
.c.o:
<tab>$(CC) -g -Wall -I$(PG)/include -c $<
$(PGM): $(PGM).o
<tab>$(CC) -o $@ $(PGM).o -L$(PG_LIB) -lecpg
clean:
<tab>rm -f $(PGM).o $(PGM)
-------------------------------------------------------------------
--089e0823f844becb7b055a43e15e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">Hi all,<div><br></div><div>=C2=A0 =C2=A0I'm working on=
migrating a project from Centos-6 Linux (gcc 4.4.7) to Centos-7 (gcc 4.8.5=
) Linux. The postgreSQL version is staying the same, at 9.2.x. Everything i=
s going fine, except for one section of our ECPG/C code which uses SQL desc=
riptor areas to insert a dynamic number of columns into a table. Demonstrat=
ion code can be found below.</div><div><br></div><div>The following code co=
mpiles and runs fine without error messages on the Centos-6 box, but on the=
Centos-7 box, the execution of the prepared query with descriptor returns =
successful but without inserting any records and displaying the "No re=
cord actually inserted" message. Set PGUSER/PGDATABASE accordingly to =
run.</div><div><br></div><div>Any idea what I might be doing wrong?</div><d=
iv><br></div><div><br></div><div>Thanks,</div><div>Anders</div><div><br></d=
iv><div>(P.S. The presence of that descriptor placeholder variable is to ge=
t it to compile. I'm not exactly sure if that is the best way of going =
about it. )</div><div><br></div><div><font face=3D"monospace, monospace">--=
----------------------------<wbr>------------------------------<wbr>-------=
--</font></div><div><font face=3D"monospace, monospace">descriptor.pgc</fon=
t></div><div><font face=3D"monospace, monospace">--------------------------=
----<wbr>------------------------------<wbr>---------</font></div><div><fon=
t face=3D"monospace, monospace">// Includes</font></div><div><font face=3D"=
monospace, monospace">#include <stdlib.h></font></div><div><font face=
=3D"monospace, monospace"><br></font></div><div><font face=3D"monospace, mo=
nospace">int main ( int argc, char *argv[] )</font></div><div><font face=3D=
"monospace, monospace">{</font></div><div><font face=3D"monospace, monospac=
e">=C2=A0 =C2=A0 // Declared variables</font></div><div><font face=3D"monos=
pace, monospace">=C2=A0 =C2=A0 exec sql begin declare section;</font></div>=
<div><font face=3D"monospace, monospace"><br></font></div><div><font face=
=3D"monospace, monospace">=C2=A0 =C2=A0 long =C2=A0 =C2=A0column; =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 // Column index</fo=
nt></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 char =C2=A0=
=C2=A0insert_desc[32]; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0// Descriptor pla=
ceholder variable</font></div><div><font face=3D"monospace, monospace">=C2=
=A0 =C2=A0 int =C2=A0 =C2=A0 is_null; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0// NULL indicator</font></div><div><font face=3D=
"monospace, monospace">=C2=A0 =C2=A0 long =C2=A0 =C2=A0number_columns; =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 // Number of table columns</font></div><div=
><font face=3D"monospace, monospace">=C2=A0 =C2=A0 char =C2=A0 =C2=A0statem=
ent[256]; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 // Query statement</font></div=
><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 long =C2=A0 =C2=A0v=
alue_long ; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0// Test value</=
font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 double =
=C2=A0value_double ; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0// Test value=
</font></div><div><font face=3D"monospace, monospace"><br></font></div><div=
><font face=3D"monospace, monospace">=C2=A0 =C2=A0 exec sql end declare sec=
tion;</font></div><div><font face=3D"monospace, monospace"><br></font></div=
><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 // Initialize varia=
bles</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 ins=
ert_desc[0] =3D 0 ; // Dummy placeholder variable for descriptor name</font=
></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 number_column=
s =3D 3 ;</font></div><div><font face=3D"monospace, monospace"><br></font><=
/div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 // Connect to d=
atabase</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =
exec sql connect to default;</font></div><div><font face=3D"monospace, mono=
space">=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable to connect=
to database:%s\n", sqlca.sqlerrm.sqlerrmc ); }</font></div><div><font=
face=3D"monospace, monospace">=C2=A0 =C2=A0 // Create scratch table to tes=
t on</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 spr=
intf ( statement, "create temporary table scratch ( value1 integer, &q=
uot;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "value2 double precision, "=
;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "value3 double precision )" );=
</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 exec sq=
l execute immediate :statement;</font></div><div><font face=3D"monospace, m=
onospace">=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable to crea=
te temporary table scratch:%s\n", sqlca.sqlerrm.sqlerrmc ); }</font></=
div><div><font face=3D"monospace, monospace"><br></font></div><div><font fa=
ce=3D"monospace, monospace">=C2=A0 =C2=A0 // Prepare statement</font></div>=
<div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 strcpy ( statement, =
"insert into scratch ( value1, value2, value3 ) "</font></div><di=
v><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "values ( ?, ?, ? )&q=
uot; );</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =
exec sql prepare sid1 from :statement;</font></div><div><font face=3D"monos=
pace, monospace">=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable =
to prepare statement \"%s\":%s\n", statement, sqlca.sqlerrm.=
sqlerrmc ); }</font></div><div><font face=3D"monospace, monospace"><br></fo=
nt></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 // Allocate=
descriptor</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=
=A0 exec sql allocate descriptor insert_desc;</font></div><div><font face=
=3D"monospace, monospace">=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( &quo=
t;Unable to allocate descriptor:%s\n", sqlca.sqlerrm.sqlerrmc ); }</fo=
nt></div><div><font face=3D"monospace, monospace"><br></font></div><div><fo=
nt face=3D"monospace, monospace">=C2=A0 =C2=A0 // Set number of columns</fo=
nt></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 exec sql se=
t descriptor insert_desc COUNT =3D :number_columns;</font></div><div><font =
face=3D"monospace, monospace">=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( =
"Unable to set descriptor size:%s\n", sqlca.sqlerrm.sqlerrmc ); }=
</font></div><div><font face=3D"monospace, monospace"><br></font></div><div=
><font face=3D"monospace, monospace">=C2=A0 =C2=A0 // Populate columns</fon=
t></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 column =3D 1=
;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 is_nul=
l =3D 0;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0=
value_long =3D 60799;</font></div><div><font face=3D"monospace, monospace"=
>=C2=A0 =C2=A0 exec sql set descriptor insert_desc VALUE :column</font></di=
v><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DATA =3D :value_long,</font></div><div><=
font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 INDICATOR =3D :is_null ;</font></div><div><font=
face=3D"monospace, monospace">=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf(=
"Unable to set column %ld descriptor data:%s\n", column, sqlca.s=
qlerrm.sqlerrmc ); }</font></div><div><font face=3D"monospace, monospace"><=
br></font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 colu=
mn++;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 is=
_null =3D 0;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =
=C2=A0 value_double =3D -45.78;</font></div><div><font face=3D"monospace, m=
onospace">=C2=A0 =C2=A0 exec sql set descriptor insert_desc VALUE :column</=
font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DATA =3D :value_double,</font=
></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 INDICATOR =3D :is_null ;</font></div=
><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 if ( sqlca.sqlcode =
) { printf( "Unable to set column %ld descriptor data:%s\n", colu=
mn, sqlca.sqlerrm.sqlerrmc ); }</font></div><div><font face=3D"monospace, m=
onospace"><br></font></div><div><font face=3D"monospace, monospace">=C2=A0 =
=C2=A0 column++;</font></div><div><font face=3D"monospace, monospace">=C2=
=A0 =C2=A0 is_null =3D 0;</font></div><div><font face=3D"monospace, monospa=
ce">=C2=A0 =C2=A0 value_double =3D 559997.4;</font></div><div><font face=3D=
"monospace, monospace">=C2=A0 =C2=A0 exec sql set descriptor insert_desc VA=
LUE :column</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DATA =3D :value_dou=
ble,</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 INDICATOR =3D :is_null ;</=
font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 if ( sqlc=
a.sqlcode ) { printf( "Unable to set column %ld descriptor data:%s\n&q=
uot;, column, sqlca.sqlerrm.sqlerrmc ); }</font></div><div><font face=3D"mo=
nospace, monospace"><br></font></div><div><font face=3D"monospace, monospac=
e">=C2=A0 =C2=A0 // Insert record</font></div><div><font face=3D"monospace,=
monospace">=C2=A0 =C2=A0 exec sql execute sid1 using descriptor insert_des=
c;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 if ( =
sqlca.sqlcode ) { printf( "Unable to insert record:%s\n", sqlca.s=
qlerrm.sqlerrmc ); }</font></div><div><font face=3D"monospace, monospace">=
=C2=A0 =C2=A0 // Checking record count</font></div><div><font face=3D"monos=
pace, monospace">=C2=A0 =C2=A0 if ( sqlca.sqlerrd[2] =3D=3D 0 ) { printf( &=
quot;No record actually inserted\n" ); }</font></div><div><font face=
=3D"monospace, monospace"><br></font></div><div><font face=3D"monospace, mo=
nospace">=C2=A0 =C2=A0 // Free allocated memory</font></div><div><font face=
=3D"monospace, monospace">=C2=A0 =C2=A0 exec sql deallocate descriptor inse=
rt_desc;</font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0=
exec sql free sid1;</font></div><div><font face=3D"monospace, monospace"><=
br></font></div><div><font face=3D"monospace, monospace">=C2=A0 =C2=A0 // D=
isconnect from database</font></div><div><font face=3D"monospace, monospace=
">=C2=A0 =C2=A0 exec sql disconnect;</font></div><div><font face=3D"monospa=
ce, monospace">=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable to=
disconnect from database:%s\n", sqlca.sqlerrm.sqlerrmc ); }</font></d=
iv><div><font face=3D"monospace, monospace"><br></font></div><div><font fac=
e=3D"monospace, monospace">=C2=A0 =C2=A0 // Done</font></div><div><font fac=
e=3D"monospace, monospace">=C2=A0 =C2=A0 exit ( 0 );</font></div><div><font=
face=3D"monospace, monospace">}</font></div><div><font face=3D"monospace, =
monospace"><br></font></div><div><div><font face=3D"monospace, monospace">-=
-----------------------------<wbr>------------------------------<wbr>------=
</font></div><div><font face=3D"monospace, monospace">Makefile:</font></div=
><div><font face=3D"monospace, monospace">------------------------------<wb=
r>------------------------------<wbr>------</font></div><font face=3D"monos=
pace, monospace">PG=3D/usr<br>PG_LIB=3D$(PG)/lib64<br>PGM=3Ddescriptor<br>.=
SUFFIXES: .o =C2=A0.pgc .c<br>.pgc.c:<br><tab>$(PG)/bin/ecpg -t $<=
<br>.c.o:<br><tab>$(CC) -g -Wall -I$(PG)/include -c $<<br>$(PGM): =
$(PGM).o<br><tab>$(CC) -o $@ $(PGM).o -L$(PG_LIB) -lecpg<br>clean:<br=
><tab>rm -f $(PGM).o $(PGM)</font><font face=3D"monospace, monospace"=
><br></font></div><div><font face=3D"monospace, monospace">----------------=
--------------------------------------------------- =C2=A0 =C2=A0 =C2=A0</f=
ont></div></div>
--089e0823f844becb7b055a43e15e--