Hi,

I work with with dynamically generated SQL statements (may be a
thousand or more).
When working with Postgres I can simply use a single JDBC statement and
dispatch them all.
However, when trying to do the same thing with Informix (9.3) the JDBC
driver complains that temp tables are not known.

This can be reproduced the following way:
Statement stm = con.createStatement();
stm.execute("create temp table testmb(col1 integer);insert into
testmb values (1);");
//normally more SQL would follow which makes use of the temp tables
--> The specified table (testtab) is not in the database.

When splitting it up into batch-Statements, it works.
stm.addBatch("create temp table testmb(col1 integer);");
stm.addBatch("insert into testmb values (1);");
stm.executeBatch();

However, my SQL is very dynamically created, and so I would not like
to parse manually with a StringTokenizer over all semicolons, since
semicolons might be contained in a char value (select val1||';'||val2
from xy).

Is there an elegant way to achieve a batch-execution?

m.bisping@gmx.de wrote:
> Hi,
>
> I work with with dynamically generated SQL statements (may be a
> thousand or more).
> When working with Postgres I can simply use a single JDBC statement and
> dispatch them all.
> However, when trying to do the same thing with Informix (9.3) the JDBC
> driver complains that temp tables are not known.
>
> This can be reproduced the following way:
> Statement stm = con.createStatement();
> stm.execute("create temp table testmb(col1 integer);insert into
> testmb values (1);");
> //normally more SQL would follow which makes use of the temp tables
> --> The specified table (testtab) is not in the database.
>
> When splitting it up into batch-Statements, it works.
> stm.addBatch("create temp table testmb(col1 integer);");
> stm.addBatch("insert into testmb values (1);");
> stm.executeBatch();
>
> However, my SQL is very dynamically created, and so I would not like
> to parse manually with a StringTokenizer over all semicolons, since
> semicolons might be contained in a char value (select val1||';'||val2
> from xy).
>
> Is there an elegant way to achieve a batch-execution?

Do the CREATE TEMP TABLE in a separate statement.

Once the table exists, combine as many inserts as you like into as few
statements as you like - beware, the total length must be less than 64 KB.

Consider the benefits of an INSERT cursor - which might not be available
via the JDBC interface. Or using a prepared INSERT statement with
question marks as placeholders for the values, and execute it
repeatedly, providing new values for each row.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/