Examples with Call org.h2.command.dml.Call used on opensource projects

Example 71 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestFunctions method testMathFunctions.

private void testMathFunctions() throws SQLException {
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("CALL SINH(50)");
    assertTrue(rs.next());
    assertEquals(Math.sinh(50), rs.getDouble(1));
    rs = stat.executeQuery("CALL COSH(50)");
    assertTrue(rs.next());
    assertEquals(Math.cosh(50), rs.getDouble(1));
    rs = stat.executeQuery("CALL TANH(50)");
    assertTrue(rs.next());
    assertEquals(Math.tanh(50), rs.getDouble(1));
    conn.close();
}

Example 72 with Call

use of org.h2.command.dml.Call 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 73 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestFunctions method testTransactionId.

private void testTransactionId() throws SQLException {
    if (config.memory) {
        return;
    }
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int)");
    ResultSet rs;
    rs = stat.executeQuery("call transaction_id()");
    rs.next();
    assertTrue(rs.getString(1) == null && rs.wasNull());
    stat.execute("insert into test values(1)");
    rs = stat.executeQuery("call transaction_id()");
    rs.next();
    assertTrue(rs.getString(1) == null && rs.wasNull());
    conn.setAutoCommit(false);
    stat.execute("delete from test");
    rs = stat.executeQuery("call transaction_id()");
    rs.next();
    assertNotNull(rs.getString(1));
    stat.execute("drop table test");
    conn.close();
}

Example 74 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestFunctions method testValue.

private void testValue() throws SQLException {
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    ResultSet rs;
    stat.execute("create alias TO_CHAR_2 for \"" + getClass().getName() + ".toChar\"");
    rs = stat.executeQuery("call TO_CHAR_2(TIMESTAMP '2001-02-03 04:05:06', 'format')");
    rs.next();
    assertEquals("2001-02-03 04:05:06", rs.getString(1));
    stat.execute("drop alias TO_CHAR_2");
    conn.close();
}

Example 75 with Call

use of org.h2.command.dml.Call in project h2database by h2database.

the class TestFunctions method testArrayParameters.

private void testArrayParameters() throws SQLException {
    deleteDb("functions");
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    ResultSet rs;
    stat.execute("create alias array_test AS " + "$$ Integer[] array_test(Integer[] in_array) " + "{ return in_array; } $$;");
    PreparedStatement stmt = conn.prepareStatement("select array_test(?) from dual");
    stmt.setObject(1, new Integer[] { 1, 2 });
    rs = stmt.executeQuery();
    rs.next();
    assertEquals(Integer[].class.getName(), rs.getObject(1).getClass().getName());
    CallableStatement call = conn.prepareCall("{ ? = call array_test(?) }");
    call.setObject(2, new Integer[] { 2, 1 });
    call.registerOutParameter(1, Types.ARRAY);
    call.execute();
    assertEquals(Integer[].class.getName(), call.getArray(1).getArray().getClass().getName());
    assertEquals(new Integer[] { 2, 1 }, (Integer[]) call.getObject(1));
    stat.execute("drop alias array_test");
    conn.close();
}