Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dxdL6-0005YJ-6V for pgsql-interfaces@arkaria.postgresql.org; Thu, 28 Sep 2017 18:15:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dxdL5-0001BS-5A for pgsql-interfaces@arkaria.postgresql.org; Thu, 28 Sep 2017 18:15:11 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dxdKk-0000ZZ-S2 for pgsql-interfaces@postgresql.org; Thu, 28 Sep 2017 18:14:51 +0000 Received: from mail-wr0-x22d.google.com ([2a00:1450:400c:c0c::22d]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dxdKc-0005j0-Oh for pgsql-interfaces@postgresql.org; Thu, 28 Sep 2017 18:14:49 +0000 Received: by mail-wr0-x22d.google.com with SMTP id z39so4183175wrb.8 for ; Thu, 28 Sep 2017 11:14:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=noaa-gov.20150623.gappssmtp.com; s=20150623; h=mime-version:from:date:message-id:subject:to; bh=jC+C4elCIG5h4VZHwLUTGeQdxiFPD3/f1W1BHLCvnys=; b=QaoUN84Gda0m0RnNQKg7KB1RuRrPqD0+epVSn9f1JaMqzo1mwFAzL75ChEEJ1yvM88 tGjQwrWbT94GQXjypxwpwtz4R4vZbmb/rf2fijgJg9PqtNIkuoV25bY+fNBhO/gLJpFH aXMRB24SF1LGpp6Xi1xTkdoeozSJiehvyyCqZwQ9IzL7aNtDlyYoA6Yffr0m+8lRcqKN JKHD7B1w/6dY47uALgr/8ewecie1WU14aIro/Lewbu3B61VDwhY+5IjzKr8QflnKH8hX 44P9+qRfAJ5c+8PVM+WQqnApDoRGuNFilXuT48vWl3EtrIwUA5duK5X18ODG3AIc0Q1V T3RA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=jC+C4elCIG5h4VZHwLUTGeQdxiFPD3/f1W1BHLCvnys=; b=lZlJ9nnm+XHOkEg/Jra46M4J3NDOU46BlWNDvNXdN49kYDJW88m2pFIXh+JanxcnUe b22i1IY/VDqFUoQCXFLR61ukk+hiQD9QtZbYs84aJSbV3PC/ReExpf/vGFV4H1AyvK/D DVDrBZQMtDY/vCHi808WxQlUUtU+HN94M9aYSv8QiiyYfeCOHXkm1ty2AUcj0VeNY2iO gu/sM6OzBmbQE0Q8wdhDXP6LgwaJGCWFWtFLy0zU1HQOCAtEKfwig/CdeT6cSk6XnzLy DkEfOM79+SEVak7lOrRW6sjORBF2L01We+Elt0MICUNPT2+P/aBs5Y22flnOV9Loiieh 9Wzg== X-Gm-Message-State: AHPjjUjD+ItAG5wBVQV9Ew0nVU2Dd6NHd+GEBCHTBH4+cx8FII9Me44b VehBhloIDyRvEKjrsxM6IOZhymzUR4wSzHB5mkEJBDTp/kY= X-Google-Smtp-Source: AOwi7QCVWxGINqTu2qZscsQDRzNF9fYKTv+lpSDg7A9Pf7cDow7icLA6s5ignjuEDHzKhrf0wvJ+V2+dS17ct3kzKiY= X-Received: by 10.46.56.8 with SMTP id f8mr2402324lja.189.1506622480220; Thu, 28 Sep 2017 11:14:40 -0700 (PDT) MIME-Version: 1.0 Received: by 10.46.27.153 with HTTP; Thu, 28 Sep 2017 11:14:39 -0700 (PDT) From: Anders Nilsson Date: Thu, 28 Sep 2017 13:14:39 -0500 Message-ID: Subject: Inserting using SQL descriptors in ECPG in different versions of Linux To: pgsql-interfaces@postgresql.org Content-Type: multipart/alternative; boundary="089e0823f844becb7b055a43e15e" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-interfaces Precedence: bulk Sender: pgsql-interfaces-owner@postgresql.org --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 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: $(PG)/bin/ecpg -t $< .c.o: $(CC) -g -Wall -I$(PG)/include -c $< $(PGM): $(PGM).o $(CC) -o $@ $(PGM).o -L$(PG_LIB) -lecpg clean: rm -f $(PGM).o $(PGM) ------------------------------------------------------------------- --089e0823f844becb7b055a43e15e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi all,

=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.

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.

Any idea what I might be doing wrong?


Thanks,
Anders

(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. )

--= -----------------------------------------------------------------= --
descriptor.pgc
--------------------------= -------------------------------------------
// Includes
#include <stdlib.h>

int main ( int argc, char *argv[] )
{
=C2=A0 =C2=A0 // Declared variables
=C2=A0 =C2=A0 exec sql begin declare section;
=

=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
=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
=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
=C2=A0 =C2=A0 long =C2=A0 =C2=A0number_columns; =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 // Number of table columns
=C2=A0 =C2=A0 char =C2=A0 =C2=A0statem= ent[256]; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 // Query statement
=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
=C2=A0 =C2=A0 double = =C2=A0value_double ; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0// Test value=

=C2=A0 =C2=A0 exec sql end declare sec= tion;

=C2=A0 =C2=A0 // Initialize varia= bles
=C2=A0 =C2=A0 ins= ert_desc[0] =3D 0 ; // Dummy placeholder variable for descriptor name
=C2=A0 =C2=A0 number_column= s =3D 3 ;

<= /div>
=C2=A0 =C2=A0 // Connect to d= atabase
=C2=A0 =C2=A0 = exec sql connect to default;
=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable to connect= to database:%s\n", sqlca.sqlerrm.sqlerrmc ); }
=C2=A0 =C2=A0 // Create scratch table to tes= t on
=C2=A0 =C2=A0 spr= intf ( statement, "create temporary table scratch ( value1 integer, &q= uot;
=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, "= ;
=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 )" );=
=C2=A0 =C2=A0 exec sq= l execute immediate :statement;
=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable to crea= te temporary table scratch:%s\n", sqlca.sqlerrm.sqlerrmc ); }

=C2=A0 =C2=A0 // Prepare statement
=
=C2=A0 =C2=A0 strcpy ( statement, = "insert into scratch ( value1, value2, value3 ) "
=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; );
=C2=A0 =C2=A0 = exec sql prepare sid1 from :statement;
=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable = to prepare statement \"%s\":%s\n", statement, sqlca.sqlerrm.= sqlerrmc ); }

=C2=A0 =C2=A0 // Allocate= descriptor
=C2=A0 =C2= =A0 exec sql allocate descriptor insert_desc;
=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( &quo= t;Unable to allocate descriptor:%s\n", sqlca.sqlerrm.sqlerrmc ); }

=C2=A0 =C2=A0 // Set number of columns
=C2=A0 =C2=A0 exec sql se= t descriptor insert_desc COUNT =3D :number_columns;
=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( = "Unable to set descriptor size:%s\n", sqlca.sqlerrm.sqlerrmc ); }=

=C2=A0 =C2=A0 // Populate columns
=C2=A0 =C2=A0 column =3D 1= ;
=C2=A0 =C2=A0 is_nul= l =3D 0;
=C2=A0 =C2=A0= value_long =3D 60799;
=C2=A0 =C2=A0 exec sql set descriptor insert_desc VALUE :column
=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 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 ;
=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf(= "Unable to set column %ld descriptor data:%s\n", column, sqlca.s= qlerrm.sqlerrmc ); }
<= br>
=C2=A0 =C2=A0 colu= mn++;
=C2=A0 =C2=A0 is= _null =3D 0;
=C2=A0 = =C2=A0 value_double =3D -45.78;
=C2=A0 =C2=A0 exec sql set descriptor insert_desc VALUE :column
=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,
=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 ;
=C2=A0 =C2=A0 if ( sqlca.sqlcode = ) { printf( "Unable to set column %ld descriptor data:%s\n", colu= mn, sqlca.sqlerrm.sqlerrmc ); }

=C2=A0 = =C2=A0 column++;
=C2= =A0 =C2=A0 is_null =3D 0;
=C2=A0 =C2=A0 value_double =3D 559997.4;
=C2=A0 =C2=A0 exec sql set descriptor insert_desc VA= LUE :column
=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,
=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 ;
=C2=A0 =C2=A0 if ( sqlc= a.sqlcode ) { printf( "Unable to set column %ld descriptor data:%s\n&q= uot;, column, sqlca.sqlerrm.sqlerrmc ); }

=C2=A0 =C2=A0 // Insert record
=C2=A0 =C2=A0 exec sql execute sid1 using descriptor insert_des= c;
=C2=A0 =C2=A0 if ( = sqlca.sqlcode ) { printf( "Unable to insert record:%s\n", sqlca.s= qlerrm.sqlerrmc ); }
= =C2=A0 =C2=A0 // Checking record count
=C2=A0 =C2=A0 if ( sqlca.sqlerrd[2] =3D=3D 0 ) { printf( &= quot;No record actually inserted\n" ); }

=C2=A0 =C2=A0 // Free allocated memory
=C2=A0 =C2=A0 exec sql deallocate descriptor inse= rt_desc;
=C2=A0 =C2=A0= exec sql free sid1;
<= br>
=C2=A0 =C2=A0 // D= isconnect from database
=C2=A0 =C2=A0 if ( sqlca.sqlcode ) { printf( "Unable to= disconnect from database:%s\n", sqlca.sqlerrm.sqlerrmc ); }

=C2=A0 =C2=A0 // Done
=C2=A0 =C2=A0 exit ( 0 );
}

-= -----------------------------------------------------------------=
Makefile:
------------------------------------------------------------------
PG=3D/usr
PG_LIB=3D$(PG)/lib64
PGM=3Ddescriptor
.= SUFFIXES: .o =C2=A0.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)
----------------= --------------------------------------------------- =C2=A0 =C2=A0 =C2=A0
--089e0823f844becb7b055a43e15e--