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 <[email protected]> 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:[email protected]] > 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
