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?

 

-Thanks

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.
http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setNull(int,%20int)
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.

David