Re: WHERE clause

[John Embretsen <Jo...@Sun.COM>]

Alan M. Feldstein wrote:

>> I am not able to reproduce the error you are seeing. Would you like to post the 
>> SQL you use to create the table? I tried with "testCaseID" being of type 
>> integer, and it worked just fine (Derby 10.3 alpha).
>>   
> CREATE TABLE "ADDTestCasesResults" ( "ADDTestCasesResultsID" BIGINT NOT 
> NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY , "chipRelease" 
> VARCHAR(10) NOT NULL , "testCaseID" BIGINT NOT NULL , "actualSum" BIGINT 
> NOT NULL )

I used this SQL for creating my test table, and I still cannot reproduce the 
error. I have tried both the client driver and the embedded driver of 10.2.1.6.

> The following Java statement fails with the same error message:
> 
>     statement.executeUpdate( "INSERT INTO \"ADDTestCasesResults\" (
>     \"chipRelease\", \"testCaseID\", \"actualSum\" ) VALUES( " +
>     simulationResult.getChipRelease() + ", " + Long.toString(
>     simulationResult.getTestCaseID() ) + ", " + Long.toString(
>     simulationResult.getActualSum() ) + " )" );

This probably fails because of missing single quotes around the "chipRelease" 
value (simulationResult.getChipRelease(), which is a String/VARCHAR). I was able 
to reproduce it, replacing the method calls with variable names containing 
String ("Sputnik001") and longs (11L and 22L, respectively). Adding the single 
quotes made the problem go away.

>> I would suggest using PreparedStatement instead, which lets you avoid quoting 
>> strings such as 'Sputnik001'.
[snip]
> Actually, that is a nice way of doing it. The SQL statement passed to 
> Connection.prepareStatement is simpler. Even the second parameter to 
> PreparedStatement.setLong is simpler. Furthermore, while not useful in 
> all cases (it is useful in mine), the PreparedStatement object can then 
> be used to efficiently execute the parameterized statement multiple 
> times. Finally, and most importantly, it avoids whatever the above 
> problem was (i.e. it works).

It's good to hear that using PreparedStatement solved the problem. 
PreparedStatements have many advantages over Statements that many JDBC users are 
not aware of - performance, security, memory usage, etc. But I guess you are 
right, it is not necessarily the best choice in _all_ situations.


-- 
John