Maximum Statement length in a batch update?
Hi All, I am seeing an issue when I add 200 statements to a batch and run executeBatch(). The following exception is thrown: java.sql.BatchUpdateException: Statement length exceeds maximum. at com.informix.jdbc.IfxStatement.executeBatch(IfxStatement.java:1635) No exception is thrown when I call addbatch(). I do not see the issue when I add 75 statements per batch. How can I figure out how many statements I can safely add? TIA, rouble
On Mar 29, 11:00 am, "rouble" <rou...@gmail.com> wrote: > Hi All, > > I am seeing an issue when I add 200 statements to a batch and run > executeBatch(). The following exception is thrown: > java.sql.BatchUpdateException: Statement length exceeds maximum. > at > com.informix.jdbc.IfxStatement.executeBatch(IfxStatement.java:1635) > > No exception is thrown when I call addbatch(). > > I do not see the issue when I add 75 statements per batch. > > How can I figure out how many statements I can safely add? > > TIA, > rouble That seems to be a driver-specific limitation, and it's probably not about the number of statements, but the collected length. I bet that if you try a much smaller statement you can add more, and with a much bigger statement, you'll hit the limit sooner. Joe Weinstein at BEA Systems
If you run on an older version, try a newer one, this sounds as a known bug. Sorry no bug# if the latest, contact Tech support. Superboer. On 29 mrt, 20:00, "rouble" <rou...@gmail.com> wrote: > Hi All, > > I am seeing an issue when I add 200 statements to a batch and run > executeBatch(). The following exception is thrown: > java.sql.BatchUpdateException: Statement length exceeds maximum. > at > com.informix.jdbc.IfxStatement.executeBatch(IfxStatement.java:1635) > > No exception is thrown when I call addbatch(). > > I do not see the issue when I add 75 statements per batch. > > How can I figure out how many statements I can safely add? > > TIA, > rouble
On Mar 29, 11:00 am, "rouble" <rou...@gmail.com> wrote: > Hi All, > > I am seeing an issue when I add 200 statements to a batch and run > executeBatch(). The following exception is thrown: > java.sql.BatchUpdateException: Statement length exceeds maximum. > at > com.informix.jdbc.IfxStatement.executeBatch(IfxStatement.java:1635) > > No exception is thrown when I call addbatch(). > > I do not see the issue when I add 75 statements per batch. > > How can I figure out how many statements I can safely add? The limit in Informix (CSDK - which includes JDBC - talking to IDS) is 64 KB in a 'single statement' - more precisely, a single string sent as to be prepared. You may separately be running into a limit in the driver if you are confident you've not gotten close to 64 KB yet. If you are batching INSERT operations, see whether you can find a way to use Informix's 'insert cursor' feature. In ESQL/C terms, it allows you to PREPARE the INSERT with placeholders for the values "INSERT INTO SomeTable VALUES(?,?,?,?,...,?,?)", then DECLARE a cursor for it, and then use "PUT cursorname USING $hostvar1, $hostvar2, ..., $hostvarN", with self-flushing when the buffer fills, an explicit FLUSH cursor to send the data to the server, and CLOSE and FREE to release it when you're done. The advantage of this is that it sends just the value data, not the entire SQL statement, so the server has less work to do. What I don't know is whether this interface is available to you via a JDBC extension.
On Mar 29, 2:00 pm, "rouble" <rou...@gmail.com> wrote: > Hi All, > > I am seeing an issue when I add 200 statements to a batch and run > executeBatch(). The following exception is thrown: > java.sql.BatchUpdateException: Statement length exceeds maximum. > at com.informix.jdbc.IfxStatement.executeBatch(IfxStatement.java:1635) > > No exception is thrown when I call addbatch(). > > I do not see the issue when I add 75 statements per batch. > > How can I figure out how many statements I can safely add? > > TIA, > rouble When I use the addBatch() with 20,000 insert commands, I do not see an issue. I use these properties when setting up a connection: Properties pr = new Properties(); pr.put("IFX_USEPUT","1"); pr.put("FET_BUF_SIZE", "32767"); Connection con = DriverManager.getConnection( "jdbc:informix-sqli:// <host>:<port>:INFORMIXSERVER=<server>;user=<username>;password=<password>", pr); IFX_USEPUT is to allow the driver to use the insert cursor, and FET_BUF_SIZE is set to the max buffer size allowed. Zachi