예제 #1
1
  /**
   * Tests the implementation of getCharacterStream(long pos, long length).
   *
   * @throws Exception
   */
  public void testGetCharacterStreamLong() throws Exception {
    String str1 = "This is a test String. This is a test String";

    Reader r1 = new java.io.StringReader(str1);

    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, r1);
    ps.execute();
    ps.close();

    Statement st = createStatement();

    ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
    rs.next();
    Clob clob = rs.getClob(1);

    Reader r_1 = clob.getCharacterStream(2L, 5L);
    String str2 = str1.substring(1, 6);
    Reader r_2 = new java.io.StringReader(str2);

    assertEquals(r_2, r_1);

    rs.close();
    st.close();
  }
예제 #2
0
  /**
   * Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from
   * store. (DERBY-2891)
   */
  public void testGetCharacterStreamLongOnLargeClob() throws Exception {
    getConnection().setAutoCommit(false);

    // create large (>32k) clob that can be read from store
    final int size = 33000;
    StringBuilder sb = new StringBuilder(size);
    for (int i = 0; i < size; i += 10) {
      sb.append("1234567890");
    }

    final int id = BlobClobTestSetup.getID();
    PreparedStatement ps =
        prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))");
    ps.setInt(1, id);
    ps.setString(2, sb.toString());
    ps.executeUpdate();
    ps.close();

    Statement s = createStatement();
    ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id);
    assertTrue(rs.next());
    Clob c = rs.getClob(1);

    // request a small region of the clob
    BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L));
    assertEquals("456", r.readLine());

    r.close();
    c.free();
    rs.close();
    s.close();
    rollback();
  }
  //
  // Examine BLOBs and CLOBs.
  //
  private void vetLargeObjects(
      Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
      throws Exception {
    Statement stmt = conn.createStatement();

    stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
    stmt.execute(
        "INSERT INTO t (id, b, c) VALUES (1, "
            + "CAST ("
            + TestUtil.stringToHexLiteral("101010001101")
            + "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");

    ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");

    rs.next();

    Blob blob = rs.getBlob(2);
    Clob clob = rs.getClob(3);

    vetObject(blob, unsupportedList, notUnderstoodList);
    vetObject(clob, unsupportedList, notUnderstoodList);

    stmt.close();
    conn.rollback();
  }
예제 #4
0
  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");
  }
예제 #5
0
 public void tearDown() {
   try {
     Connection con = AllTests.getConnection();
     Statement st = con.createStatement();
     st.execute("drop table " + table);
     st.close();
   } catch (Throwable e) {
     // e.printStackTrace();
   }
 }
예제 #6
0
  /**
   * Inserts a Clob with the specified length, using a stream source, then fetches it from the
   * database and checks the length.
   *
   * @param length number of characters in the Clob
   * @throws IOException if reading from the source fails
   * @throws SQLException if something goes wrong
   */
  private void insertAndFetchTest(long length) throws IOException, SQLException {
    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
    long tsStart = System.currentTimeMillis();
    ps.execute();
    println(
        "Inserted "
            + length
            + " chars (length specified) in "
            + (System.currentTimeMillis() - tsStart)
            + " ms");
    Statement stmt = createStatement();
    tsStart = System.currentTimeMillis();
    ResultSet rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
    assertTrue("Clob not inserted", rs.next());
    Clob aClob = rs.getClob(1);
    assertEquals("Invalid length", length, aClob.length());
    println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
    rs.close();

    // Insert same Clob again, using the lengthless override.
    id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length));
    tsStart = System.currentTimeMillis();
    ps.executeUpdate();
    println(
        "Inserted "
            + length
            + " chars (length unspecified) in "
            + (System.currentTimeMillis() - tsStart)
            + " ms");
    rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
    assertTrue("Clob not inserted", rs.next());
    aClob = rs.getClob(1);
    assertEquals("Invalid length", length, aClob.length());
    println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
    rs.close();

    rollback();
  }
예제 #7
0
  public void setUp() {
    tearDown();
    try {
      Connection con = AllTests.getConnection();
      Statement st = con.createStatement();
      st.execute(
          "create table " + table + "(a " + testValue.dataType + ", b " + testValue.dataType + ")");
      st.close();
      PreparedStatement pr = con.prepareStatement("INSERT into " + table + "(a,b) Values(?,?)");

      pr.setObject(1, testValue.small);
      pr.setObject(2, testValue.large);
      pr.execute();

      pr.setObject(1, testValue.small);
      pr.setObject(2, testValue.small);
      pr.execute();

      pr.setObject(1, testValue.large);
      pr.setObject(2, testValue.large);
      pr.execute();

      pr.setObject(1, testValue.large);
      pr.setObject(2, testValue.small);
      pr.execute();

      pr.setObject(1, null);
      pr.setObject(2, testValue.small);
      pr.execute();

      pr.setObject(1, testValue.small);
      pr.setObject(2, null);
      pr.execute();

      pr.setObject(1, null);
      pr.setObject(2, null);
      pr.execute();
      pr.close();
    } catch (Throwable e) {
      e.printStackTrace();
    }
  }
예제 #8
0
  /**
   * Insert a row with a large clob into the test table. Read the row from the database and assign
   * the clob value to <code>clob</code>.
   *
   * @return The id of the row that was inserted
   * @throws java.sql.SQLException
   */
  private int initializeLongClob() throws SQLException {
    // Clob needs to be larger than one page for locking to occur
    final int lobLength = 40000;

    // Insert a long Clob
    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(lobLength), lobLength);
    ps.execute();
    ps.close();
    commit();

    // Fetch the Clob object from the database
    Statement st = createStatement();
    ResultSet rs = st.executeQuery("select CLOBDATA from BLOBCLOB where ID=" + id);
    rs.next();
    clob = rs.getClob(1);
    rs.close();
    st.close();

    return id;
  }
예제 #9
0
  /**
   * Try to update the row with the given error. Flag a failure if a timeout occurs when not
   * expected, and vice versa.
   *
   * @param id The id of the row to be updated
   * @param timeoutExpected true if it is expected that the update times out
   * @throws java.sql.SQLException
   */
  private void executeParallelUpdate(int id, boolean timeoutExpected) throws SQLException {
    Connection conn2 = openDefaultConnection();
    Statement stmt2 = conn2.createStatement();

    try {
      stmt2.executeUpdate(
          "update BLOBCLOB set BLOBDATA = " + "cast(X'FFFFFF' as blob) where ID=" + id);
      stmt2.close();
      conn2.commit();
      conn2.close();
      if (timeoutExpected) {
        fail("FAIL - should have gotten lock timeout");
      }
    } catch (SQLException se) {
      stmt2.close();
      conn2.rollback();
      conn2.close();
      if (timeoutExpected) {
        assertSQLState(LOCK_TIMEOUT, se);
      } else {
        throw se;
      }
    }
  }
예제 #10
0
  /**
   * Tests the exceptions thrown by the getCharacterStream (long pos, long length) for the following
   * conditions a) pos <= 0 b) pos > (length of LOB) c) length < 0 d) pos + length > (length of
   * LOB).
   *
   * @throws SQLException
   */
  public void testGetCharacterStreamLongExceptionConditions() throws SQLException {
    String str1 = "This is a test String. This is a test String";

    Reader r1 = new java.io.StringReader(str1);

    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, r1);
    ps.execute();
    ps.close();

    Statement st = createStatement();

    ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
    rs.next();
    Clob clob = rs.getClob(1);
    // check the case where pos <= 0
    try {
      // set pos as negative
      clob.getCharacterStream(-2L, 5L);
      // Should not come here. The exception has to be thrown.
      fail("FAIL: Expected SQLException for pos being negative " + "not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when pos <= 0 is XJ070
      assertSQLState("XJ070", sqle);
    }

    // check for the case pos > length of clob
    try {
      // set the pos to any value greater than the Clob length
      clob.getCharacterStream(clob.length() + 1, 5L);
      // Should not come here. The exception has to be thrown.
      fail(
          "FAIL: Expected SQLException for position being greater than "
              + "length of LOB not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when pos > length of Clob
      // is XJ076
      assertSQLState("XJ087", sqle);
    }

    // check for the case when length < 0
    try {
      // set length as negative
      clob.getCharacterStream(2L, -5L);
      // Should not come here. The exception has to be thrown.
      fail("Fail: expected exception for the length being negative " + "not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when length < 0 of Clob
      // is XJ071
      assertSQLState("XJ071", sqle);
    }

    // check for the case when pos + length > length of Clob
    try {
      // set pos + length > length of Clob
      clob.getCharacterStream((clob.length() - 4), 10L);
      // Should not come here. The exception has to be thrown.
      fail(
          "Fail: expected exception for the sum of position and length"
              + " being greater than the LOB size not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when length < 0 of Clob
      // is XJ087
      assertSQLState("XJ087", sqle);
    }
  }
예제 #11
0
  public void runTest() throws Exception {
    Connection con = AllTests.getConnection();
    Statement st = con.createStatement();
    ResultSet rs;

    rs = st.executeQuery("Select * from " + table + " where 1 = 0");
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where a = b");
    assertTrue("To few rows", rs.next());
    assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
    assertTrue("To few rows", rs.next());
    assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where a <= b and b <= a");
    assertTrue("To few rows", rs.next());
    assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
    assertTrue("To few rows", rs.next());
    assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where (a > (b))");
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where a >= b");
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where not (a >= b)");
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where a < b");
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where a < b or a>b");
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where a <= b");
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());

    rs = st.executeQuery("Select * from " + table + " where a <> b");
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());

    PreparedStatement pr =
        con.prepareStatement("Select * from " + table + " where a between ? and ?");
    pr.setObject(1, testValue.small);
    pr.setObject(2, testValue.large);
    rs = pr.executeQuery();
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());
    pr.close();

    pr = con.prepareStatement("Select * from " + table + " where a not between ? and ?");
    pr.setObject(1, testValue.small);
    pr.setObject(2, testValue.large);
    rs = pr.executeQuery();
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());
    pr.close();

    pr = con.prepareStatement("Select * from " + table + " where a in(?,?)");
    pr.setObject(1, testValue.small);
    pr.setObject(2, testValue.large);
    rs = pr.executeQuery();
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());
    pr.close();

    pr = con.prepareStatement("Select * from " + table + " where a not in(?,?)");
    pr.setObject(1, testValue.small);
    pr.setObject(2, testValue.large);
    rs = pr.executeQuery();
    assertTrue("To few rows", rs.next());
    assertTrue("To few rows", rs.next());
    assertFalse("To many rows", rs.next());
    pr.close();

    st.close();
  }
예제 #12
0
  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(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(md.getSQLKeywords());

        //                System.out.println(md.getSQLStateType());
        System.out.println(md.getStringFunctions());
        System.out.println(md.getSystemFunctions());
        System.out.println(md.getTimeDateFunctions());
        System.out.println(md.getURL());
        System.out.println(md.getUserName());
        System.out.println(md.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.executeQuery("CREATE TABLE T (I IDENTITY, A CHAR, B CHAR);");
        stmnt.executeQuery(
            "INSERT INTO T VALUES (NULL, 'get_column_name', '" + getColumnName + "');");

        ResultSet rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" 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\" 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);
  }
예제 #13
0
  /**
   * 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. In
   * 1.7.0 these values are inserted as nulls (fredt)<b>
   *
   * <p>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);
      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();

      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);
      }

      rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");

      if (rs.next()) {
        int max = rs.getInt(1);

        System.out.println("Max value for i: " + max);
      }

      {
        stmnt.execute("drop table CDTYPE if exists");

        // test for the value MAX(column) in an empty table
        rs =
            stmnt.executeQuery(
                "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.executeQuery("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);
  }