@Test public void doubleclose() throws SQLException { PreparedStatement prep = conn.prepareStatement("select null;"); ResultSet rs = prep.executeQuery(); rs.close(); prep.close(); prep.close(); }
@Test public void emptyRS() throws SQLException { PreparedStatement prep = conn.prepareStatement("select null limit 0;"); ResultSet rs = prep.executeQuery(); assertFalse(rs.next()); rs.close(); prep.close(); }
private void verifyDateResultSet(ResultSet rs, Date date, int rowCount) throws SQLException { for (int i = 0; i < rowCount; i++) { assertTrue(rs.next()); if (tgtPH()) assertEquals(date, rs.getDate(1)); else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString()); } assertFalse(rs.next()); }
@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(); }
@Test public void stringRS() throws SQLException { String name = "Gandhi"; PreparedStatement prep = conn.prepareStatement("select ?;"); prep.setString(1, name); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(name, rs.getString(1)); assertFalse(rs.next()); rs.close(); }
@Test public void date2() throws SQLException { Date d1 = new Date(1092941466000L); stat.execute("create table t (c1);"); PreparedStatement prep = conn.prepareStatement("insert into t values (datetime(?/1000, 'unixepoch'));"); prep.setDate(1, d1); prep.executeUpdate(); ResultSet rs = stat.executeQuery("select strftime('%s', c1) * 1000 from t;"); assertTrue(rs.next()); assertEquals(d1.getTime(), rs.getLong(1)); assertTrue(rs.getDate(1).equals(d1)); }
@Test public void timestamp() throws SQLException { connectWithJulianDayModeActivated(); long now = System.currentTimeMillis(); Timestamp d1 = new Timestamp(now); Date d2 = new Date(now); Time d3 = new Time(now); stat.execute("create table t (c1);"); PreparedStatement prep = conn.prepareStatement("insert into t values (?);"); prep.setTimestamp(1, d1); prep.executeUpdate(); ResultSet rs = stat.executeQuery("select c1 from t;"); assertTrue(rs.next()); assertEquals(d1, rs.getTimestamp(1)); rs = stat.executeQuery("select date(c1, 'localtime') from t;"); assertTrue(rs.next()); assertEquals(d2.toString(), rs.getString(1)); rs = stat.executeQuery("select time(c1, 'localtime') from t;"); assertTrue(rs.next()); assertEquals(d3.toString(), rs.getString(1)); rs = stat.executeQuery("select strftime('%Y-%m-%d %H:%M:%f', c1, 'localtime') from t;"); assertTrue(rs.next()); // assertEquals(d1.toString(), rs.getString(1)); // ms are not occurate... }
@Test public void tokens() throws SQLException { /* checks for a bug where a substring is read by the driver as the * full original string, caused by my idiocyin assuming the * pascal-style string was null terminated. Thanks Oliver Randschau. */ StringTokenizer st = new StringTokenizer("one two three"); st.nextToken(); String substr = st.nextToken(); PreparedStatement prep = conn.prepareStatement("select ?;"); prep.setString(1, substr); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(substr, rs.getString(1)); }
@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(); }
@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(); }
@SuppressWarnings("deprecation") @Test public void time() throws SQLException { connectWithJulianDayModeActivated(); Time d1 = new Time(System.currentTimeMillis()); stat.execute("create table t (c1);"); PreparedStatement prep = conn.prepareStatement("insert into t values (?);"); prep.setTime(1, d1); prep.executeUpdate(); ResultSet rs = stat.executeQuery("select c1 from t;"); assertTrue(rs.next()); assertEquals(d1.getHours(), rs.getTime(1).getHours()); assertEquals(d1.getMinutes(), rs.getTime(1).getMinutes()); assertEquals(d1.getSeconds(), rs.getTime(1).getSeconds()); }
@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;"); }
@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(); }
/** * 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 { } }
@Test public void twoRowRS() throws SQLException { PreparedStatement prep = conn.prepareStatement("select ? union all select ?;"); prep.setDouble(1, Double.MAX_VALUE); prep.setDouble(2, Double.MIN_VALUE); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(Double.MAX_VALUE, rs.getDouble(1)); assertTrue(rs.next()); assertEquals(Double.MIN_VALUE, rs.getDouble(1)); assertFalse(rs.next()); rs.close(); }
@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(); }
@Test public void colNameAccess() throws SQLException { PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as bingo;"); prep.setNull(1, 0); prep.setFloat(2, Float.MIN_VALUE); prep.setShort(3, Short.MIN_VALUE); prep.executeQuery(); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertNull(rs.getString("col1")); assertTrue(rs.wasNull()); assertEquals(Float.MIN_VALUE, rs.getFloat("col2")); assertEquals(Short.MIN_VALUE, rs.getShort("bingo")); rs.close(); prep.close(); }
@Test public void date1() throws SQLException { Date d1 = new Date(987654321); stat.execute("create table t (c1);"); PreparedStatement prep = conn.prepareStatement("insert into t values(?);"); prep.setDate(1, d1); prep.executeUpdate(); prep.setDate(1, null); prep.executeUpdate(); ResultSet rs = stat.executeQuery("select c1 from t;"); assertTrue(rs.next()); assertEquals(d1.getTime(), rs.getLong(1)); assertTrue(rs.getDate(1).equals(d1)); assertTrue(rs.next()); assertEquals(null, rs.getDate(1)); rs.close(); }
@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(); }
@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(); }
@Test(expected = SQLException.class) public void noSuchColName() throws SQLException { ResultSet rs = conn.prepareStatement("select 1;").executeQuery(); assertTrue(rs.next()); rs.getInt("noSuchColName"); }
@Test public void utf() throws SQLException { ResultSet rs = stat.executeQuery( "select '" + utf01 + "','" + utf02 + "','" + utf03 + "','" + utf04 + "','" + utf05 + "','" + utf06 + "','" + utf07 + "','" + utf08 + "';"); assertEquals(utf01, rs.getString(1)); assertEquals(utf02, rs.getString(2)); assertEquals(utf03, rs.getString(3)); assertEquals(utf04, rs.getString(4)); assertEquals(utf05, rs.getString(5)); assertEquals(utf06, rs.getString(6)); assertEquals(utf07, rs.getString(7)); assertEquals(utf08, rs.getString(8)); rs.close(); PreparedStatement prep = conn.prepareStatement("select ?,?,?,?,?,?,?,?;"); prep.setString(1, utf01); prep.setString(2, utf02); prep.setString(3, utf03); prep.setString(4, utf04); prep.setString(5, utf05); prep.setString(6, utf06); prep.setString(7, utf07); prep.setString(8, utf08); rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(utf01, rs.getString(1)); assertEquals(utf02, rs.getString(2)); assertEquals(utf03, rs.getString(3)); assertEquals(utf04, rs.getString(4)); assertEquals(utf05, rs.getString(5)); assertEquals(utf06, rs.getString(6)); assertEquals(utf07, rs.getString(7)); assertEquals(utf08, rs.getString(8)); rs.close(); }
@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(); }
@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(); }