db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stephan van Loendersloot (LIST)" <step...@republika.nl>
Subject Re: NullPointerException using PreparedStatement INSERT INTO... SELECT ?,? FROM
Date Sat, 29 May 2010 12:36:18 GMT


On 29-05-10 13:28, Knut Anders Hatlen wrote:
> 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;
>>
>>
<SNIP>
>>
> 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 = ?)'
>
>

Hi Knut Anders,

Thanks for the investigation. I had read about DERBY-4671 and checked 
out the latest trunk, since I thought it might be related to my problem.

Something must have gone wrong with my build. Did it again today... and 
the problem is indeed fixed.

In the meantime, the CASTing solution you suggested works flawlessly.


Thanks,

Stephan.

Mime
View raw message