Re: Batch execution via JDBC (temp table problem)

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/