db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alan M. Feldstein" <afel...@earthlink.net>
Subject Re: WHERE clause
Date Wed, 29 Nov 2006 22:55:38 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
<blockquote type="cite">From: John Embretsen &lt;<a target="_top"
Subject: <a target="_top"
WHERE clause</a><br>
Newsgroups: <a
Date: 2006-11-29 10:53:52 GMT (11 hours and 31 minutes ago)<br>
  <pre>Alan M. Feldstein wrote:
&gt; the SQL that Derby sees
&gt;      selectSql = "SELECT * FROM "ADDTestCasesResults" WHERE "chipRelease" = 'Sputnik001'
AND "testCaseID" = 1"
&gt; This Java statement
&gt;     resultSet = statement.executeQuery( selectSql );
&gt; then fails with
&gt;     ERROR 42X04: Column 'SPUTNIK001' is either not in any table in the
&gt;     FROM list or ...
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).
<tt>CREATE TABLE "ADDTestCasesResults" ( "ADDTestCasesResultsID" BIGINT
VARCHAR(10) NOT NULL , "testCaseID" BIGINT NOT NULL , "actualSum"
BIGINT NOT NULL )</tt><br>
The following Java statement fails with the same error message:<br>
<blockquote><tt>statement.executeUpdate( "INSERT INTO
\"ADDTestCasesResults\" ( \"chipRelease\", \"testCaseID\",
\"actualSum\" ) VALUES( " + simulationResult.getChipRelease() + ", " +
Long.toString( simulationResult.getTestCaseID() ) + ", " +
Long.toString( simulationResult.getActualSum() ) + " )" );<br>
Both observations were made with Derby<br>
<blockquote type="cite">
I've seen this error message before, and it is often caused by quoting errors.</pre>
Either it's my error, or there is a bug in one of the software layers
under my application (e.g. JDBC or Derby).<br>
<blockquote type="cite">
  <pre> I 
would suggest using PreparedStatement instead, which lets you avoid quoting 
strings such as 'Sputnik001'.


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

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).<br>
Thank you, John.<br>

View raw message