Exemplo n.º 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();
  }
Exemplo n.º 2
1
  /**
   * Obtains streams from the Clob and makes sure we can always read the last char in the Clob.
   *
   * <p>See DERBY-4060.
   *
   * @param id id of the Clob to use
   * @param length the length of the Clob
   * @param alphabet the alphabet used to create the content
   * @throws IOException if reading from a stream fails
   * @throws SQLException if something goes wrong
   */
  private void getCharacterStreamLongLastChar(int id, int length, CharAlphabet alphabet)
      throws IOException, SQLException {
    // Get last char from the source stream.
    Reader cmpReader = new LoopingAlphabetReader(length, alphabet);
    cmpReader.skip(length - 1);
    char srcLastChar = (char) cmpReader.read();
    assertTrue(cmpReader.read() == -1);

    PreparedStatement ps = prepareStatement("select CLOBDATA from BLOBCLOB where ID=?");
    ps.setInt(1, id);
    // Read everything first.
    int charsToRead = length;
    ResultSet rs = ps.executeQuery();
    rs.next();
    Reader reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    // Drain the stream, and make sure we are able to read the last char.
    char lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();

    // Read a portion of the stream.
    charsToRead = length / 4;
    rs = ps.executeQuery();
    rs.next();
    reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();

    // Read a very small portion of the stream.
    charsToRead = 1;
    rs = ps.executeQuery();
    rs.next();
    reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();
  }
Exemplo n.º 3
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();
  }
Exemplo n.º 5
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;
  }
  //
  // Find all the methods for java.sql objects in the Connection which raise
  // SQLFeatureNotSupportedException.
  //
  private void connectionWorkhorse(
      Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
      throws Exception {
    vetSavepoint(conn, unsupportedList, notUnderstoodList);
    vetLargeObjects(conn, unsupportedList, notUnderstoodList);

    DatabaseMetaData dbmd = conn.getMetaData();
    PreparedStatement ps = conn.prepareStatement("select * from sys.systables where tablename = ?");

    ps.setString(1, "foo");

    ParameterMetaData parameterMetaData = ps.getParameterMetaData();
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();
    Statement stmt = conn.createStatement();

    CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SET_RUNTIMESTATISTICS(0)");
    ParameterMetaData csmd = cs.getParameterMetaData();

    //
    // The vetObject() method calls all of the methods in these objects
    // in a deterministic order, calling the close() method last.
    // Inspect these objects in an order which respects the fact that
    // the objects are closed as a result of calling vetObject().
    //
    vetObject(dbmd, unsupportedList, notUnderstoodList);
    vetObject(stmt, unsupportedList, notUnderstoodList);
    vetObject(csmd, unsupportedList, notUnderstoodList);
    vetObject(cs, unsupportedList, notUnderstoodList);
    vetObject(rsmd, unsupportedList, notUnderstoodList);
    vetObject(rs, unsupportedList, notUnderstoodList);
    vetObject(parameterMetaData, unsupportedList, notUnderstoodList);
    vetObject(ps, unsupportedList, notUnderstoodList);
    vetObject(conn, unsupportedList, notUnderstoodList);

    // No need to close the objects. They were closed by vetObject().
  }
Exemplo n.º 7
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();
  }
Exemplo n.º 8
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);
    }
  }
Exemplo n.º 9
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();
  }
Exemplo n.º 10
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);
  }
Exemplo n.º 11
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);
  }