Обсуждение: [INTERFACES] Inserting using SQL descriptors in ECPG in different versions of Linux

Поиск
Список
Период
Сортировка

[INTERFACES] Inserting using SQL descriptors in ECPG in different versions of Linux

От
Anders Nilsson
Дата:
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)
-------------------------------------------------------------------      

Re: [INTERFACES] Inserting using SQL descriptors in ECPG indifferent versions of Linux

От
Michael Meskes
Дата:
> 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.

No idea why the same ecpg version behaves differently on these two
boxes. I assume that not both have the same version installed by
default, meaning you installed a non-default PostgreSQL yourself. Maybe
there was something left from the distro provided version?

> Any idea what I might be doing wrong?

Yes.

>     // Insert record
>     exec sql execute sid1 using descriptor insert_desc;

If my memory serves well, this should be "exec sql
execute sid1 using sql descriptor insert_desc;"

Without the SQL it tries to insert the sqlda which does not exist.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL


--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

Re: [INTERFACES] Inserting using SQL descriptors in ECPG in differentversions of Linux

От
Anders Nilsson
Дата:
Adding the SQL keyword fixed it! And, I was able to remove that dummy insert_desc variable declaration above.

Thanks for your help,
Anders

On Sun, Oct 1, 2017 at 3:44 AM, Michael Meskes <meskes@postgresql.org> wrote:
> 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.

No idea why the same ecpg version behaves differently on these two
boxes. I assume that not both have the same version installed by
default, meaning you installed a non-default PostgreSQL yourself. Maybe
there was something left from the distro provided version?

> Any idea what I might be doing wrong?

Yes.

>     // Insert record
>     exec sql execute sid1 using descriptor insert_desc;

If my memory serves well, this should be "exec sql
execute sid1 using sql descriptor insert_desc;"

Without the SQL it tries to insert the sqlda which does not exist.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL