Joe Wilson <developir-/[EMAIL PROTECTED]> writes:
> Perhaps some JDBC drivers implement the behavior you expect, but
> technically, you should call addBatch() to add each individual SQL
> statement to the batch prior to calling executeBatch().
Yes, I tried that too before posting. Unfortunately, addBatch() and
executeBatch() are implemented to simply run each "batch" separately
in sequence:
,----[ Stmt.executeBatch() ]
| public int[] executeBatch() throws SQLException {
| // TODO: optimise
| checkOpen(); close();
| if (batch == null) return new int[] {};
|
| int[] changes = new int[batch.size()];
|
| synchronized (db) { try {
| for (int i=0; i < changes.length; i++) {
| try {
| sql = (String)batch.get(i);
| db.prepare(this);
| changes[i] = db.executeUpdate(this, null);
| } catch (SQLException e) {
| throw new BatchUpdateException(
| "batch entry " + i + ": " + e.getMessage(), changes);
| } finally {
| db.finalize(this);
| }
| }
| } finally {
| batch.clear();
| } }
|
| return changes;
| }
`----
That doesn't solve my problem of having read the ten DDL commands from
a file, and providing them as a single "batch". There's still just one
call to prepare(), which misses out on all but the first statement in
the string.
> Perhaps if you post to the sqlitejdbc mailing list, the author may
> consider making such an extension.
I did that last night¹ before finally going home, and, thinking about
it on the way, I think it's too much to ask of the implementor. The
workarounds are probably easier to justify than adding the extra state
and loops required to consume the entire SQL string -- at least given
the resources I've seen dedicated to this library so far.
> But you can probably get away with just splitting your DDL string on
> ";" and feeding them to addBatch in a loop and then calling
> executeBatch.
Yes, I agree. I wasn't sure whether I can always trust that finding a
semicolon is the end of statement; one could appear in a string, but
that's a stretch for my DDL.
I also am considering splitting on blank lines, or on lines beginning
with the comment "-- go" or something more deliberate. Or, going even
further, putting each statement in a separate XML element, and letting
an XML parser do the splitting.
I'll be working on this problem this morning, so any more advice would
be most welcome.
Footnotes:
¹ http://thread.gmane.org/gmane.comp.db.sqlite.jdbc/113
--
Steven E. Harris
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
