001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.PreparedStatement;
035: import java.sql.Statement;
036:
037: /**
038: * @author kloska@users
039: */
040: class TestPreparedSubQueries {
041:
042: private Connection con = null;
043:
044: private class sqlStmt {
045:
046: boolean prepare;
047: boolean update;
048: String command;
049:
050: sqlStmt(String c, boolean p, boolean u) {
051:
052: prepare = p;
053: command = c;
054: update = u;
055: }
056: };
057:
058: private sqlStmt[] stmtArray = {
059: new sqlStmt("drop table a if exists", false, false),
060: new sqlStmt("create cached table a (a int identity,b int)",
061: false, false),
062: new sqlStmt("create index bIdx on a(b)", false, false),
063: new sqlStmt("insert into a(b) values(1)", true, true),
064: new sqlStmt("insert into a(b) values(2)", true, true),
065: new sqlStmt("insert into a(b) values(3)", true, true),
066: new sqlStmt("insert into a(b) values(4)", true, true),
067: new sqlStmt("insert into a(b) values(5)", true, true),
068: new sqlStmt("insert into a(b) values(6)", true, true),
069: new sqlStmt(
070: "update a set b=100 where b>(select b from a X where X.a=2)",
071: true, true),
072: new sqlStmt(
073: "update a set b=200 where b>(select b from a where a=?)",
074: true, true),
075: new sqlStmt(
076: "update a set b=300 where b>(select b from a X where X.a=?)",
077: true, true) };
078: private Object[][] stmtArgs = { {}, {}, {}, {}, {}, {}, {}, {}, {},
079: {}, { new Integer(2) }, { new Integer(2) } };
080:
081: public static void main(String[] argv) {
082:
083: Connection con = null;
084:
085: try {
086: String url = "jdbc:hsqldb:test";
087:
088: Class.forName("org.hsqldb.jdbcDriver");
089:
090: con = java.sql.DriverManager.getConnection(url, "sa", "");
091:
092: System.out.println("SciSelect::connect -- connected to '"
093: + url + "'");
094: } catch (Exception e) {
095: System.out.println(" ?? main: Caught Exception " + e);
096: System.out.println(" - FAILED - ");
097:
098: return;
099: }
100:
101: TestPreparedSubQueries t = new TestPreparedSubQueries(con);
102: boolean b = t.test();
103:
104: System.out.println(b ? " -- OK -- " : " ?? FAILED ?? ");
105: System.exit(0);
106: }
107:
108: public TestPreparedSubQueries(Connection c) {
109: con = c;
110: }
111:
112: public boolean test() {
113:
114: try {
115: int i = 0;
116:
117: for (i = 0; i < stmtArray.length; i++) {
118: int j;
119:
120: System.out.println(" -- #" + i
121: + " ----------------------- ");
122:
123: if (stmtArray[i].prepare) {
124: PreparedStatement ps = null;
125:
126: System.out.println(" -- preparing\n<<<\n"
127: + stmtArray[i].command + "\n>>>\n");
128:
129: ps = con.prepareStatement(stmtArray[i].command);
130:
131: System.out.print(" -- setting "
132: + stmtArgs[i].length + " Args [");
133:
134: for (j = 0; j < stmtArgs[i].length; j++) {
135: System.out.print((j > 0 ? "; " : "")
136: + stmtArgs[i][j]);
137: ps.setObject(j + 1, stmtArgs[i][j]);
138: }
139:
140: System.out.println("]");
141: System.out.println(" -- executing ");
142:
143: if (stmtArray[i].update) {
144: int r = ps.executeUpdate();
145:
146: System.out
147: .println(" ***** ps.executeUpdate gave me "
148: + r);
149: } else {
150: boolean b = ps.execute();
151:
152: System.out.print(" ***** ps.execute gave me "
153: + b);
154: }
155: } else {
156: System.out.println(" -- executing directly\n<<<\n"
157: + stmtArray[i].command + "\n>>>\n");
158:
159: Statement s = con.createStatement();
160: boolean b = s.execute(stmtArray[i].command);
161:
162: System.out
163: .println(" ***** st.execute gave me " + b);
164: }
165: }
166: } catch (Exception e) {
167: System.out.println(" ?? Caught Exception " + e);
168:
169: return false;
170: }
171:
172: return true;
173: }
174: }
|