From derby-user-return-14595-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue Sep 4 21:22:43 2012 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 27072DBC5 for ; Tue, 4 Sep 2012 21:22:43 +0000 (UTC) Received: (qmail 19142 invoked by uid 500); 4 Sep 2012 21:22:42 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 19123 invoked by uid 500); 4 Sep 2012 21:22:42 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 19113 invoked by uid 99); 4 Sep 2012 21:22:42 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Sep 2012 21:22:42 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=FSL_RCVD_USER,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of david.myers.scibearspace@gmail.com designates 74.125.83.44 as permitted sender) Received: from [74.125.83.44] (HELO mail-ee0-f44.google.com) (74.125.83.44) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Sep 2012 21:22:36 +0000 Received: by eekb45 with SMTP id b45so2702619eek.31 for ; Tue, 04 Sep 2012 14:22:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; bh=h0J6CQuZm6PnQxMBDsh1cef+qJ/fZxFvPNwHfDefQ/A=; b=svKCCRmuu+BwQpZ1X/vDKJhAXT5P03fx3O11/6tXqP8ZXJWDt7n0MFoN+usUlMNCcm +e0KGRz/Wf8DyyfSzPXOLB5W44Kf0k+Y8go4eUrdGmt8NsYUips+XldGyTLER2dWrr6o YxVgb7G0m9h2Nxf7f8lpuygP5bccNe0PLQp3cJ5Ynlwtqok2jkc+lSrzBFj8OLELDV0/ DBvEqgBLo1ogsGZnXLZ5VNObn5odwb8DwkVPyk8WemPsZEBu74hh9v1MRXZ413PNqLVt rNYf2qAgbLdC1fOwvWyeC4C02dufb0UNbrAk4WiaSUd5wchgtpyx8ekPlDvq2xwK+cvD tNuw== Received: by 10.14.0.198 with SMTP id 46mr27952212eeb.30.1346793735252; Tue, 04 Sep 2012 14:22:15 -0700 (PDT) Received: from [192.168.1.10] (ALagny-153-1-46-134.w86-212.abo.wanadoo.fr. [86.212.253.134]) by mx.google.com with ESMTPS id l42sm48525568eep.1.2012.09.04.14.22.13 (version=SSLv3 cipher=OTHER); Tue, 04 Sep 2012 14:22:14 -0700 (PDT) Message-ID: <50467102.20509@gmail.com> Date: Tue, 04 Sep 2012 23:22:10 +0200 From: david myers User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120714 Thunderbird/14.0 MIME-Version: 1.0 To: Derby Discussion Subject: Re: Problems with very large Batch Prepared Statements References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------020101040400040105050904" X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. --------------020101040400040105050904 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > 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 --------------020101040400040105050904 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
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
--------------020101040400040105050904--