[ECPG] - Help on Dynamic Query using SQLDA with Cursors

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[ECPG] - Help on Dynamic Query using SQLDA with Cursors

Rajesh Parameswaran
Hi there,
I hope this is the right place to ask questions regarding postgres.

I'm trying to execute a sample program with SQL DESCRIPTOR using CURSOR for INSERT SQL.
I'm getting the following errors. Please let me know, if you need any more details.
Kindly advise.

Details below.

Best Regards,
Rajesh Iyer

Details of System:
Postgres Version: 11.3
OS: RHEL 7 - 64 bit
GCC Version: 4.8.5

Problem Statement:
- Migration of a project from Oracle (Pro*C) to Postgres (ECPG).
- The queries are generated dynamically based on conditions and the table name, input columns and the input value varies based on the condition. Hence decided to use SQL DESCRIPTOR with Cursor.
- Huge amount of query gets processed based on time interval and hence can't use direct SQL INSERT.
: If I use a single connection, then it will take long time to process all the queries
: If I use multiple connections per query, then the number of connections will hit the threshold and may result in failure of DB as I understand, each DB has a specific number of DB connections to spawn.
- Hence decided to go ahead with a dedicated number of Cursors (20 Nos), which if available, will be used to execute.
Kindly advise, if there is a better way to do this.

Table Structure:
mithdb=> \d mithun.cars
Table "mithun.cars"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id         | integer               | not null
 name   | character varying(20) |
 price    | integer               |
Indexes:
    "cars_pkey" PRIMARY KEY, btree (id)



Contents:
mithdb=> select * from mithun.cars;
 id |      name       | price
----+-----------------+--------
  1 | Audi            |  52642
  2 | Mercedes        |  59127
  3 | Skoda           |   9000
  4 | Volvo           |  29000
  5 | Bentley         | 350000
  6 | Citroen         |  21000
  7 | Hummer          |  41400
  8 | Volkswagen      |  21600
 10 | Hyundai i10     |  12000
 15 | Hyundai Tuscan  |  26000
 17 | 'Tata Hector'   |  33000
 88 | 'Tata Hector'   |  88000
 55 | Chevorlet Cruze |  40000
(13 rows)


Source Code: SampleDescriptor.pgc
#include <stdio.h>
#include <stdlib.h>

EXEC SQL include sqlda.h;
sqlda_t *sqlda2;
EXEC SQL include sqlca;
EXEC SQL WHENEVER SQLERROR CALL print_sqlca();

void print_sqlca()
{
    fprintf(stderr, "==== sqlca ====\n");
    fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode);
    fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    fprintf(stderr, "sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],
                                                          sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
    fprintf(stderr, "sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2],
                                                          sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5],
                                                          sqlca.sqlwarn[6], sqlca.sqlwarn[7]);
    fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate);
    fprintf(stderr, "===============\n");
}

int main(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
    const char *target = "mithdb@10.40.12.31:5432";
    const char *user = "mithdev";
    const char *password = "mithdev";
    char *szQuery = "INSERT INTO mithun.cars (id, name, price) VALUES(?, ?, ?)";
    int intVal;
    int nInputCnt = 3;
    EXEC SQL END DECLARE SECTION;
   
    EXEC SQL ALLOCATE DESCRIPTOR sqlda2;
    EXEC SQL CONNECT TO :target AS dbconn USER :user IDENTIFIED BY :password;
    EXEC SQL PREPARE stmt FROM :szQuery;
    EXEC SQL DECLARE cur CURSOR FOR stmt;
   
    sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + ((nInputCnt-1) * sizeof(sqlvar_t)));
    memset(sqlda2, 0, sizeof(sqlda_t) + ((nInputCnt-1) * sizeof(sqlvar_t)));
    sqlda2->sqln = nInputCnt;
   
    intVal = 25;
    sqlda2->sqlvar[0].sqltype = ECPGt_int;
    sqlda2->sqlvar[0].sqldata = (char *)&intVal;
    sqlda2->sqlvar[0].sqllen  = sizeof(intVal);
   
    sqlda2->sqlvar[1].sqltype = ECPGt_char;
    sqlda2->sqlvar[1].sqldata = "Rolls Royce";
    sqlda2->sqlvar[1].sqllen  = 11;
   
    intVal = 55000;
    sqlda2->sqlvar[2].sqltype = ECPGt_int;
    sqlda2->sqlvar[2].sqldata = (char *)&intVal;
    sqlda2->sqlvar[2].sqllen  = sizeof(intVal);
   
    EXEC SQL OPEN cur USING DESCRIPTOR sqlda2;
    free(sqlda2);
   
    EXEC SQL DEALLOCATE DESCRIPTOR sqlda2;
    EXEC SQL COMMIT;
    EXEC SQL CLOSE ALL;
    EXEC SQL DISCONNECT dbconn;
    EXEC SQL DISCONNECT ALL;
    return 0;
}

Output:
bash-4.2$ vim SampleDescriptor.pgc
bash-4.2$ ecpg SampleDescriptor.pgc
bash-4.2$ gcc -o SampleDescriptor SampleDescriptor.c -I/usr/pgsql-11/include -L/usr/pgsql-11/lib -lecpg -std=c99
bash-4.2$ ./SampleDescriptor
==== sqlca ====
sqlcode: -400
sqlerrm.sqlerrml: 43
sqlerrm.sqlerrmc: syntax error at or near "INSERT" on line 62
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 42601
===============