Example #1
0
  @Test
  public void retainKeysInBatch() throws SQLException {
    stat.executeUpdate("create table test (c1, c2);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?, ?);");
    prep.setInt(1, 10);
    prep.setString(2, "ten");
    prep.addBatch();
    prep.setInt(1, 100);
    prep.setString(2, "hundred");
    prep.addBatch();
    prep.setString(2, "one hundred");
    prep.addBatch();
    prep.setInt(1, 1000);
    prep.setString(2, "thousand");
    prep.addBatch();
    prep.executeBatch();
    prep.close();

    ResultSet rs = stat.executeQuery("select * from test;");
    assertTrue(rs.next());
    assertEquals(10, rs.getInt(1));
    assertEquals("ten", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(100, rs.getInt(1));
    assertEquals("hundred", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(100, rs.getInt(1));
    assertEquals("one hundred", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(1000, rs.getInt(1));
    assertEquals("thousand", rs.getString(2));
    assertFalse(rs.next());
    rs.close();
  }
Example #2
0
  @Test
  public void set() throws SQLException {
    ResultSet rs;
    PreparedStatement prep = conn.prepareStatement("select ?, ?, ?;");

    // integers
    prep.setInt(1, Integer.MIN_VALUE);
    prep.setInt(2, Integer.MAX_VALUE);
    prep.setInt(3, 0);
    rs = prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(Integer.MIN_VALUE, rs.getInt(1));
    assertEquals(Integer.MAX_VALUE, rs.getInt(2));
    assertEquals(0, rs.getInt(3));

    // strings
    String name = "Winston Leonard Churchill";
    String fn = name.substring(0, 7), mn = name.substring(8, 15), sn = name.substring(16, 25);
    prep.clearParameters();
    prep.setString(1, fn);
    prep.setString(2, mn);
    prep.setString(3, sn);
    prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(fn, rs.getString(1));
    assertEquals(mn, rs.getString(2));
    assertEquals(sn, rs.getString(3));

    // mixed
    prep.setString(1, name);
    prep.setString(2, null);
    prep.setLong(3, Long.MAX_VALUE);
    prep.executeQuery();
    assertTrue(rs.next());
    assertEquals(name, rs.getString(1));
    assertNull(rs.getString(2));
    assertTrue(rs.wasNull());
    assertEquals(Long.MAX_VALUE, rs.getLong(3));

    // bytes
    prep.setBytes(1, b1);
    prep.setBytes(2, b2);
    prep.setBytes(3, b3);
    prep.executeQuery();
    assertTrue(rs.next());
    assertArrayEq(rs.getBytes(1), b1);
    assertArrayEq(rs.getBytes(2), b2);
    assertArrayEq(rs.getBytes(3), b3);
    assertFalse(rs.next());
    rs.close();
  }
Example #3
0
  @Test
  public void batch() throws SQLException {
    ResultSet rs;

    stat.executeUpdate("create table test (c1, c2, c3, c4);");
    PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);");
    for (int i = 0; i < 10; i++) {
      prep.setInt(1, Integer.MIN_VALUE + i);
      prep.setFloat(2, Float.MIN_VALUE + i);
      prep.setString(3, "Hello " + i);
      prep.setDouble(4, Double.MAX_VALUE + i);
      prep.addBatch();
    }
    assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
    assertEquals(0, prep.executeBatch().length);
    prep.close();

    rs = stat.executeQuery("select * from test;");
    for (int i = 0; i < 10; i++) {
      assertTrue(rs.next());
      assertEquals(Integer.MIN_VALUE + i, rs.getInt(1));
      assertEquals(Float.MIN_VALUE + i, rs.getFloat(2));
      assertEquals("Hello " + i, rs.getString(3));
      assertEquals(Double.MAX_VALUE + i, rs.getDouble(4));
    }
    rs.close();
    stat.executeUpdate("drop table test;");
  }
  /**
   * aggregation - group by
   *
   * @throws Exception
   */
  @Test
  public void testDateGroupBy() throws Exception {
    printTestDescription();

    Date date = new Date(1);
    initATableValues(date);

    try {
      ResultSet rs;
      String query = null;
      if (tgtPH())
        query =
            "SELECT a_date, count(1) FROM atable WHERE organization_id='"
                + tenantId
                + "' group by a_date";
      else if (tgtSQ() || tgtTR())
        query =
            "SELECT a_date, count(1) FROM atable WHERE organization_id='"
                + tenantId
                + "' group by a_date order by 1";
      rs = getResultSet(conn, query);

      /* 3 rows in expected result:
       * 1969-12-31   3
       * 1970-01-01   3
       * 1970-01-02   3
       * */

      assertTrue(rs.next());
      if (tgtPH()) assertEquals(date, rs.getDate(1));
      else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString());
      assertEquals(3, rs.getInt(2));

      // the following assertions fails
      assertTrue(rs.next());
      assertEquals(3, rs.getInt(2));
      assertTrue(rs.next());
      assertEquals(3, rs.getInt(2));
      assertFalse(rs.next());

    } finally {
    }
  }
Example #5
0
 @Test
 public void setmaxrows() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select 1 union select 2;");
   prep.setMaxRows(1);
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertEquals(1, rs.getInt(1));
   assertFalse(rs.next());
   prep.close();
 }
Example #6
0
 @Test
 public void singleRowRS() throws SQLException {
   PreparedStatement prep = conn.prepareStatement("select ?;");
   prep.setInt(1, Integer.MAX_VALUE);
   ResultSet rs = prep.executeQuery();
   assertTrue(rs.next());
   assertEquals(Integer.MAX_VALUE, rs.getInt(1));
   assertEquals(Integer.toString(Integer.MAX_VALUE), rs.getString(1));
   assertEquals(new Integer(Integer.MAX_VALUE).doubleValue(), rs.getDouble(1));
   assertFalse(rs.next());
   rs.close();
   prep.close();
 }
Example #7
0
  @Test
  public void reusingSetValues() throws SQLException {
    PreparedStatement prep = conn.prepareStatement("select ?,?;");
    prep.setInt(1, 9);

    for (int i = 0; i < 10; i++) {
      prep.setInt(2, i);
      ResultSet rs = prep.executeQuery();
      assertTrue(rs.next());
      assertEquals(9, rs.getInt(1));
      assertEquals(i, rs.getInt(2));
    }

    for (int i = 0; i < 10; i++) {
      prep.setInt(2, i);
      ResultSet rs = prep.executeQuery();
      assertTrue(rs.next());
      assertEquals(9, rs.getInt(1));
      assertEquals(i, rs.getInt(2));
      rs.close();
    }

    prep.close();
  }
Example #8
0
 @Test
 public void batchOneParam() throws SQLException {
   stat.executeUpdate("create table test (c1);");
   PreparedStatement prep = conn.prepareStatement("insert into test values (?);");
   for (int i = 0; i < 10; i++) {
     prep.setInt(1, Integer.MIN_VALUE + i);
     prep.addBatch();
   }
   assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
   prep.close();
   ResultSet rs = stat.executeQuery("select count(*) from test;");
   assertTrue(rs.next());
   assertEquals(10, rs.getInt(1));
   rs.close();
 }
Example #9
0
  @Test
  public void insert1000() throws SQLException {
    stat.executeUpdate("create table in1000 (a);");
    PreparedStatement prep = conn.prepareStatement("insert into in1000 values (?);");
    conn.setAutoCommit(false);
    for (int i = 0; i < 1000; i++) {
      prep.setInt(1, i);
      prep.executeUpdate();
    }
    conn.commit();

    ResultSet rs = stat.executeQuery("select count(a) from in1000;");
    assertTrue(rs.next());
    assertEquals(1000, rs.getInt(1));
    rs.close();
  }
Example #10
0
  @Test
  public void update() throws SQLException {
    assertEquals(conn.prepareStatement("create table s1 (c1);").executeUpdate(), 0);
    PreparedStatement prep = conn.prepareStatement("insert into s1 values (?);");
    prep.setInt(1, 3);
    assertEquals(1, prep.executeUpdate());
    prep.setInt(1, 5);
    assertEquals(1, prep.executeUpdate());
    prep.setInt(1, 7);
    assertEquals(1, prep.executeUpdate());
    prep.close();

    // check results with normal statement
    ResultSet rs = stat.executeQuery("select sum(c1) from s1;");
    assertTrue(rs.next());
    assertEquals(15, rs.getInt(1));
    rs.close();
  }
Example #11
0
  @Ignore
  @Test
  public void getObject() throws SQLException {
    stat.executeUpdate(
        "create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);");
    PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);");

    prep.setInt(1, Integer.MAX_VALUE);
    prep.setFloat(2, Float.MAX_VALUE);
    prep.setDouble(3, Double.MAX_VALUE);
    prep.setLong(4, Long.MAX_VALUE);
    prep.setBoolean(5, false);
    prep.setByte(6, (byte) 7);
    prep.setBytes(7, b1);
    prep.executeUpdate();

    ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;");
    assertTrue(rs.next());

    assertEquals(Integer.MAX_VALUE, rs.getInt(1));
    assertEquals(Integer.MAX_VALUE, (int) rs.getLong(1));
    assertEquals(Float.MAX_VALUE, rs.getFloat(2));
    assertEquals(Double.MAX_VALUE, rs.getDouble(3));
    assertEquals(Long.MAX_VALUE, rs.getLong(4));
    assertFalse(rs.getBoolean(5));
    assertEquals((byte) 7, rs.getByte(6));
    assertArrayEq(rs.getBytes(7), b1);

    assertNotNull(rs.getObject(1));
    assertNotNull(rs.getObject(2));
    assertNotNull(rs.getObject(3));
    assertNotNull(rs.getObject(4));
    assertNotNull(rs.getObject(5));
    assertNotNull(rs.getObject(6));
    assertNotNull(rs.getObject(7));
    assertTrue(rs.getObject(1) instanceof Integer);
    assertTrue(rs.getObject(2) instanceof Double);
    assertTrue(rs.getObject(3) instanceof Double);
    assertTrue(rs.getObject(4) instanceof String);
    assertTrue(rs.getObject(5) instanceof Integer);
    assertTrue(rs.getObject(6) instanceof Integer);
    assertTrue(rs.getObject(7) instanceof byte[]);
    rs.close();
  }
Example #12
0
 @Test(expected = SQLException.class)
 public void noSuchColName() throws SQLException {
   ResultSet rs = conn.prepareStatement("select 1;").executeQuery();
   assertTrue(rs.next());
   rs.getInt("noSuchColName");
 }