Examples with AssertThrows org.h2.test.utils.AssertThrows used on opensource projects

Example 16 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestTriggersConstraints method testTriggers.

private void testTriggers() throws SQLException {
    mustNotCallTrigger = false;
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
    // CREATE TRIGGER trigger {BEFORE|AFTER}
    // {INSERT|UPDATE|DELETE|ROLLBACK} ON table
    // [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL triggeredClass
    stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE " + "BEFORE INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE " + "BEFORE INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER INS_AFTER " + "" + "AFTER INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER UPD_BEFORE " + "BEFORE UPDATE ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER INS_AFTER_ROLLBACK " + "AFTER INSERT, ROLLBACK ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
    ResultSet rs;
    rs = stat.executeQuery("SCRIPT");
    checkRows(rs, new String[] { "CREATE FORCE TRIGGER PUBLIC.INS_BEFORE " + "BEFORE INSERT ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.INS_AFTER " + "AFTER INSERT ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.UPD_BEFORE " + "BEFORE UPDATE ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.INS_AFTER_ROLLBACK " + "AFTER INSERT, ROLLBACK ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";" });
    while (rs.next()) {
        String sql = rs.getString(1);
        if (sql.startsWith("CREATE TRIGGER")) {
            System.out.println(sql);
        }
    }
    rs = stat.executeQuery("SELECT * FROM TEST");
    rs.next();
    assertEquals("Hello-updated", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("UPDATE TEST SET NAME=NAME||'-upd'");
    rs = stat.executeQuery("SELECT * FROM TEST");
    rs.next();
    assertEquals("Hello-updated-upd-updated2", rs.getString(2));
    assertFalse(rs.next());
    mustNotCallTrigger = true;
    stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
    stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
    stat.execute("DROP TRIGGER IF EXISTS INS_AFTER_ROLLBACK");
    assertThrows(ErrorCode.TRIGGER_NOT_FOUND_1, stat).execute("DROP TRIGGER INS_BEFORE");
    stat.execute("DROP TRIGGER  INS_AFTER");
    stat.execute("DROP TRIGGER  UPD_BEFORE");
    stat.execute("UPDATE TEST SET NAME=NAME||'-upd-no_trigger'");
    stat.execute("INSERT INTO TEST VALUES(100, 'Insert-no_trigger')");
    conn.close();
    conn = getConnection("trigger");
    mustNotCallTrigger = false;
    conn.close();
}

Example 17 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestTriggersConstraints method testDeleteInTrigger.

private void testDeleteInTrigger() throws SQLException {
    if (config.mvcc || config.mvStore) {
        return;
    }
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("create table test(id int) as select 1");
    stat.execute("create trigger test_u before update on test " + "for each row call \"" + DeleteTrigger.class.getName() + "\"");
    // this threw a NullPointerException
    assertThrows(ErrorCode.ROW_NOT_FOUND_WHEN_DELETING_1, stat).execute("update test set id = 2");
    stat.execute("drop table test");
    conn.close();
}

Example 18 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestTriggersConstraints method testTriggerSelectEachRow.

private void testTriggerSelectEachRow() throws SQLException {
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("drop table if exists test");
    stat.execute("create table test(id int)");
    assertThrows(ErrorCode.TRIGGER_SELECT_AND_ROW_BASED_NOT_SUPPORTED, stat).execute("create trigger test_insert before select on test " + "for each row call \"" + TestTriggerAdapter.class.getName() + "\"");
    conn.close();
}

Example 19 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestFunctions method testFunctions.

private void testFunctions() throws SQLException {
    deleteDb("functions");
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    assertCallResult(null, stat, "abs(null)");
    assertCallResult("1", stat, "abs(1)");
    assertCallResult("1", stat, "abs(1)");
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
    stat.execute("CREATE ALIAS ADD_ROW FOR \"" + getClass().getName() + ".addRow\"");
    ResultSet rs;
    rs = stat.executeQuery("CALL ADD_ROW(1, 'Hello')");
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs = stat.executeQuery("SELECT * FROM TEST");
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    DatabaseMetaData meta = conn.getMetaData();
    rs = meta.getProcedureColumns(null, null, "ADD_ROW", null);
    assertTrue(rs.next());
    assertEquals("P0", rs.getString("COLUMN_NAME"));
    assertTrue(rs.next());
    assertEquals("FUNCTIONS", rs.getString("PROCEDURE_CAT"));
    assertEquals("PUBLIC", rs.getString("PROCEDURE_SCHEM"));
    assertEquals("ADD_ROW", rs.getString("PROCEDURE_NAME"));
    assertEquals("P2", rs.getString("COLUMN_NAME"));
    assertEquals(DatabaseMetaData.procedureColumnIn, rs.getInt("COLUMN_TYPE"));
    assertEquals("INTEGER", rs.getString("TYPE_NAME"));
    assertEquals(10, rs.getInt("PRECISION"));
    assertEquals(10, rs.getInt("LENGTH"));
    assertEquals(0, rs.getInt("SCALE"));
    assertEquals(DatabaseMetaData.columnNoNulls, rs.getInt("NULLABLE"));
    assertEquals("", rs.getString("REMARKS"));
    assertEquals(null, rs.getString("COLUMN_DEF"));
    assertEquals(0, rs.getInt("SQL_DATA_TYPE"));
    assertEquals(0, rs.getInt("SQL_DATETIME_SUB"));
    assertEquals(0, rs.getInt("CHAR_OCTET_LENGTH"));
    assertEquals(1, rs.getInt("ORDINAL_POSITION"));
    assertEquals("YES", rs.getString("IS_NULLABLE"));
    assertEquals("ADD_ROW", rs.getString("SPECIFIC_NAME"));
    assertTrue(rs.next());
    assertEquals("P3", rs.getString("COLUMN_NAME"));
    assertEquals("VARCHAR", rs.getString("TYPE_NAME"));
    assertFalse(rs.next());
    stat.executeQuery("CALL ADD_ROW(2, 'World')");
    stat.execute("CREATE ALIAS SELECT_F FOR \"" + getClass().getName() + ".select\"");
    rs = stat.executeQuery("CALL SELECT_F('SELECT * " + "FROM TEST ORDER BY ID')");
    assertEquals(2, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    rs.next();
    assertEquals(2, rs.getInt(1));
    assertEquals("World", rs.getString(2));
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT NAME FROM SELECT_F('SELECT * " + "FROM TEST ORDER BY NAME') ORDER BY NAME DESC");
    assertEquals(1, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals("World", rs.getString(1));
    rs.next();
    assertEquals("Hello", rs.getString(1));
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT SELECT_F('SELECT * " + "FROM TEST WHERE ID=' || ID) FROM TEST ORDER BY ID");
    assertEquals(1, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals("((1, Hello))", rs.getString(1));
    rs.next();
    assertEquals("((2, World))", rs.getString(1));
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT SELECT_F('SELECT * " + "FROM TEST ORDER BY ID') FROM DUAL");
    assertEquals(1, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals("((1, Hello), (2, World))", rs.getString(1));
    assertFalse(rs.next());
    assertThrows(ErrorCode.SYNTAX_ERROR_2, stat).executeQuery("CALL SELECT_F('ERROR')");
    stat.execute("CREATE ALIAS SIMPLE FOR \"" + getClass().getName() + ".simpleResultSet\"");
    rs = stat.executeQuery("CALL SIMPLE(2, 1, 1, 1, 1, 1, 1, 1)");
    assertEquals(2, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals(0, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("World", rs.getString(2));
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT * FROM SIMPLE(1, 1, 1, 1, 1, 1, 1, 1)");
    assertEquals(2, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals(0, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("CREATE ALIAS ARRAY FOR \"" + getClass().getName() + ".getArray\"");
    rs = stat.executeQuery("CALL ARRAY()");
    assertEquals(1, rs.getMetaData().getColumnCount());
    rs.next();
    Array a = rs.getArray(1);
    Object[] array = (Object[]) a.getArray();
    assertEquals(2, array.length);
    assertEquals(0, ((Integer) array[0]).intValue());
    assertEquals("Hello", (String) array[1]);
    assertThrows(ErrorCode.INVALID_VALUE_2, a).getArray(1, -1);
    assertThrows(ErrorCode.INVALID_VALUE_2, a).getArray(1, 3);
    assertEquals(0, ((Object[]) a.getArray(1, 0)).length);
    assertEquals(0, ((Object[]) a.getArray(2, 0)).length);
    assertThrows(ErrorCode.INVALID_VALUE_2, a).getArray(0, 0);
    assertThrows(ErrorCode.INVALID_VALUE_2, a).getArray(3, 0);
    HashMap<String, Class<?>> map = new HashMap<>();
    assertEquals(0, ((Object[]) a.getArray(1, 0, map)).length);
    assertEquals(2, ((Object[]) a.getArray(map)).length);
    assertEquals(2, ((Object[]) a.getArray(null)).length);
    map.put("x", Object.class);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, a).getArray(1, 0, map);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, a).getArray(map);
    ResultSet rs2;
    rs2 = a.getResultSet();
    rs2.next();
    assertEquals(1, rs2.getInt(1));
    assertEquals(0, rs2.getInt(2));
    rs2.next();
    assertEquals(2, rs2.getInt(1));
    assertEquals("Hello", rs2.getString(2));
    assertFalse(rs.next());
    map.clear();
    rs2 = a.getResultSet(map);
    rs2.next();
    assertEquals(1, rs2.getInt(1));
    assertEquals(0, rs2.getInt(2));
    rs2.next();
    assertEquals(2, rs2.getInt(1));
    assertEquals("Hello", rs2.getString(2));
    assertFalse(rs.next());
    rs2 = a.getResultSet(2, 1);
    rs2.next();
    assertEquals(2, rs2.getInt(1));
    assertEquals("Hello", rs2.getString(2));
    assertFalse(rs.next());
    rs2 = a.getResultSet(1, 1, map);
    rs2.next();
    assertEquals(1, rs2.getInt(1));
    assertEquals(0, rs2.getInt(2));
    assertFalse(rs.next());
    map.put("x", Object.class);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, a).getResultSet(map);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, a).getResultSet(0, 1, map);
    a.free();
    assertThrows(ErrorCode.OBJECT_CLOSED, a).getArray();
    assertThrows(ErrorCode.OBJECT_CLOSED, a).getResultSet();
    stat.execute("CREATE ALIAS ROOT FOR \"" + getClass().getName() + ".root\"");
    rs = stat.executeQuery("CALL ROOT(9)");
    rs.next();
    assertEquals(3, rs.getInt(1));
    assertFalse(rs.next());
    stat.execute("CREATE ALIAS MAX_ID FOR \"" + getClass().getName() + ".selectMaxId\"");
    rs = stat.executeQuery("CALL MAX_ID()");
    rs.next();
    assertEquals(2, rs.getInt(1));
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT * FROM MAX_ID()");
    rs.next();
    assertEquals(2, rs.getInt(1));
    assertFalse(rs.next());
    rs = stat.executeQuery("CALL CASE WHEN -9 < 0 THEN 0 ELSE ROOT(-9) END");
    rs.next();
    assertEquals(0, rs.getInt(1));
    assertFalse(rs.next());
    stat.execute("CREATE ALIAS blob FOR \"" + getClass().getName() + ".blob\"");
    rs = stat.executeQuery("SELECT blob(CAST('0102' AS BLOB)) FROM DUAL");
    while (rs.next()) {
    // ignore
    }
    rs.close();
    stat.execute("CREATE ALIAS clob FOR \"" + getClass().getName() + ".clob\"");
    rs = stat.executeQuery("SELECT clob(CAST('Hello' AS CLOB)) FROM DUAL");
    while (rs.next()) {
    // ignore
    }
    rs.close();
    stat.execute("create alias sql as " + "'ResultSet sql(Connection conn, String sql) " + "throws SQLException { return conn.createStatement().executeQuery(sql); }'");
    rs = stat.executeQuery("select * from sql('select cast(''Hello'' as clob)')");
    assertTrue(rs.next());
    assertEquals("Hello", rs.getString(1));
    rs = stat.executeQuery("select * from sql('select cast(''4869'' as blob)')");
    assertTrue(rs.next());
    assertEquals("Hi", new String(rs.getBytes(1)));
    rs = stat.executeQuery("select sql('select 1 a, ''Hello'' b')");
    assertTrue(rs.next());
    rs2 = (ResultSet) rs.getObject(1);
    rs2.next();
    assertEquals(1, rs2.getInt(1));
    assertEquals("Hello", rs2.getString(2));
    ResultSetMetaData meta2 = rs2.getMetaData();
    assertEquals(Types.INTEGER, meta2.getColumnType(1));
    assertEquals("INTEGER", meta2.getColumnTypeName(1));
    assertEquals("java.lang.Integer", meta2.getColumnClassName(1));
    assertEquals(Types.VARCHAR, meta2.getColumnType(2));
    assertEquals("VARCHAR", meta2.getColumnTypeName(2));
    assertEquals("java.lang.String", meta2.getColumnClassName(2));
    stat.execute("CREATE ALIAS blob2stream FOR \"" + getClass().getName() + ".blob2stream\"");
    stat.execute("CREATE ALIAS stream2stream FOR \"" + getClass().getName() + ".stream2stream\"");
    stat.execute("CREATE TABLE TEST_BLOB(ID INT PRIMARY KEY, VALUE BLOB)");
    stat.execute("INSERT INTO TEST_BLOB VALUES(0, null)");
    stat.execute("INSERT INTO TEST_BLOB VALUES(1, 'edd1f011edd1f011edd1f011')");
    rs = stat.executeQuery("SELECT blob2stream(VALUE) FROM TEST_BLOB");
    while (rs.next()) {
    // ignore
    }
    rs.close();
    rs = stat.executeQuery("SELECT stream2stream(VALUE) FROM TEST_BLOB");
    while (rs.next()) {
    // ignore
    }
    stat.execute("CREATE ALIAS NULL_RESULT FOR \"" + getClass().getName() + ".nullResultSet\"");
    rs = stat.executeQuery("CALL NULL_RESULT()");
    assertEquals(1, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals(null, rs.getString(1));
    assertFalse(rs.next());
    rs = meta.getProcedures(null, null, "NULL_RESULT");
    rs.next();
    assertEquals("FUNCTIONS", rs.getString("PROCEDURE_CAT"));
    assertEquals("PUBLIC", rs.getString("PROCEDURE_SCHEM"));
    assertEquals("NULL_RESULT", rs.getString("PROCEDURE_NAME"));
    assertEquals(0, rs.getInt("NUM_INPUT_PARAMS"));
    assertEquals(0, rs.getInt("NUM_OUTPUT_PARAMS"));
    assertEquals(0, rs.getInt("NUM_RESULT_SETS"));
    assertEquals("", rs.getString("REMARKS"));
    assertEquals(DatabaseMetaData.procedureReturnsResult, rs.getInt("PROCEDURE_TYPE"));
    assertEquals("NULL_RESULT", rs.getString("SPECIFIC_NAME"));
    rs = meta.getProcedureColumns(null, null, "NULL_RESULT", null);
    assertTrue(rs.next());
    assertEquals("P0", rs.getString("COLUMN_NAME"));
    assertFalse(rs.next());
    stat.execute("CREATE ALIAS RESULT_WITH_NULL FOR \"" + getClass().getName() + ".resultSetWithNull\"");
    rs = stat.executeQuery("CALL RESULT_WITH_NULL()");
    assertEquals(1, rs.getMetaData().getColumnCount());
    rs.next();
    assertEquals(null, rs.getString(1));
    assertFalse(rs.next());
    conn.close();
}

Example 20 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestLob method testLobHibernate.

private void testLobHibernate() throws Exception {
    deleteDb("lob");
    Connection conn0 = reconnect(null);
    conn0.getAutoCommit();
    conn0.setAutoCommit(false);
    DatabaseMetaData dbMeta0 = conn0.getMetaData();
    dbMeta0.getDatabaseProductName();
    dbMeta0.getDatabaseMajorVersion();
    dbMeta0.getDatabaseProductVersion();
    dbMeta0.getDriverName();
    dbMeta0.getDriverVersion();
    dbMeta0.supportsResultSetType(1004);
    dbMeta0.supportsBatchUpdates();
    dbMeta0.dataDefinitionCausesTransactionCommit();
    dbMeta0.dataDefinitionIgnoredInTransactions();
    dbMeta0.supportsGetGeneratedKeys();
    conn0.getAutoCommit();
    conn0.getAutoCommit();
    conn0.commit();
    conn0.setAutoCommit(true);
    Statement stat0 = conn0.createStatement();
    stat0.executeUpdate("drop table CLOB_ENTITY if exists");
    stat0.getWarnings();
    stat0.executeUpdate("create table CLOB_ENTITY (ID bigint not null, " + "DATA clob, CLOB_DATA clob, primary key (ID))");
    stat0.getWarnings();
    stat0.close();
    conn0.getWarnings();
    conn0.clearWarnings();
    conn0.setAutoCommit(false);
    conn0.getAutoCommit();
    conn0.getAutoCommit();
    PreparedStatement prep0 = conn0.prepareStatement("select max(ID) from CLOB_ENTITY");
    ResultSet rs0 = prep0.executeQuery();
    rs0.next();
    rs0.getLong(1);
    rs0.wasNull();
    rs0.close();
    prep0.close();
    conn0.getAutoCommit();
    PreparedStatement prep1 = conn0.prepareStatement("insert into CLOB_ENTITY" + "(DATA, CLOB_DATA, ID) values (?, ?, ?)");
    prep1.setNull(1, 2005);
    StringBuilder buff = new StringBuilder(10000);
    for (int i = 0; i < 10000; i++) {
        buff.append((char) ('0' + (i % 10)));
    }
    Reader x = new StringReader(buff.toString());
    prep1.setCharacterStream(2, x, 10000);
    prep1.setLong(3, 1);
    prep1.addBatch();
    prep1.executeBatch();
    prep1.close();
    conn0.getAutoCommit();
    conn0.getAutoCommit();
    conn0.commit();
    conn0.isClosed();
    conn0.getWarnings();
    conn0.clearWarnings();
    conn0.getAutoCommit();
    conn0.getAutoCommit();
    PreparedStatement prep2 = conn0.prepareStatement("select c_.ID as ID0_0_, c_.DATA as S_, " + "c_.CLOB_DATA as CLOB3_0_0_ from CLOB_ENTITY c_ where c_.ID=?");
    prep2.setLong(1, 1);
    ResultSet rs1 = prep2.executeQuery();
    rs1.next();
    rs1.getCharacterStream("S_");
    Clob clob0 = rs1.getClob("CLOB3_0_0_");
    rs1.wasNull();
    rs1.next();
    rs1.close();
    prep2.getMaxRows();
    prep2.getQueryTimeout();
    prep2.close();
    conn0.getAutoCommit();
    Reader r;
    int ch;
    r = clob0.getCharacterStream();
    for (int i = 0; i < 10000; i++) {
        ch = r.read();
        if (ch != ('0' + (i % 10))) {
            fail("expected " + (char) ('0' + (i % 10)) + " got: " + ch + " (" + (char) ch + ")");
        }
    }
    ch = r.read();
    if (ch != -1) {
        fail("expected -1 got: " + ch);
    }
    r.close();
    r = clob0.getCharacterStream(1235, 1000);
    for (int i = 1234; i < 2234; i++) {
        ch = r.read();
        if (ch != ('0' + (i % 10))) {
            fail("expected " + (char) ('0' + (i % 10)) + " got: " + ch + " (" + (char) ch + ")");
        }
    }
    ch = r.read();
    if (ch != -1) {
        fail("expected -1 got: " + ch);
    }
    r.close();
    assertThrows(ErrorCode.INVALID_VALUE_2, clob0).getCharacterStream(10001, 1);
    assertThrows(ErrorCode.INVALID_VALUE_2, clob0).getCharacterStream(10002, 0);
    conn0.close();
}