db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Embretsen <John.Embret...@Sun.COM>
Subject Re: WHERE clause
Date Wed, 29 Nov 2006 10:53:52 GMT
Alan M. Feldstein wrote:
> jdb shows me the SQL that Derby sees
> 
>     Thread-1[1] print selectSql
>      selectSql = "SELECT * FROM "ADDTestCasesResults" WHERE
>     "chipRelease" = 'Sputnik001' AND "testCaseID" = 1"
>     Thread-1[1]
> 
> This Java statement
> 
>     resultSet = statement.executeQuery( selectSql );
> 
> then fails with
> 
>     ERROR 42X04: Column 'SPUTNIK001' is either not in any table in the
>     FROM list or ...
> 
> Apparently, Derby thinks that 'Sputnik001' is a column name, failing to 
> recognize that it is a character string to be compared with each row in 
> the "chipRelease" column.
> 
> If you need to see the Java code that assigned to the selectSql 
> variable, it is
> 
>     selectSql = "SELECT * FROM \"ADDTestCasesResults\" WHERE
>     \"chipRelease\" = \'" + simulationResult.getChipRelease() + "\' AND
>     \"testCaseID\" = " + Long.toString( simulationResult.getTestCaseID() );
> 

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).

I've seen this error message before, and it is often caused by quoting errors. I 
would suggest using PreparedStatement instead, which lets you avoid quoting 
strings such as 'Sputnik001'.

Example:

selectSql = "SELECT * FROM \"ADDTestCasesResults\" WHERE \"chipRelease\" = ? AND
\"testCaseID\" = ?";

PreparedStatement pStmt = conn.prepareStatement(selectSql);
pStmt.setString(1, simulationResult.getChipRelease());
pStmt.setLong(2, simulationResult.getTestCaseID());
resultSet = pStmt.executeQuery();


If you are using the same statement more than once, prepare it once and then 
reuse it (prepareStatement(), ..., setXXX(), executeQuery(), ..., setXXX(), 
executeQuery(), ...).


-- 
John


Mime
View raw message