db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: NullPointerException using PreparedStatement INSERT INTO... SELECT ?,? FROM
Date Sat, 29 May 2010 11:28:48 GMT
On 05/28/10 09:26 PM, Stephan van Loendersloot wrote:
> Hello everyone,
>
> We use some queries (via prepared statements) to implement INSERT IF NOT 
> EXISTS functionality.
> However, 10.6.1.0 aborts with a NullPointerException and kills the 
> connection.
>
> The query below is not the actual query we use... it's a simplified 
> version for debugging purposes.
>
> (Stack trace follows at the end of this message)
>
> Steps to reproduce:
>
> ij version 10.6
> ij> CONNECT 'jdbc:derby://localhost/db;create=true';
> CREATE TABLE a (c1 INTEGER NOT NULL, c2 INTEGER NOT NULL);
> PREPARE ins AS 'INSERT INTO a (c1, c2) SELECT ?, ? FROM a WHERE NOT 
> EXISTS (SELECT c1 FROM a WHERE c1 = ? AND c2 = ?)';
> ij> 0 rows inserted/updated/deleted
> ij> ERROR XJ001: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, 
> SQLERRMC: java.lang.NullPointerExceptionXJ001.U
> ij> quit;
>
>
> It used to work in 10.5.3.0:
>
>
> ij version 10.5
> ij> CONNECT 'jdbc:derby://localhost/db;create=true';
> ij> CREATE TABLE a (c1 INTEGER NOT NULL, c2 INTEGER NOT NULL);
> 0 rows inserted/updated/deleted
> ij> PREPARE ins AS 'INSERT INTO a (c1, c2) SELECT ?, ? FROM a WHERE NOT 
> EXISTS (SELECT c1 FROM a WHERE c1 = ? AND c2 = ?)';
> ij> quit;
>
> If I fill in the first parameters of the statement (i.e. SELECT 1,1) it 
> does work, but since these first two need to correspond with the last 
> two, the advantage of using prepared statements is lost.
>
> I didn't see anything regarding this particular error in the release 
> notes, so I wonder if anyone can confirm that this is a bug? If so, I'll 
> file a JIRA issue.
>   

Hi Stephan,

I think this is DERBY-4671 (Embedded driver does not work with
jbossCache), and a fix for it has been checked in and back-ported to the
10.6 branch.

One workaround is to wrap the parameters in a CAST operator (the NPE is
thrown because the type of the parameters isn't known, and the CAST
operator adds the missing type information):

ij> PREPARE ins AS 'INSERT INTO a (c1, c2) SELECT CAST(? AS INT), CAST(? AS INT)
FROM a WHERE NOT EXISTS (SELECT c1 FROM a WHERE c1 = ? AND c2 = ?)'


-- 
Knut Anders


Mime
View raw message