Thanks again. Will review the test case and continue to work on the problem.
Yes: Sorry about lack of support for prepared DDL. That's coming, but almost certainly without parametric support. ----- Original Message ----- From: "Sebastian Kloska" <[EMAIL PROTECTED]> To: "HSQLDB Developers" <[EMAIL PROTECTED]>; "Campbell Boucher-Burnet" <[EMAIL PROTECTED]> Sent: Friday, September 12, 2003 7:51 AM Subject: Re: [Hsqldb-developers] Subqueries in prepared Statements ? > Hi .... > > 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; > } > } > ------------------------------------------------------- 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
