Re: WHERE clause

[Alan M. Feldstein]

From: John Embretsen <[EMAIL PROTECTED]> Subject: Re: WHERE clause Newsgroups: gmane.comp.apache.db.derby.user Date: 2006-11-29 10:53:52 GMT (11 hours and 31 minutes ago) Alan M. Feldstein wrote: > the SQL that Derby sees > > selectSql = "SELECT * FROM "ADDTestCasesResults" WHERE "chipRelease" = 'Sputnik001' AND "testCaseID" = 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 ... > 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 ) 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() ) + " )" ); Both observations were made with Derby 10.2.1.6. I've seen this error message before, and it is often caused by quoting errors. Either it's my error, or there is a bug in one of the software layers under my application (e.g. JDBC or Derby). 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(), ...). 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). Thank you, John.