Hi All!

Can someone tell me if it is possible with CLI/ODBC to bind multiple rows of parameters
and then with a single call to SQLExecute() to insert all those rows at once?

Of course I could have an insert statement with multiple VALUES sections. That is a valid workaround -
but it would be more elegant if the statement contains only one VALUES and is prepared as such and
then just but set of rows to it.

Without such feature, if I do insert a row per statement, will I have a round trip to the server for every single statement -
meaning that for every single statement my applicaiton will have to wait for the CLI driver to send a request to the server,
wait for the server to execute my statement, return a result code to the application, and then it may continue with the next statement?

Thanks in advance.

Alex.

Yes.

Do you have such an enormous amont of records that have to be inserted that this would be a bottle neck ? You could place all the data that has teo be inserted in a text file and issue an IMPORT or LOAD statement.

Even if you would perform an IMPORT statement, you would make a round trip to the server.

Only with a LOAD this will not happen. But then the server needs direct access to the text file to be able to insert the data into the tabel.

In a test I conducted, inseting 500 000 lines using IMPORT took 20 minutes, using LOAD it took 20 seconds, a seed increase of x 60 !

In my case, I am trying to make a CLI/ODBC program to have shorter transactions and in that way reduce the time there are row locks hold by the transaction. The use of import/load probably would be not practicle for this type of application.

You could write something like:
INSERT INTO tablename (ID, columnA) VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

Thereby reducing the number of client to/from server communication with 75%.

All the records would be inserted or none. Let's say that the ID 1 was already taken, you'd get a PK violation error and nothing would have been inserted into the table, not even (2, 'B') even if that tuple would have been allowed.

You could write something like
INSERT INTO tablename (ID, column) VALUES
(:hv_id_1, :hv_ca_1), (:hv_id_2, :hv_ca_2), (:hv_id_3, :hv_ca_3), (:hv_id_4, :hv_ca_4);

with hv_... the host variables, and that for every possible amount of records you could submit in one transaction by your application. This would lead to a chain of

if nbr_of recs == 1 then
INSERT INTO tablename (ID, column) VALUES
(:hv_id_1, :hv_ca_1)
else if nbr_of recs == 2 then
INSERT INTO tablename (ID, column) VALUES
(:hv_id_1, :hv_ca_1), (:hv_id_2, :hv_ca_2);
....

It looks ugly.

I always design normalised tables and shy from "clever hacks" in my programs. Only when tests show (serious) performance problems will I try denormalisation and clever hacks. Most of the time (and I mean 99.9% of the time) you can solve initial problems in a more elegant way. Denormalisation and clever hacks always come at a price.

Thank you, Wim! I was considering to try something like that. I was wondering if CLI/ODBC would give me a way to use a statement like "INSERT INTO tablename (ID, colA) VALUES (?,?)" and just be able to bind multiple rows of parameters to it instead. It would be more elegant and would have the same performance effect.