db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From david myers <david.myers.scibearsp...@gmail.com>
Subject Re: Problems with very large Batch Prepared Statements
Date Tue, 04 Sep 2012 21:22:10 GMT
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

Mime
View raw message