| /* Copyright (c) 2001-2010, The HSQL Development Group |
| * All rights reserved. |
| * |
| * Redistribution and use in source and binary forms, with or without |
| * modification, are permitted provided that the following conditions are met: |
| * |
| * Redistributions of source code must retain the above copyright notice, this |
| * list of conditions and the following disclaimer. |
| * |
| * Redistributions in binary form must reproduce the above copyright notice, |
| * this list of conditions and the following disclaimer in the documentation |
| * and/or other materials provided with the distribution. |
| * |
| * Neither the name of the HSQL Development Group nor the names of its |
| * contributors may be used to endorse or promote products derived from this |
| * software without specific prior written permission. |
| * |
| * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
| * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
| * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE |
| * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, |
| * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, |
| * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, |
| * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; |
| * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND |
| * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT |
| * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS |
| * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
| */ |
| |
| |
| package org.hsqldb.test; |
| |
| import java.sql.Connection; |
| import java.sql.DatabaseMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Types; |
| |
| import junit.framework.TestCase; |
| import junit.framework.TestResult; |
| import java.sql.Date; |
| |
| /** |
| * Test sql statements via jdbc against in-memory database |
| * @author Fred Toussi (fredt@users dot sourceforge.net) |
| */ |
| public class TestSql extends TestBase { |
| |
| Statement stmnt; |
| PreparedStatement pstmnt; |
| Connection connection; |
| String getColumnName = "false"; |
| |
| public TestSql(String name) { |
| super(name); |
| } |
| |
| protected void setUp() { |
| |
| super.setUp(); |
| |
| try { |
| connection = super.newConnection(); |
| stmnt = connection.createStatement(); |
| } catch (Exception e) {} |
| } |
| |
| public void testMetaData() { |
| |
| String ddl0 = |
| "DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;"; |
| 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))"; |
| 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)"; |
| 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)"; |
| String result1 = "1"; |
| String result2 = "2"; |
| String result3 = "3"; |
| String result4 = "4"; |
| String result5 = "5"; |
| |
| try { |
| stmnt.execute(ddl0); |
| stmnt.execute(ddl1); |
| stmnt.execute(ddl2); |
| stmnt.execute(ddl3); |
| |
| DatabaseMetaData md = connection.getMetaData(); |
| |
| { |
| System.out.println("Testing DatabaseMetaData methods"); |
| |
| System.out.println(md.getDatabaseMajorVersion()); |
| System.out.println(md.getDatabaseMinorVersion()); |
| System.out.println(md.getDatabaseProductName()); |
| System.out.println(md.getDatabaseProductVersion()); |
| System.out.println(md.getDefaultTransactionIsolation()); |
| System.out.println(md.getDriverMajorVersion()); |
| System.out.println(md.getDriverMinorVersion()); |
| System.out.println(md.getDriverName()); |
| System.out.println(md.getDriverVersion()); |
| System.out.println(md.getExtraNameCharacters()); |
| System.out.println(md.getIdentifierQuoteString()); |
| |
| System.out.println(md.getJDBCMajorVersion()); |
| System.out.println(md.getJDBCMinorVersion()); |
| System.out.println(md.getMaxBinaryLiteralLength()); |
| System.out.println(md.getMaxCatalogNameLength()); |
| System.out.println(md.getMaxColumnsInGroupBy()); |
| System.out.println(md.getMaxColumnsInIndex()); |
| System.out.println(md.getMaxColumnsInOrderBy()); |
| System.out.println(md.getMaxColumnsInSelect()); |
| System.out.println(md.getMaxColumnsInTable()); |
| System.out.println(md.getMaxConnections()); |
| System.out.println(md.getMaxCursorNameLength()); |
| System.out.println(md.getMaxIndexLength()); |
| System.out.println(md.getMaxProcedureNameLength()); |
| System.out.println(md.getMaxRowSize()); |
| System.out.println(md.getMaxSchemaNameLength()); |
| System.out.println(md.getMaxStatementLength()); |
| System.out.println(md.getMaxStatements()); |
| System.out.println(md.getMaxTableNameLength()); |
| System.out.println(md.getMaxUserNameLength()); |
| System.out.println(md.getNumericFunctions()); |
| System.out.println(md.getProcedureTerm()); |
| |
| System.out.println(md.getResultSetHoldability()); |
| System.out.println(md.getSchemaTerm()); |
| System.out.println(md.getSearchStringEscape()); |
| System.out.println( |
| "Testing DatabaseMetaData.getSQLKeywords()"); |
| System.out.println(md.getSQLKeywords()); |
| |
| System.out.println(md.getSQLStateType()); |
| System.out.println( |
| "Testing DatabaseMetaData.getStringFunctions()"); |
| System.out.println(md.getStringFunctions()); |
| System.out.println( |
| "Testing DatabaseMetaData.getSystemFunctions()"); |
| System.out.println(md.getSystemFunctions()); |
| System.out.println( |
| "Testing DatabaseMetaData.getTimeDateFunctions()"); |
| System.out.println(md.getTimeDateFunctions()); |
| System.out.println(md.getURL()); |
| System.out.println(md.getUserName()); |
| System.out.println(DatabaseMetaData.importedKeyCascade); |
| System.out.println(md.isCatalogAtStart()); |
| System.out.println(md.isReadOnly()); |
| |
| ResultSet rs; |
| |
| rs = md.getPrimaryKeys(null, null, "USER"); |
| |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| String result0 = ""; |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result0 += rs.getString(i + 1) + ":"; |
| } |
| |
| result0 += "\n"; |
| } |
| |
| rs.close(); |
| System.out.println(result0); |
| } |
| |
| { |
| ResultSet rs; |
| |
| rs = md.getBestRowIdentifier(null, null, "USER", 0, true); |
| |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| String result0 = ""; |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result0 += rs.getString(i + 1) + ":"; |
| } |
| |
| result0 += "\n"; |
| } |
| |
| rs.close(); |
| System.out.println(result0); |
| } |
| |
| { |
| ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK"); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| |
| result1 = ""; |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result1 += rs.getString(i + 1) + ":"; |
| } |
| |
| result1 += "\n"; |
| } |
| |
| rs.close(); |
| System.out.println(result1); |
| } |
| |
| { |
| ResultSet rs = md.getCrossReference(null, null, |
| "ADDRESSBOOK_CATEGORY", |
| null, null, "ADDRESSBOOK"); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| |
| result2 = ""; |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result2 += rs.getString(i + 1) + ":"; |
| } |
| |
| result2 += "\n"; |
| } |
| |
| rs.close(); |
| System.out.println(result2); |
| } |
| |
| { |
| ResultSet rs = md.getExportedKeys(null, null, "USER"); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| |
| result3 = ""; |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result3 += rs.getString(i + 1) + ":"; |
| } |
| |
| result3 += "\n"; |
| } |
| |
| rs.close(); |
| System.out.println(result3); |
| } |
| |
| { |
| ResultSet rs = md.getCrossReference(null, null, "USER", null, |
| null, |
| "ADDRESSBOOK_CATEGORY"); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| |
| result4 = ""; |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result4 += rs.getString(i + 1) + ":"; |
| } |
| |
| result4 += "\n"; |
| } |
| |
| rs.close(); |
| System.out.println(result4); |
| } |
| |
| { |
| stmnt.execute("DROP TABLE T IF EXISTS;"); |
| stmnt.execute( |
| "CREATE TABLE T (I IDENTITY, A CHAR(20), B CHAR(20));"); |
| stmnt.execute( |
| "INSERT INTO T VALUES (NULL, 'get_column_name', '" |
| + getColumnName + "');"); |
| |
| ResultSet rs = stmnt.executeQuery( |
| "SELECT I, A, B, A \"aliasA\", B \"aliasB\", 1 FROM T;"); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| |
| result5 = ""; |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result5 += rsmd.getColumnName(i + 1) + ":" |
| + rs.getString(i + 1) + ":"; |
| } |
| |
| result5 += "\n"; |
| } |
| |
| rs.close(); |
| |
| rs = stmnt.executeQuery( |
| "SELECT I, A, B, A \"aliasA\", B \"aliasB\", 1 FROM T;");; |
| rsmd = rs.getMetaData(); |
| |
| for (; rs.next(); ) { |
| for (int i = 0; i < rsmd.getColumnCount(); i++) { |
| result5 += rsmd.getColumnLabel(i + 1) + ":" |
| + rs.getString(i + 1) + ":"; |
| } |
| |
| result5 += "\n"; |
| } |
| |
| System.out.println(result5); |
| System.out.println("first column identity: " |
| + rsmd.isAutoIncrement(1)); |
| rsmd.isCaseSensitive(1); |
| rsmd.isCurrency(1); |
| rsmd.isDefinitelyWritable(1); |
| rsmd.isNullable(1); |
| rsmd.isReadOnly(1); |
| rsmd.isSearchable(1); |
| rsmd.isSigned(1); |
| rsmd.isWritable(1); |
| rs.close(); |
| |
| // test identity with PreparedStatement |
| pstmnt = connection.prepareStatement( |
| "INSERT INTO T VALUES (?,?,?)"); |
| |
| pstmnt.setString(1, null); |
| pstmnt.setString(2, "test"); |
| pstmnt.setString(3, "test2"); |
| pstmnt.executeUpdate(); |
| |
| pstmnt = connection.prepareStatement("call identity()"); |
| |
| ResultSet rsi = pstmnt.executeQuery(); |
| |
| rsi.next(); |
| |
| int identity = rsi.getInt(1); |
| |
| System.out.println("call identity(): " + identity); |
| rsi.close(); |
| } |
| } catch (SQLException e) { |
| fail(e.getMessage()); |
| } |
| |
| System.out.println("testMetaData complete"); |
| |
| // assert equality of exported and imported with xref |
| assertEquals(result1, result2); |
| assertEquals(result3, result4); |
| } |
| |
| /** |
| * Demonstration of a reported bug.<p> |
| * Because all values were turned into strings with toString before |
| * PreparedStatement.executeQuery() was called, special values such as |
| * NaN were not accepted. |
| * |
| * This test can be extended to cover various conversions through JDBC |
| * |
| */ |
| public void testDoubleNaN() { |
| |
| double value = 0; |
| boolean wasEqual = false; |
| String message = "DB operation completed"; |
| String ddl1 = |
| "DROP TABLE t1 IF EXISTS;" |
| + "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, " |
| + "dt DATE DEFAULT CURRENT_DATE, ti TIME DEFAULT CURRENT_TIME, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );"; |
| |
| try { |
| stmnt.execute(ddl1); |
| |
| PreparedStatement ps = connection.prepareStatement( |
| "INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)"); |
| |
| ps.setString(1, "0.2"); |
| ps.setDouble(2, 0.2); |
| ps.setLong(3, java.lang.Long.MAX_VALUE); |
| ps.setInt(4, Integer.MAX_VALUE); |
| ps.setInt(5, Short.MAX_VALUE); |
| ps.setInt(6, 0); |
| ps.setDate(7, new java.sql.Date(System.currentTimeMillis())); |
| ps.setTime(8, new java.sql.Time(System.currentTimeMillis())); |
| ps.setTimestamp( |
| 9, new java.sql.Timestamp(System.currentTimeMillis())); |
| ps.execute(); |
| ps.setInt(1, 0); |
| ps.setDouble(2, java.lang.Double.NaN); |
| ps.setLong(3, java.lang.Long.MIN_VALUE); |
| ps.setInt(4, Integer.MIN_VALUE); |
| ps.setInt(5, Short.MIN_VALUE); |
| ps.setInt(6, 0); |
| |
| // allowed conversions |
| ps.setTimestamp( |
| 7, new java.sql.Timestamp(System.currentTimeMillis() + 1)); |
| ps.setTime(8, new java.sql.Time(System.currentTimeMillis() + 1)); |
| ps.setDate(9, new java.sql.Date(System.currentTimeMillis() + 1)); |
| ps.execute(); |
| |
| // |
| ps.setInt(1, 0); |
| ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY); |
| ps.setInt(4, Integer.MIN_VALUE); |
| |
| // test conversion |
| // ps.setObject(5, Boolean.TRUE); // no longer converts boolean to int |
| // ps.setBoolean(5, true); |
| ps.setObject(5, new Short((short) 2), Types.SMALLINT); |
| ps.setObject(6, new Integer(2), Types.TINYINT); |
| |
| // allowed conversions |
| ps.setObject(7, new java.sql.Date(System.currentTimeMillis() + 2)); |
| ps.setObject(8, new java.sql.Time(System.currentTimeMillis() + 2)); |
| ps.setObject(9, new java.sql.Timestamp(System.currentTimeMillis() |
| + 2)); |
| ps.execute(); |
| ps.setObject(1, new Float(0), Types.INTEGER); |
| ps.setObject(4, new Float(1), Types.INTEGER); |
| ps.setDouble(2, java.lang.Double.NEGATIVE_INFINITY); |
| ps.execute(); |
| |
| ResultSet rs = |
| stmnt.executeQuery("SELECT d, f, l, i, s*2, t FROM t1"); |
| boolean result = rs.next(); |
| |
| value = rs.getDouble(2); |
| |
| // int smallintValue = rs.getShort(3); |
| int integerValue = rs.getInt(4); |
| |
| if (rs.next()) { |
| value = rs.getDouble(2); |
| wasEqual = Double.isNaN(value); |
| integerValue = rs.getInt(4); |
| |
| // tests for conversion |
| // getInt on DECIMAL |
| integerValue = rs.getInt(1); |
| } |
| |
| if (rs.next()) { |
| value = rs.getDouble(2); |
| wasEqual = wasEqual && value == Double.POSITIVE_INFINITY; |
| } |
| |
| if (rs.next()) { |
| value = rs.getDouble(2); |
| wasEqual = wasEqual && value == Double.NEGATIVE_INFINITY; |
| } |
| |
| rs = stmnt.executeQuery("SELECT MAX(i) FROM t1"); |
| |
| if (rs.next()) { |
| int max = rs.getInt(1); |
| |
| System.out.println("Max value for i: " + max); |
| } |
| |
| try { |
| |
| // cause errors |
| ps.setString(5, "three"); |
| assertTrue(false); |
| } catch (SQLException e) { |
| System.out.println("rubbish"); |
| } |
| |
| { |
| stmnt.execute("drop table CDTYPE if exists"); |
| |
| // test for the value MAX(column) in an empty table |
| stmnt.execute( |
| "CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))"); |
| |
| rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType"); |
| |
| if (rs.next()) { |
| int max = rs.getInt(1); |
| |
| System.out.println("Max value for ID: " + max); |
| } else { |
| System.out.println("Max value for ID not returned"); |
| } |
| |
| stmnt.executeUpdate( |
| "INSERT INTO cdType VALUES (10,'Test String');"); |
| stmnt.execute("CALL IDENTITY();"); |
| |
| try { |
| stmnt.executeUpdate( |
| "INSERT INTO cdType VALUES (10,'Test String');"); |
| } catch (SQLException e1) { |
| stmnt.execute("ROLLBACK"); |
| connection.rollback(); |
| } |
| } |
| } catch (SQLException e) { |
| fail(e.getMessage()); |
| } |
| |
| System.out.println("testDoubleNaN complete"); |
| |
| // assert new behaviour |
| assertEquals(true, wasEqual); |
| } |
| |
| public void testAny() { |
| |
| try { |
| String ddl = |
| "drop table PRICE_RELATE_USER_ORDER_V2 if exists;" |
| + "create table PRICE_RELATE_USER_ORDER_V2 " |
| + "(ID_ORDER_V2 BIGINT, ID_USER NUMERIC, DATE_CREATE TIMESTAMP)"; |
| String sql = "insert into PRICE_RELATE_USER_ORDER_V2 " |
| + "(ID_ORDER_V2, ID_USER, DATE_CREATE) " + "values " |
| + "(?, ?, ?)"; |
| Statement st = connection.createStatement(); |
| |
| st.execute(ddl); |
| |
| PreparedStatement ps = connection.prepareStatement(sql); |
| |
| ps.setLong(1, 1); |
| ps.setNull(2, Types.NUMERIC); |
| ps.setTimestamp( |
| 3, new java.sql.Timestamp(System.currentTimeMillis())); |
| ps.execute(); |
| } catch (SQLException e) { |
| e.printStackTrace(); |
| System.out.println("TestSql.testAny() error: " + e.getMessage()); |
| } |
| |
| System.out.println("testAny complete"); |
| } |
| |
| /** |
| * Fix for bug #1201135 |
| */ |
| public void testBinds() { |
| |
| try { |
| PreparedStatement pstmt = |
| connection.prepareStatement("drop table test if exists"); |
| |
| pstmt.execute(); |
| |
| pstmt = |
| connection.prepareStatement("create table test (id integer)"); |
| |
| pstmt.execute(); |
| |
| pstmt = |
| connection.prepareStatement("insert into test values (10)"); |
| |
| pstmt.execute(); |
| |
| pstmt = |
| connection.prepareStatement("insert into test values (20)"); |
| |
| pstmt.execute(); |
| |
| pstmt = connection.prepareStatement( |
| "select count(*) from test where id is null"); |
| |
| ResultSet rs = pstmt.executeQuery(); |
| |
| rs.next(); |
| |
| int count = rs.getInt(1); |
| |
| assertEquals(0, count); |
| |
| pstmt = |
| connection.prepareStatement("select limit ? 2 id from test"); |
| |
| pstmt.setInt(1, 0); |
| |
| rs = pstmt.executeQuery(); |
| |
| rs.next(); |
| |
| count = rs.getInt(1); |
| |
| assertEquals(10, count); |
| pstmt.setInt(1, 1); |
| |
| rs = pstmt.executeQuery(); |
| |
| rs.next(); |
| |
| count = rs.getInt(1); |
| |
| assertEquals(20, count); |
| } catch (SQLException e) { |
| e.printStackTrace(); |
| System.out.println("TestSql.testBinds() error: " + e.getMessage()); |
| } |
| } |
| |
| // miscellaneous tests |
| public void testX1() { |
| |
| String tableDDL = |
| "create table lo_attribute ( " |
| + "learningid varchar(15) not null, " |
| + "ordering integer not null," |
| + "attribute_value_data varchar(85)," |
| + "constraint PK_LO_ATTR primary key (learningid, ordering))"; |
| |
| try { |
| Statement stmt = connection.createStatement(); |
| |
| stmt.execute("drop table lo_attribute if exists"); |
| stmt.execute(tableDDL); |
| stmt.execute( |
| "insert into lo_attribute values('abcd', 10, 'cdef')"); |
| stmt.execute( |
| "insert into lo_attribute values('bcde', 20, 'cdef')"); |
| } catch (SQLException e) { |
| assertEquals(0, 1); |
| } |
| |
| try { |
| String prepared = |
| "update lo_attribute set " |
| + " ordering = (ordering - 1) where ordering > ?"; |
| PreparedStatement ps = connection.prepareStatement(prepared); |
| |
| ps.setInt(1, 10); |
| ps.execute(); |
| } catch (SQLException e) { |
| assertEquals(0, 1); |
| } |
| |
| try { |
| connection.setAutoCommit(false); |
| |
| java.sql.Savepoint savepoint = |
| connection.setSavepoint("savepoint"); |
| |
| connection.createStatement().executeQuery("CALL true;"); |
| connection.rollback(savepoint); |
| } catch (SQLException e) { |
| assertEquals(0, 1); |
| } |
| } |
| |
| /** |
| * In 1.8.0.2, this fails in client / server due to column type of the |
| * second select for b1 being boolean, while the first select is interpreted |
| * as varchar. The rowOutputBase class attempts to cast the Java Boolean |
| * into String. |
| */ |
| public void testUnionColumnTypes() { |
| |
| try { |
| Connection conn = newConnection(); |
| Statement stmt = conn.createStatement(); |
| |
| stmt.execute("DROP TABLE test1 IF EXISTS"); |
| stmt.execute("DROP TABLE test2 IF EXISTS"); |
| stmt.execute("CREATE TABLE test1 (id int, b1 boolean)"); |
| stmt.execute("CREATE TABLE test2 (id int)"); |
| stmt.execute("INSERT INTO test1 VALUES(1,true)"); |
| stmt.execute("INSERT INTO test2 VALUES(2)"); |
| |
| ResultSet rs = stmt.executeQuery( |
| "select id,null as b1 from test2 union select id, b1 from test1"); |
| Boolean[] array = new Boolean[2]; |
| |
| for (int i = 0; rs.next(); i++) { |
| boolean boole = rs.getBoolean(2); |
| |
| array[i] = Boolean.valueOf(boole); |
| |
| if (rs.wasNull()) { |
| array[i] = null; |
| } |
| } |
| |
| boolean result = (array[0] == null && array[1] == Boolean.TRUE) |
| || (array[0] == Boolean.TRUE && array[1] == null); |
| |
| assertTrue(result); |
| } catch (SQLException e) { |
| e.printStackTrace(); |
| System.out.println("TestSql.testUnionColumnType() error: " |
| + e.getMessage()); |
| } |
| } |
| |
| public void testUnionSubquery() throws Exception { |
| |
| Statement st = connection.createStatement(); |
| |
| st.execute("DROP TABLE t1 if exists;"); |
| st.execute("DROP TABLE t2 if exists;"); |
| st.execute( |
| "CREATE TABLE t1 (id int not null, v1 int, v2 int, primary key(id))"); |
| st.execute( |
| "CREATE TABLE t2 (id int not null, v1 int, v3 int, primary key(id))"); |
| st.execute("INSERT INTO t1 values(1,1,1)"); |
| st.execute("INSERT INTO t1 values(2,2,2)"); |
| st.execute("INSERT INTO t2 values(1,3,3)"); |
| |
| ResultSet rs = st.executeQuery( |
| "select t as atable, a as idvalue, b as value1, c as value2, d as value3 from(" |
| + "(select 't1' as t, t1.id as a, t1.v1 as b, t1.v2 as c, null as d from t1) union" |
| + "(select 't2' as t, t2.id as a, t2.v1 as b, null as c, t2.v3 as d from t2)) order by atable, idvalue"); |
| |
| assertTrue(rs.next()); |
| assertEquals("t1", rs.getObject("atable")); |
| assertEquals(1, rs.getInt("idvalue")); |
| assertEquals(1, rs.getInt("value1")); |
| assertEquals(1, rs.getInt("value2")); |
| assertEquals(null, rs.getObject("value3")); |
| assertTrue(rs.next()); |
| assertEquals("t1", rs.getObject("atable")); |
| assertEquals(2, rs.getInt("idvalue")); |
| assertEquals(2, rs.getInt("value1")); |
| assertEquals(2, rs.getInt("value2")); |
| assertEquals(null, rs.getObject("value3")); |
| assertTrue(rs.next()); |
| assertEquals("t2", rs.getObject("atable")); |
| assertEquals(1, rs.getInt("idvalue")); |
| assertEquals(3, rs.getInt("value1")); |
| assertEquals(null, rs.getObject("value2")); |
| assertEquals(3, rs.getInt("value3")); //this fails! |
| assertFalse(rs.next()); |
| } |
| |
| public void testPreparedWithManyParams() throws Exception { |
| |
| int count = 40; |
| String tabledef = "CREATE TABLE T1 ("; |
| |
| for (int i = 0; i < count; i++) { |
| if (i != 0) { |
| tabledef = tabledef + ','; |
| } |
| |
| tabledef = tabledef + "COL_" + i + " INT NOT NULL"; |
| } |
| |
| tabledef += ");"; |
| |
| String querydef = "INSERT INTO T1("; |
| |
| for (int i = 0; i < count; i++) { |
| if (i != 0) { |
| querydef = querydef + ','; |
| } |
| |
| querydef = querydef + "COL_" + i; |
| } |
| |
| querydef += ") VALUES ("; |
| |
| for (int i = 0; i < count; i++) { |
| if (i != 0) { |
| querydef = querydef + ','; |
| } |
| |
| querydef = querydef + "?"; |
| } |
| |
| querydef += ");"; |
| |
| Statement st = connection.createStatement(); |
| |
| st.execute("DROP TABLE T1 IF EXISTS;"); |
| st.execute(tabledef); |
| |
| PreparedStatement ps = connection.prepareStatement(querydef); |
| |
| for (int i = 0; i < count; i++) { |
| ps.setInt(i + 1, i + 311); |
| } |
| |
| ps.executeUpdate(); |
| } |
| |
| |
| static byte[] b1 = { |
| 0, 1, -128, 44, 12 |
| }; |
| static byte[] b2 = { |
| 10, 127 |
| }; |
| |
| public void testBinaryFunction() throws Exception { |
| |
| Statement sStatement = null; |
| ResultSet r; |
| boolean mismatch; |
| sStatement = connection.createStatement(); |
| |
| try { |
| |
| // prepared statements |
| String s = "create table bintest(id int primary key, bin varbinary(100))"; |
| |
| sStatement.execute(s); |
| |
| s = "insert into bintest values ( ?, ?)"; |
| |
| PreparedStatement p = connection.prepareStatement(s); |
| |
| p.clearParameters(); |
| p.setInt(1, 10); |
| |
| p.setBytes(2, b1); |
| p.executeUpdate(); |
| |
| p.clearParameters(); |
| p.setInt(1, 20); |
| |
| p.setBytes(2, b2); |
| p.executeUpdate(); |
| |
| byte[] b1n; |
| byte[] b2n; |
| |
| s = "select \"org.hsqldb.lib.ArrayUtil.countStartElementsAt\"(bin,0, ?) " |
| + "from bintest"; |
| p = connection.prepareStatement(s); |
| |
| p.setBytes(1, b2); |
| |
| r = p.executeQuery(); |
| |
| r.next(); |
| |
| int integer1 = r.getInt(1); |
| |
| r.next(); |
| |
| int integer2 = r.getInt(1); |
| |
| s = "select \"org.hsqldb.lib.StringConverter.hexStringToByteArray\"" |
| + "(\"org.hsqldb.lib.StringConverter.byteArrayToHexString\"(x'abcd')) " |
| + "from bintest"; |
| r = sStatement.executeQuery(s); |
| |
| r.next(); |
| |
| b1n = r.getBytes(1); |
| |
| r.next(); |
| |
| b1n = r.getBytes(1); |
| |
| //-- |
| s = "select \"org.hsqldb.lib.StringConverter.byteArrayToHexString\"(bin) " |
| + "from bintest"; |
| r = sStatement.executeQuery(s); |
| |
| r.next(); |
| |
| b1n = r.getBytes(1); |
| |
| r.next(); |
| |
| b1n = r.getBytes(1); |
| |
| s = "create table obj(id int,o object)"; |
| |
| sStatement.execute(s); |
| |
| s = "insert into obj values(?,?)"; |
| p = connection.prepareStatement(s); |
| |
| p.setInt(1, 1); |
| |
| int[] ia1 = { |
| 1, 2, 3 |
| }; |
| |
| p.setObject(2, ia1); |
| p.executeUpdate(); |
| p.clearParameters(); |
| p.setInt(1, 2); |
| |
| java.awt.Rectangle r1 = new java.awt.Rectangle(10, 11, 12, 13); |
| |
| p.setObject(2, r1); |
| p.executeUpdate(); |
| |
| r = sStatement.executeQuery("SELECT o FROM obj ORDER BY id DESC"); |
| |
| r.next(); |
| |
| java.awt.Rectangle r2 = (java.awt.Rectangle) r.getObject(1); |
| |
| if (r2.x != 10 || r2.y != 11 || r2.width != 12 |
| || r2.height != 13) { |
| throw new Exception("Object data error: Rectangle"); |
| } |
| |
| r.next(); |
| |
| int[] ia2 = (int[]) (r.getObject(1)); |
| |
| if (ia2[0] != 1 || ia2[1] != 2 || ia2[2] != 3 || ia2.length != 3) { |
| throw new Exception("Object data error: int[]"); |
| } |
| |
| sStatement.close(); |
| } catch (Exception e) { |
| assertEquals(0, 1); |
| } |
| } |
| |
| protected void tearDown() { |
| |
| try { |
| stmnt.execute("SHUTDOWN"); |
| connection.close(); |
| } catch (Exception e) { |
| e.printStackTrace(); |
| System.out.println("TestSql.tearDown() error: " + e.getMessage()); |
| } |
| |
| super.tearDown(); |
| } |
| |
| public static void main(String[] argv) { |
| |
| TestResult result = new TestResult(); |
| TestCase testA = new TestSql("testMetaData"); |
| TestCase testB = new TestSql("testDoubleNaN"); |
| TestCase testC = new TestSql("testAny"); |
| |
| testA.run(result); |
| testB.run(result); |
| testC.run(result); |
| System.out.println("TestSql error count: " + result.failureCount()); |
| } |
| } |
