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 Wed, 05 Sep 2012 14:59:52 GMT
Alain,

no I simply created my prepared statement.
<enter a loop over a resultSet>
catch the data from the external source for a single row
<end loop over single row>
send this data into the prepared statement, an then executeUpdate on it
... then go back around and do it all again...

Its maybe a little more involved than the above makes it sound, but it
works fairly happily.

The joys of data management !

David

On Wed, Sep 5, 2012 at 3:07 PM, Alain Kuchta <alain.kuchta@etegent.com> wrote:
> 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?
>
>
>
> -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