001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.jdbc;
007:
008: import java.sql.Connection;
009: import java.sql.ResultSet;
010: import java.sql.SQLException;
011: import java.sql.Savepoint;
012: import java.sql.Statement;
013:
014: import org.h2.constant.SysProperties;
015: import org.h2.jdbc.JdbcStatement;
016: import org.h2.test.TestBase;
017:
018: /**
019: * Tests for the Statement implementation.
020: */
021: public class TestStatement extends TestBase {
022:
023: Connection conn;
024:
025: public void test() throws Exception {
026: deleteDb("statement");
027: conn = getConnection("statement");
028: if (config.jdk14) {
029: testSavepoint();
030: }
031: testConnectionRollback();
032: testStatement();
033: if (config.jdk14) {
034: testIdentity();
035: }
036: conn.close();
037: }
038:
039: private void testConnectionRollback() throws Exception {
040: Statement stat = conn.createStatement();
041: conn.setAutoCommit(false);
042: stat
043: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
044: stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
045: conn.rollback();
046: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
047: checkFalse(rs.next());
048: stat.execute("DROP TABLE TEST");
049: conn.setAutoCommit(true);
050: }
051:
052: void testSavepoint() throws Exception {
053: Statement stat = conn.createStatement();
054: stat
055: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
056: conn.setAutoCommit(false);
057: stat.execute("INSERT INTO TEST VALUES(0, 'Hi')");
058: Savepoint savepoint1 = conn.setSavepoint();
059: int id1 = savepoint1.getSavepointId();
060: try {
061: savepoint1.getSavepointName();
062: error();
063: } catch (SQLException e) {
064: checkNotGeneralException(e);
065: }
066: stat.execute("DELETE FROM TEST");
067: conn.rollback(savepoint1);
068: stat.execute("UPDATE TEST SET NAME='Hello'");
069: Savepoint savepoint2a = conn.setSavepoint();
070: Savepoint savepoint2 = conn.setSavepoint();
071: conn.releaseSavepoint(savepoint2a);
072: try {
073: savepoint2a.getSavepointId();
074: error();
075: } catch (SQLException e) {
076: checkNotGeneralException(e);
077: }
078: int id2 = savepoint2.getSavepointId();
079: check(id1 != id2);
080: stat.execute("UPDATE TEST SET NAME='Hallo' WHERE NAME='Hello'");
081: Savepoint savepointTest = conn.setSavepoint("Joe's");
082: stat.execute("DELETE FROM TEST");
083: check(savepointTest.getSavepointName(), "Joe's");
084: try {
085: savepointTest.getSavepointId();
086: error();
087: } catch (SQLException e) {
088: checkNotGeneralException(e);
089: }
090: conn.rollback(savepointTest);
091: conn.commit();
092: ResultSet rs = stat.executeQuery("SELECT NAME FROM TEST");
093: rs.next();
094: String name = rs.getString(1);
095: check(name, "Hallo");
096: checkFalse(rs.next());
097: try {
098: conn.rollback(savepoint2);
099: error();
100: } catch (SQLException e) {
101: checkNotGeneralException(e);
102: }
103: stat.execute("DROP TABLE TEST");
104: conn.setAutoCommit(true);
105: }
106:
107: void testStatement() throws Exception {
108:
109: Statement stat = conn.createStatement();
110:
111: //#ifdef JDK14
112: check(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability());
113: conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
114: check(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability());
115: //#endif
116:
117: // ignored
118: stat.setCursorName("x");
119: // fixed return value
120: check(stat.getFetchDirection(), ResultSet.FETCH_FORWARD);
121: // ignored
122: stat.setFetchDirection(ResultSet.FETCH_REVERSE);
123: // ignored
124: stat.setMaxFieldSize(100);
125:
126: check(SysProperties.SERVER_RESULT_SET_FETCH_SIZE, stat
127: .getFetchSize());
128: stat.setFetchSize(10);
129: check(10, stat.getFetchSize());
130: stat.setFetchSize(0);
131: check(SysProperties.SERVER_RESULT_SET_FETCH_SIZE, stat
132: .getFetchSize());
133: check(ResultSet.TYPE_FORWARD_ONLY, stat.getResultSetType());
134: Statement stat2 = conn.createStatement(
135: ResultSet.TYPE_SCROLL_SENSITIVE,
136: ResultSet.CONCUR_READ_ONLY,
137: ResultSet.HOLD_CURSORS_OVER_COMMIT);
138: check(ResultSet.TYPE_SCROLL_SENSITIVE, stat2.getResultSetType());
139: check(ResultSet.HOLD_CURSORS_OVER_COMMIT, stat2
140: .getResultSetHoldability());
141: check(ResultSet.CONCUR_UPDATABLE, stat2
142: .getResultSetConcurrency());
143: check(0, stat.getMaxFieldSize());
144: check(!((JdbcStatement) stat2).isClosed());
145: stat2.close();
146: check(((JdbcStatement) stat2).isClosed());
147:
148: ResultSet rs;
149: int count;
150: boolean result;
151:
152: stat.execute("CREATE TABLE TEST(ID INT)");
153: stat.execute("SELECT * FROM TEST");
154: stat.execute("DROP TABLE TEST");
155:
156: conn.getTypeMap();
157:
158: // this method should not throw an exception - if not supported, this
159: // calls are ignored
160:
161: if (config.jdk14) {
162: check(stat.getResultSetHoldability(),
163: ResultSet.HOLD_CURSORS_OVER_COMMIT);
164: }
165: check(stat.getResultSetConcurrency(),
166: ResultSet.CONCUR_UPDATABLE);
167:
168: stat.cancel();
169: stat.setQueryTimeout(10);
170: check(stat.getQueryTimeout() == 10);
171: stat.setQueryTimeout(0);
172: check(stat.getQueryTimeout() == 0);
173: // this is supposed to throw an exception
174: try {
175: stat.setQueryTimeout(-1);
176: error("setQueryTimeout(-1) didn't throw an exception");
177: } catch (SQLException e) {
178: checkNotGeneralException(e);
179: }
180: check(stat.getQueryTimeout() == 0);
181: trace("executeUpdate");
182: count = stat
183: .executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
184: check(count, 0);
185: count = stat
186: .executeUpdate("INSERT INTO TEST VALUES(1,'Hello')");
187: check(count, 1);
188: count = stat
189: .executeUpdate("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
190: check(count, 1);
191: count = stat
192: .executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE ID=2 OR ID=1");
193: check(count, 2);
194: count = stat
195: .executeUpdate("UPDATE TEST SET VALUE='\\LDBC\\' WHERE VALUE LIKE 'LDBC' ");
196: check(count, 2);
197: count = stat
198: .executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE VALUE LIKE '\\\\LDBC\\\\'");
199: trace("count:" + count);
200: check(count, 2);
201: count = stat.executeUpdate("DELETE FROM TEST WHERE ID=-1");
202: check(count, 0);
203: count = stat.executeUpdate("DELETE FROM TEST WHERE ID=2");
204: check(count, 1);
205: try {
206: stat.executeUpdate("SELECT * FROM TEST");
207: error("executeUpdate allowed SELECT");
208: } catch (SQLException e) {
209: checkNotGeneralException(e);
210: trace("no error - SELECT not allowed with executeUpdate");
211: }
212: count = stat.executeUpdate("DROP TABLE TEST");
213: check(count == 0);
214:
215: trace("execute");
216: result = stat
217: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
218: check(!result);
219: result = stat.execute("INSERT INTO TEST VALUES(1,'Hello')");
220: check(!result);
221: result = stat
222: .execute("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
223: check(!result);
224: result = stat
225: .execute("UPDATE TEST SET VALUE='LDBC' WHERE ID=2");
226: check(!result);
227: result = stat.execute("DELETE FROM TEST WHERE ID=3");
228: check(!result);
229: result = stat.execute("SELECT * FROM TEST");
230: check(result);
231: result = stat.execute("DROP TABLE TEST");
232: check(!result);
233:
234: trace("executeQuery");
235: try {
236: stat
237: .executeQuery("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
238: error("executeQuery allowed CREATE TABLE");
239: } catch (SQLException e) {
240: checkNotGeneralException(e);
241: trace("no error - CREATE not allowed with executeQuery");
242: }
243: stat
244: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
245: try {
246: stat.executeQuery("INSERT INTO TEST VALUES(1,'Hello')");
247: error("executeQuery allowed INSERT");
248: } catch (SQLException e) {
249: checkNotGeneralException(e);
250: trace("no error - INSERT not allowed with executeQuery");
251: }
252: try {
253: stat
254: .executeQuery("UPDATE TEST SET VALUE='LDBC' WHERE ID=2");
255: error("executeQuery allowed UPDATE");
256: } catch (SQLException e) {
257: checkNotGeneralException(e);
258: trace("no error - UPDATE not allowed with executeQuery");
259: }
260: try {
261: stat.executeQuery("DELETE FROM TEST WHERE ID=3");
262: error("executeQuery allowed DELETE");
263: } catch (SQLException e) {
264: checkNotGeneralException(e);
265: trace("no error - DELETE not allowed with executeQuery");
266: }
267: stat.executeQuery("SELECT * FROM TEST");
268: try {
269: stat.executeQuery("DROP TABLE TEST");
270: error("executeQuery allowed DROP");
271: } catch (SQLException e) {
272: checkNotGeneralException(e);
273: trace("no error - DROP not allowed with executeQuery");
274: }
275: // getMoreResults
276: rs = stat.executeQuery("SELECT * FROM TEST");
277: checkFalse(stat.getMoreResults());
278: try {
279: // supposed to be closed now
280: rs.next();
281: error("getMoreResults didn't close this result set");
282: } catch (SQLException e) {
283: checkNotGeneralException(e);
284: trace("no error - getMoreResults is supposed to close the result set");
285: }
286: check(stat.getUpdateCount() == -1);
287: count = stat.executeUpdate("DELETE FROM TEST");
288: checkFalse(stat.getMoreResults());
289: check(stat.getUpdateCount() == -1);
290:
291: stat.execute("DROP TABLE TEST");
292: stat.executeUpdate("DROP TABLE IF EXISTS TEST");
293:
294: check(stat.getWarnings() == null);
295: stat.clearWarnings();
296: check(stat.getWarnings() == null);
297: check(conn == stat.getConnection());
298:
299: stat.close();
300: }
301:
302: private void testIdentity() throws Exception {
303: Statement stat = conn.createStatement();
304: stat.execute("CREATE SEQUENCE SEQ");
305: stat.execute("CREATE TABLE TEST(ID INT)");
306: stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)");
307: ResultSet rs = stat.getGeneratedKeys();
308: rs.next();
309: check(rs.getInt(1), 1);
310: checkFalse(rs.next());
311: stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
312: Statement.RETURN_GENERATED_KEYS);
313: rs = stat.getGeneratedKeys();
314: rs.next();
315: check(rs.getInt(1), 2);
316: checkFalse(rs.next());
317: stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
318: new int[] { 1 });
319: rs = stat.getGeneratedKeys();
320: rs.next();
321: check(rs.getInt(1), 3);
322: checkFalse(rs.next());
323: stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
324: new String[] { "ID" });
325: rs = stat.getGeneratedKeys();
326: rs.next();
327: check(rs.getInt(1), 4);
328: checkFalse(rs.next());
329: stat.executeUpdate(
330: "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
331: Statement.RETURN_GENERATED_KEYS);
332: rs = stat.getGeneratedKeys();
333: rs.next();
334: check(rs.getInt(1), 5);
335: checkFalse(rs.next());
336: stat.executeUpdate(
337: "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
338: new int[] { 1 });
339: rs = stat.getGeneratedKeys();
340: rs.next();
341: check(rs.getInt(1), 6);
342: checkFalse(rs.next());
343: stat.executeUpdate(
344: "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
345: new String[] { "ID" });
346: rs = stat.getGeneratedKeys();
347: rs.next();
348: check(rs.getInt(1), 7);
349: checkFalse(rs.next());
350: stat.execute("DROP TABLE TEST");
351: }
352:
353: }
|