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.DatabaseMetaData;
035: import java.sql.PreparedStatement;
036: import java.sql.ResultSet;
037: import java.sql.ResultSetMetaData;
038: import java.sql.SQLException;
039: import java.sql.Statement;
040: import java.sql.Types;
041:
042: import junit.framework.TestCase;
043: import junit.framework.TestResult;
044:
045: /**
046: * Test sql statements via jdbc against in-memory database
047: * @author fredt@users
048: */
049: public class TestSql extends TestBase {
050:
051: Statement stmnt;
052: PreparedStatement pstmnt;
053: Connection connection;
054: String getColumnName = "false";
055:
056: public TestSql(String name) {
057: super (name);
058: }
059:
060: protected void setUp() {
061:
062: super .setUp();
063:
064: try {
065: connection = super .newConnection();
066: stmnt = connection.createStatement();
067: } catch (Exception e) {
068: }
069: }
070:
071: public void testMetaData() {
072:
073: String ddl0 = "DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;";
074: String ddl1 = "CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
075: String ddl2 = "CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
076: String ddl3 = "CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
077: String result1 = "1";
078: String result2 = "2";
079: String result3 = "3";
080: String result4 = "4";
081: String result5 = "5";
082:
083: try {
084: stmnt.execute(ddl0);
085: stmnt.execute(ddl1);
086: stmnt.execute(ddl2);
087: stmnt.execute(ddl3);
088:
089: DatabaseMetaData md = connection.getMetaData();
090:
091: {
092:
093: // System.out.println(md.getDatabaseMajorVersion());
094: // System.out.println(md.getDatabaseMinorVersion());
095: System.out.println(md.getDatabaseProductName());
096: System.out.println(md.getDatabaseProductVersion());
097: System.out.println(md.getDefaultTransactionIsolation());
098: System.out.println(md.getDriverMajorVersion());
099: System.out.println(md.getDriverMinorVersion());
100: System.out.println(md.getDriverName());
101: System.out.println(md.getDriverVersion());
102: System.out.println(md.getExtraNameCharacters());
103: System.out.println(md.getIdentifierQuoteString());
104:
105: // System.out.println(md.getJDBCMajorVersion());
106: // System.out.println(md.getJDBCMinorVersion());
107: System.out.println(md.getMaxBinaryLiteralLength());
108: System.out.println(md.getMaxCatalogNameLength());
109: System.out.println(md.getMaxColumnsInGroupBy());
110: System.out.println(md.getMaxColumnsInIndex());
111: System.out.println(md.getMaxColumnsInOrderBy());
112: System.out.println(md.getMaxColumnsInSelect());
113: System.out.println(md.getMaxColumnsInTable());
114: System.out.println(md.getMaxConnections());
115: System.out.println(md.getMaxCursorNameLength());
116: System.out.println(md.getMaxIndexLength());
117: System.out.println(md.getMaxProcedureNameLength());
118: System.out.println(md.getMaxRowSize());
119: System.out.println(md.getMaxSchemaNameLength());
120: System.out.println(md.getMaxStatementLength());
121: System.out.println(md.getMaxStatements());
122: System.out.println(md.getMaxTableNameLength());
123: System.out.println(md.getMaxUserNameLength());
124: System.out.println(md.getNumericFunctions());
125: System.out.println(md.getProcedureTerm());
126:
127: // System.out.println(md.getResultSetHoldability());
128: System.out.println(md.getSchemaTerm());
129: System.out.println(md.getSearchStringEscape());
130: System.out.println(md.getSQLKeywords());
131:
132: // System.out.println(md.getSQLStateType());
133: System.out.println(md.getStringFunctions());
134: System.out.println(md.getSystemFunctions());
135: System.out.println(md.getTimeDateFunctions());
136: System.out.println(md.getURL());
137: System.out.println(md.getUserName());
138: System.out.println(DatabaseMetaData.importedKeyCascade);
139: System.out.println(md.isCatalogAtStart());
140: System.out.println(md.isReadOnly());
141:
142: ResultSet rs;
143:
144: rs = md.getPrimaryKeys(null, null, "USER");
145:
146: ResultSetMetaData rsmd = rs.getMetaData();
147: String result0 = "";
148:
149: for (; rs.next();) {
150: for (int i = 0; i < rsmd.getColumnCount(); i++) {
151: result0 += rs.getString(i + 1) + ":";
152: }
153:
154: result0 += "\n";
155: }
156:
157: rs.close();
158: System.out.println(result0);
159: }
160:
161: {
162: ResultSet rs;
163:
164: rs = md.getBestRowIdentifier(null, null, "USER", 0,
165: true);
166:
167: ResultSetMetaData rsmd = rs.getMetaData();
168: String result0 = "";
169:
170: for (; rs.next();) {
171: for (int i = 0; i < rsmd.getColumnCount(); i++) {
172: result0 += rs.getString(i + 1) + ":";
173: }
174:
175: result0 += "\n";
176: }
177:
178: rs.close();
179: System.out.println(result0);
180: }
181:
182: {
183: ResultSet rs = md.getImportedKeys(null, null,
184: "ADDRESSBOOK");
185: ResultSetMetaData rsmd = rs.getMetaData();
186:
187: result1 = "";
188:
189: for (; rs.next();) {
190: for (int i = 0; i < rsmd.getColumnCount(); i++) {
191: result1 += rs.getString(i + 1) + ":";
192: }
193:
194: result1 += "\n";
195: }
196:
197: rs.close();
198: System.out.println(result1);
199: }
200:
201: {
202: ResultSet rs = md.getCrossReference(null, null,
203: "ADDRESSBOOK_CATEGORY", null, null,
204: "ADDRESSBOOK");
205: ResultSetMetaData rsmd = rs.getMetaData();
206:
207: result2 = "";
208:
209: for (; rs.next();) {
210: for (int i = 0; i < rsmd.getColumnCount(); i++) {
211: result2 += rs.getString(i + 1) + ":";
212: }
213:
214: result2 += "\n";
215: }
216:
217: rs.close();
218: System.out.println(result2);
219: }
220:
221: {
222: ResultSet rs = md.getExportedKeys(null, null, "USER");
223: ResultSetMetaData rsmd = rs.getMetaData();
224:
225: result3 = "";
226:
227: for (; rs.next();) {
228: for (int i = 0; i < rsmd.getColumnCount(); i++) {
229: result3 += rs.getString(i + 1) + ":";
230: }
231:
232: result3 += "\n";
233: }
234:
235: rs.close();
236: System.out.println(result3);
237: }
238:
239: {
240: ResultSet rs = md.getCrossReference(null, null, "USER",
241: null, null, "ADDRESSBOOK_CATEGORY");
242: ResultSetMetaData rsmd = rs.getMetaData();
243:
244: result4 = "";
245:
246: for (; rs.next();) {
247: for (int i = 0; i < rsmd.getColumnCount(); i++) {
248: result4 += rs.getString(i + 1) + ":";
249: }
250:
251: result4 += "\n";
252: }
253:
254: rs.close();
255: System.out.println(result4);
256: }
257:
258: {
259: stmnt.execute("DROP TABLE T IF EXISTS;");
260: stmnt
261: .executeQuery("CREATE TABLE T (I IDENTITY, A CHAR(20), B CHAR(20));");
262: stmnt
263: .executeQuery("INSERT INTO T VALUES (NULL, 'get_column_name', '"
264: + getColumnName + "');");
265:
266: ResultSet rs = stmnt
267: .executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
268: ResultSetMetaData rsmd = rs.getMetaData();
269:
270: result5 = "";
271:
272: for (; rs.next();) {
273: for (int i = 0; i < rsmd.getColumnCount(); i++) {
274: result5 += rsmd.getColumnName(i + 1) + ":"
275: + rs.getString(i + 1) + ":";
276: }
277:
278: result5 += "\n";
279: }
280:
281: rs.close();
282:
283: rs = stmnt
284: .executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
285: ;
286: rsmd = rs.getMetaData();
287:
288: for (; rs.next();) {
289: for (int i = 0; i < rsmd.getColumnCount(); i++) {
290: result5 += rsmd.getColumnLabel(i + 1) + ":"
291: + rs.getString(i + 1) + ":";
292: }
293:
294: result5 += "\n";
295: }
296:
297: System.out.println(result5);
298: System.out.println("first column identity: "
299: + rsmd.isAutoIncrement(1));
300: rsmd.isCaseSensitive(1);
301: rsmd.isCurrency(1);
302: rsmd.isDefinitelyWritable(1);
303: rsmd.isNullable(1);
304: rsmd.isReadOnly(1);
305: rsmd.isSearchable(1);
306: rsmd.isSigned(1);
307: rsmd.isWritable(1);
308: rs.close();
309:
310: // test identity with PreparedStatement
311: pstmnt = connection
312: .prepareStatement("INSERT INTO T VALUES (?,?,?)");
313:
314: pstmnt.setString(1, null);
315: pstmnt.setString(2, "test");
316: pstmnt.setString(3, "test2");
317: pstmnt.executeUpdate();
318:
319: pstmnt = connection.prepareStatement("call identity()");
320:
321: ResultSet rsi = pstmnt.executeQuery();
322:
323: rsi.next();
324:
325: int identity = rsi.getInt(1);
326:
327: System.out.println("call identity(): " + identity);
328: rsi.close();
329: }
330: } catch (SQLException e) {
331: fail(e.getMessage());
332: }
333:
334: System.out.println("testMetaData complete");
335:
336: // assert equality of exported and imported with xref
337: assertEquals(result1, result2);
338: assertEquals(result3, result4);
339: }
340:
341: /**
342: * Demonstration of a reported bug.<p>
343: * Because all values were turned into strings with toString before
344: * PreparedStatement.executeQuery() was called, special values such as
345: * NaN were not accepted. In 1.7.0 these values are inserted as nulls
346: * (fredt)<b>
347: *
348: * This test can be extended to cover various conversions through JDBC
349: *
350: */
351: public void testDoubleNaN() {
352:
353: double value = 0;
354: boolean wasEqual = false;
355: String message = "DB operation completed";
356: String ddl1 = "DROP TABLE t1 IF EXISTS;"
357: + "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, "
358: + "dt DATE DEFAULT CURRENT_DATE, ti TIME DEFAULT CURRENT_TIME, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );";
359:
360: try {
361: stmnt.execute(ddl1);
362:
363: PreparedStatement ps = connection
364: .prepareStatement("INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)");
365:
366: ps.setString(1, "0.2");
367: ps.setDouble(2, 0.2);
368: ps.setLong(3, java.lang.Long.MAX_VALUE);
369: ps.setInt(4, Integer.MAX_VALUE);
370: ps.setInt(5, Short.MAX_VALUE);
371: ps.setInt(6, 0);
372: ps
373: .setDate(7, new java.sql.Date(System
374: .currentTimeMillis()));
375: ps
376: .setTime(8, new java.sql.Time(System
377: .currentTimeMillis()));
378: ps.setTimestamp(9, new java.sql.Timestamp(System
379: .currentTimeMillis()));
380: ps.execute();
381: ps.setInt(1, 0);
382: ps.setDouble(2, java.lang.Double.NaN);
383: ps.setLong(3, java.lang.Long.MIN_VALUE);
384: ps.setInt(4, Integer.MIN_VALUE);
385: ps.setInt(5, Short.MIN_VALUE);
386: ps.setInt(6, 0);
387:
388: // allowed conversions
389: ps.setTimestamp(7, new java.sql.Timestamp(System
390: .currentTimeMillis() + 1));
391: ps.setTime(8, new java.sql.Time(
392: System.currentTimeMillis() + 1));
393: ps.setDate(9, new java.sql.Date(
394: System.currentTimeMillis() + 1));
395: ps.execute();
396:
397: //
398: ps.setInt(1, 0);
399: ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY);
400: ps.setInt(4, Integer.MIN_VALUE);
401:
402: // test conversion
403: ps.setObject(5, Boolean.TRUE);
404: ps.setBoolean(5, true);
405: ps.setObject(5, new Short((short) 2), Types.SMALLINT);
406: ps.setObject(6, new Integer(2), Types.TINYINT);
407:
408: // allowed conversions
409: ps.setObject(7, new java.sql.Date(System
410: .currentTimeMillis() + 2));
411: ps.setObject(8, new java.sql.Time(System
412: .currentTimeMillis() + 2));
413: ps.setObject(9, new java.sql.Timestamp(System
414: .currentTimeMillis() + 2));
415: ps.execute();
416: ps.setObject(1, new Float(0), Types.INTEGER);
417: ps.setObject(4, new Float(1), Types.INTEGER);
418: ps.setDouble(2, java.lang.Double.NEGATIVE_INFINITY);
419: ps.execute();
420:
421: ResultSet rs = stmnt
422: .executeQuery("SELECT d, f, l, i, s*2, t FROM t1");
423: boolean result = rs.next();
424:
425: value = rs.getDouble(2);
426:
427: // int smallintValue = rs.getShort(3);
428: int integerValue = rs.getInt(4);
429:
430: if (rs.next()) {
431: value = rs.getDouble(2);
432: wasEqual = Double.isNaN(value);
433: integerValue = rs.getInt(4);
434:
435: // tests for conversion
436: // getInt on DECIMAL
437: integerValue = rs.getInt(1);
438: }
439:
440: if (rs.next()) {
441: value = rs.getDouble(2);
442: wasEqual = wasEqual
443: && value == Double.POSITIVE_INFINITY;
444: }
445:
446: if (rs.next()) {
447: value = rs.getDouble(2);
448: wasEqual = wasEqual
449: && value == Double.NEGATIVE_INFINITY;
450: }
451:
452: rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");
453:
454: if (rs.next()) {
455: int max = rs.getInt(1);
456:
457: System.out.println("Max value for i: " + max);
458: }
459:
460: {
461: stmnt.execute("drop table CDTYPE if exists");
462:
463: // test for the value MAX(column) in an empty table
464: rs = stmnt
465: .executeQuery("CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))");
466: rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType");
467:
468: if (rs.next()) {
469: int max = rs.getInt(1);
470:
471: System.out.println("Max value for ID: " + max);
472: } else {
473: System.out.println("Max value for ID not returned");
474: }
475:
476: stmnt
477: .executeUpdate("INSERT INTO cdType VALUES (10,'Test String');");
478: stmnt.executeQuery("CALL IDENTITY();");
479:
480: try {
481: stmnt
482: .executeUpdate("INSERT INTO cdType VALUES (10,'Test String');");
483: } catch (SQLException e1) {
484: stmnt.execute("ROLLBACK");
485: connection.rollback();
486: }
487: }
488: } catch (SQLException e) {
489: fail(e.getMessage());
490: }
491:
492: System.out.println("testDoubleNaN complete");
493:
494: // assert new behaviour
495: assertEquals(true, wasEqual);
496: }
497:
498: public void testAny() {
499:
500: try {
501: String ddl = "drop table PRICE_RELATE_USER_ORDER_V2 if exists;"
502: + "create table PRICE_RELATE_USER_ORDER_V2 "
503: + "(ID_ORDER_V2 BIGINT, ID_USER NUMERIC, DATE_CREATE TIMESTAMP)";
504: String sql = "insert into PRICE_RELATE_USER_ORDER_V2 "
505: + "(ID_ORDER_V2, ID_USER, DATE_CREATE) "
506: + "values " + "(?, ?, ?)";
507: Statement st = connection.createStatement();
508:
509: st.execute(ddl);
510:
511: PreparedStatement ps = connection.prepareStatement(sql);
512:
513: ps.setLong(1, 1);
514: ps.setNull(2, Types.NUMERIC);
515: ps.setTimestamp(3, new java.sql.Timestamp(System
516: .currentTimeMillis()));
517: ps.execute();
518: } catch (SQLException e) {
519: e.printStackTrace();
520: System.out.println("TestSql.testAny() error: "
521: + e.getMessage());
522: }
523:
524: System.out.println("testAny complete");
525: }
526:
527: /**
528: * Fix for bug #1201135
529: */
530: public void testBinds() {
531:
532: try {
533: PreparedStatement pstmt = connection
534: .prepareStatement("drop table test if exists");
535:
536: pstmt.execute();
537:
538: pstmt = connection
539: .prepareStatement("create table test (id integer)");
540:
541: pstmt.execute();
542:
543: pstmt = connection
544: .prepareStatement("insert into test values (10)");
545:
546: pstmt.execute();
547:
548: pstmt = connection
549: .prepareStatement("insert into test values (20)");
550:
551: pstmt.execute();
552:
553: pstmt = connection
554: .prepareStatement("select count(*) from test where ? is null");
555:
556: pstmt.setString(1, "hello");
557:
558: ResultSet rs = pstmt.executeQuery();
559:
560: rs.next();
561:
562: int count = rs.getInt(1);
563:
564: assertEquals(0, count);
565:
566: pstmt = connection
567: .prepareStatement("select limit ? 1 id from test");
568:
569: pstmt.setInt(1, 0);
570:
571: rs = pstmt.executeQuery();
572:
573: rs.next();
574:
575: count = rs.getInt(1);
576:
577: assertEquals(10, count);
578: pstmt.setInt(1, 1);
579:
580: rs = pstmt.executeQuery();
581:
582: rs.next();
583:
584: count = rs.getInt(1);
585:
586: assertEquals(20, count);
587: } catch (SQLException e) {
588: e.printStackTrace();
589: System.out.println("TestSql.testBinds() error: "
590: + e.getMessage());
591: }
592: }
593:
594: public void testBinds2() {
595:
596: try {
597: PreparedStatement pstmt = connection
598: .prepareStatement("drop table test if exists");
599:
600: pstmt.execute();
601:
602: pstmt = connection
603: .prepareStatement("create table test (id integer, txt varchar(10))");
604:
605: pstmt.execute();
606:
607: pstmt = connection
608: .prepareStatement("insert into test values (10, 'hello')");
609:
610: pstmt.execute();
611:
612: pstmt = connection
613: .prepareStatement("select txt from test where id = ?");
614:
615: pstmt.setInt(1, 10);
616: pstmt.execute();
617:
618: ResultSet rs = pstmt.getResultSet();
619:
620: rs.next();
621:
622: String value = rs.getString(1);
623:
624: assertEquals("hello", value);
625:
626: pstmt = connection
627: .prepareStatement("select count(*) from test where id = ?");
628:
629: pstmt.setInt(1, 10);
630: pstmt.execute();
631:
632: rs = pstmt.getResultSet();
633:
634: rs.next();
635:
636: int count = rs.getInt(1);
637:
638: assertEquals(1, count);
639: } catch (SQLException e) {
640: e.printStackTrace();
641: System.out.println("TestSql.testBinds() error: "
642: + e.getMessage());
643: }
644: }
645:
646: // miscellaneous tests
647: public void testX1() {
648:
649: String tableDDL = "create table lo_attribute ( "
650: + "learningid varchar(15) not null, "
651: + "ordering integer not null,"
652: + "attribute_value_data varchar(85) null,"
653: + "constraint PK_LO_ATTR primary key (learningid, ordering))";
654:
655: try {
656: Statement stmt = connection.createStatement();
657:
658: stmt.execute("drop table lo_attribute if exists");
659: stmt.execute(tableDDL);
660: stmt
661: .execute("insert into lo_attribute values('abcd', 10, 'cdef')");
662: stmt
663: .execute("insert into lo_attribute values('bcde', 20, 'cdef')");
664: } catch (SQLException e) {
665: assertEquals(0, 1);
666: }
667:
668: try {
669: String prepared = "update lo_attribute set "
670: + " ordering = (ordering - 1) where ordering > ?";
671: PreparedStatement ps = connection
672: .prepareStatement(prepared);
673:
674: ps.setInt(1, 10);
675: ps.execute();
676: } catch (SQLException e) {
677: assertEquals(0, 1);
678: }
679:
680: try {
681: connection.setAutoCommit(false);
682:
683: java.sql.Savepoint savepoint = connection
684: .setSavepoint("savepoint");
685:
686: connection.createStatement().executeQuery("CALL true;");
687: connection.rollback(savepoint);
688: } catch (SQLException e) {
689: assertEquals(0, 1);
690: }
691: }
692:
693: /**
694: * In 1.8.0.2, this fails in client / server due to column type of the
695: * second select for b1 being boolean, while the first select is interpreted
696: * as varchar. The rowOutputBase class attempts to cast the Java Boolean
697: * into String.
698: */
699: public void testUnionColumnTypes() {
700:
701: try {
702: Connection conn = newConnection();
703: Statement stmt = conn.createStatement();
704:
705: stmt.execute("DROP TABLE test1 IF EXISTS");
706: stmt.execute("DROP TABLE test2 IF EXISTS");
707: stmt.execute("CREATE TABLE test1 (id int, b1 boolean)");
708: stmt.execute("CREATE TABLE test2 (id int)");
709: stmt.execute("INSERT INTO test1 VALUES(1,true)");
710: stmt.execute("INSERT INTO test2 VALUES(2)");
711:
712: ResultSet rs = stmt
713: .executeQuery("select id,null as b1 from test2 union select id, b1 from test1");
714: Boolean[] array = new Boolean[2];
715:
716: for (int i = 0; rs.next(); i++) {
717: boolean boole = rs.getBoolean(2);
718:
719: array[i] = Boolean.valueOf(boole);
720:
721: if (rs.wasNull()) {
722: array[i] = null;
723: }
724: }
725:
726: boolean result = (array[0] == null && array[1] == Boolean.TRUE)
727: || (array[0] == Boolean.TRUE && array[1] == null);
728:
729: assertTrue(result);
730: } catch (SQLException e) {
731: e.printStackTrace();
732: System.out.println("TestSql.testUnionColumnType() error: "
733: + e.getMessage());
734: }
735: }
736:
737: protected void tearDown() {
738:
739: try {
740: connection.close();
741: } catch (Exception e) {
742: e.printStackTrace();
743: System.out.println("TestSql.tearDown() error: "
744: + e.getMessage());
745: }
746: }
747:
748: public static void main(String[] argv) {
749:
750: TestResult result = new TestResult();
751: TestCase testA = new TestSql("testMetaData");
752: TestCase testB = new TestSql("testDoubleNaN");
753: TestCase testC = new TestSql("testAny");
754:
755: testA.run(result);
756: testB.run(result);
757: testC.run(result);
758: System.out.println("TestSql error count: "
759: + result.failureCount());
760: }
761: }
|