db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alain Kuchta <alain.kuc...@etegent.com>
Subject RE: Problems with very large Batch Prepared Statements
Date Wed, 05 Sep 2012 13:07:09 GMT
Thanks David, I've had to take a similar approach. Are you using batch prepared statements?
Or running them a row at a time?

From: david myers [mailto:david.myers.scibearspace@gmail.com]
Sent: Tuesday, September 04, 2012 5:22 PM
To: Derby Discussion
Subject: Re: Problems with very large Batch Prepared Statements

On 04/09/12 22:11, Alain Kuchta wrote:
Hi all,

                I'm developing a program to move database from some other DBMS database to
a derby database. To accomplish this I'm making a java application and implementing batch
prepared statements.  The batch is very large (13000+ statements). When the program runs I
receive this exception:

Caused by: java.lang.NullPointerException
       at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeBatchElement(Unknown Source)
       at org.apache.derby.impl.jdbc.EmbedStatement.executeBatch(Unknown Source)
       at com.etegent.tools.SqlDataTransferTool.SqlDataMover.transferTable(SqlDataMover.java:207)

The PreparedStatement object used to execute the batch is not null. Also, by querying the
destination database after the program ran, I noticed that all of the rows that I had desired
to move, had been moved. Doing some debugging I noticed that the Vector<E> batchStatements
which is a member of the PreparedStatements object, had extra elements, A LOT of them. 13171
elements were populated and 7309 elements were null. I observed similar behavior with a smaller
set of batchesand I am pretty sure this is typical vector behavior. However, I do not receive
the above exception when using smaller batches. Such as 10 statements. I suppose a work around
for now is to do smaller set of batches. Is this a known limitation of derby? Any idea why
this may be happening?

Alain Kuchta
Hello Alain,

I've been playing a similar game transfering data over to derbyDB.
The solution that I came to was to generate the prepared statement, then for each row in the
'other' database capture all the values.
Pass these values to the prepared statement, and send it.
Things I learnt whilst doing this...
You need to handle values that are NULL if you try to input a null value into derby the whole
row may stop (you don't seem to be having this issue). use the setNull() methods of a preparedstatement,
here is a link.
You may also find that your 'other' db isn't so strict about key words, and the use of spaces
or other extended characters. You need to handle these.


View raw message