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

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(at)10(dot)40(dot)12(dot)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
===============