[ECPG] - Help on Dynamic Query using SQLDA with Cursors - Mailing list pgsql-sql

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

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

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)

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)

#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;
}

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