I've dwelled on it a little bit more and found out that subqueries seem to be broken in prepared statements when using virtual table names (e.g. select X."Title" from "Books" X where X."Price">20.
I've attached a test application which does the following.
<snip> drop table a if exists create cached table a (a int identity,b int)
create index bIdx on a(b)
insert into a(b) values(1) insert into a(b) values(2) insert into a(b) values(3) insert into a(b) values(4) insert into a(b) values(5) insert into a(b) values(6)
/** Use a subquery to determine range to modify **/ update a set b=100 where b>(select b from a X where X.a=2)
/** Do the same with parameters and no virtual table name **/ update a set b=200 where b>(select b from a where a=?)
/** Do it with virtual table names **/ update a set b=300 where b>(select b from a X where X.a=?) </snip>
The application supports the very same value (2) for the UPDATE statement in all three cases and only stumbled over the third UPDATE statement.
Hope that helps ....
P.S.
It also seems that PreparedStatement.execute() is very picky on the kind of statement you are allowed to execute 'DROP' and 'INSERT' statements seem to be rejected.
Campbell Boucher-Burnet wrote:
Thanks Sebastian. I'll take a look at this and add a test case in org.hsqldb.test
----- Original Message ----- From: "Sebastian Kloska" <[EMAIL PROTECTED]> To: "HSQLDB Developers" <[EMAIL PROTECTED]> Sent: Thursday, September 11, 2003 9:30 AM Subject: [Hsqldb-developers] Subqueries in prepared Statements ?
Hi ...
In the readme.txt freadt mentions that IN(?,?) statements in prepared statements are broken.
I can't find anything about in the bug tracker, but I seem to have trouble with all kind of subqueries in prepared statement in APLHA_O
e.g.
I have a lot stuff like
UPDATE a SET a.b=0 where a.c>(select MIN(d) FROM a WHERE e=X)
or alike:
When I execute the stuff interactively it works find but whe X is set as a parameter in a prepared statement I often get
'Single value expected'
What seems to be the problem ?
Cheers
S.,
-- ********************************** Dr. Sebastian Kloska Head of Bioinformatics Scienion AG Volmerstr. 7a 12489 Berlin phone: +49-(30)-6392-1708 fax: +49-(30)-6392-1701 http://www.scienion.de **********************************
------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
-- ********************************** Dr. Sebastian Kloska Head of Bioinformatics Scienion AG Volmerstr. 7a 12489 Berlin phone: +49-(30)-6392-1708 fax: +49-(30)-6392-1701 http://www.scienion.de **********************************
import java.sql.*; import java.io.*; import java.text.*;
class PrepareTest {
private Connection con=null;
private class sqlStmt {
boolean prepare;
boolean update;
String command;
sqlStmt(String c,boolean p,boolean u) {
prepare=p;
command =c;
update = u;
}
};
private sqlStmt[] stmtArray = {
new sqlStmt("drop table a if exists",false,false),
new sqlStmt("create cached table a (a int identity,b int)",false,false),
new sqlStmt("create index bIdx on a(b)",false,false),
new sqlStmt("insert into a(b) values(1)",true,true),
new sqlStmt("insert into a(b) values(2)",true,true),
new sqlStmt("insert into a(b) values(3)",true,true),
new sqlStmt("insert into a(b) values(4)",true,true),
new sqlStmt("insert into a(b) values(5)",true,true),
new sqlStmt("insert into a(b) values(6)",true,true),
new sqlStmt("update a set b=100 where b>(select b from a X where
X.a=2)",true,true),
new sqlStmt("update a set b=200 where b>(select b from a where
a=?)",true,true),
new sqlStmt("update a set b=300 where b>(select b from a X where
X.a=?)",true,true)
};
private Object[][] stmtArgs = {
{ },{ },{ },{ },{ },{ },{ },{ },{ },{},{new Integer(2)},{new Integer(2)}
};
public static void main (String[] argv) {
Connection con = null;
try {
String url = "jdbc:hsqldb:Test";
Class.forName("org.hsqldb.jdbcDriver");
con = java.sql.DriverManager.getConnection(url,"sa","");
System.err.println("SciSelect::connect -- connected to '" +url+"'");
} catch(Exception e) {
System.err.println(" ?? main: Caught Exception "+e);
System.err.println(" - SHIT - ");
return;
}
PrepareTest t=new PrepareTest(con);
boolean b=t.test();
System.err.println( b ? " -- OK -- " : " ?? FAILED ?? " );
System.exit(0);
}
public PrepareTest(Connection c) {
con = c;
}
public boolean test() {
try {
int i=0;
for(i=0;i < stmtArray.length;i++) {
int j;
System.err.println(" -- #"+i+" ----------------------- ");
if(stmtArray[i].prepare) {
PreparedStatement ps = null;
System.err.println(" -- preparing\n<<<\n" + stmtArray[i].command +
"\n>>>\n");
ps = con.prepareStatement(stmtArray[i].command);
System.err.print(" -- setting " + stmtArgs[i].length + " Args [");
for(j=0;j<stmtArgs[i].length;j++) {
System.err.print( (j>0 ? "; " : "" ) + stmtArgs[i][j]);
ps.setObject(j+1,stmtArgs[i][j]);
}
System.err.println("]");
System.err.println(" -- executing ");
if(stmtArray[i].update) {
int r = ps.executeUpdate();
System.err.println(" ***** ps.executeUpdate gave me "+r);
} else {
boolean b = ps.execute();
System.err.print(" ***** ps.execute gave me "+b);
}
} else {
System.err.println(" -- executing directly\n<<<\n" +
stmtArray[i].command + "\n>>>\n");
Statement s = con.createStatement();
boolean b = s.execute(stmtArray[i].command);
System.err.println(" ***** st.execute gave me "+b);
}
}
} catch(Exception e) {
System.err.println(" ?? Caught Exception "+e);
return false;
}
return true;
}
}
