Examples with Alias org.h2.expression.Alias used on opensource projects

Example 16 with Alias

use of org.h2.expression.Alias in project h2database by h2database.

the class TestCallableStatement method testArrayReturnValue.

private void testArrayReturnValue(Connection connection) throws SQLException {
    Object[][] arraysToTest = new Object[][] { new Object[] { 0, 1, 2 }, new Object[] { 0, "1", 2 }, new Object[] { 0, null, 2 }, new Object[] { 0, new Object[] { "s", 1 }, new Object[] { null, 1L } } };
    try (Statement statement = connection.createStatement()) {
        statement.execute("CREATE ALIAS arrayIdentiy FOR \"" + getClass().getName() + ".arrayIdentiy\"");
        for (Object[] arrayToTest : arraysToTest) {
            Array sqlInputArray = connection.createArrayOf("ignored", arrayToTest);
            try {
                try (CallableStatement callableStatement = connection.prepareCall("{call arrayIdentiy(?)}")) {
                    callableStatement.setArray(1, sqlInputArray);
                    assertTrue(callableStatement.execute());
                    try (ResultSet resultSet = callableStatement.getResultSet()) {
                        assertTrue(resultSet.next());
                        // test getArray()
                        Array sqlReturnArray = resultSet.getArray(1);
                        try {
                            assertEquals((Object[]) sqlInputArray.getArray(), (Object[]) sqlReturnArray.getArray());
                        } finally {
                            sqlReturnArray.free();
                        }
                        // test getObject(Array.class)
                        sqlReturnArray = resultSet.getObject(1, Array.class);
                        try {
                            assertEquals((Object[]) sqlInputArray.getArray(), (Object[]) sqlReturnArray.getArray());
                        } finally {
                            sqlReturnArray.free();
                        }
                        assertFalse(resultSet.next());
                    }
                }
            } finally {
                sqlInputArray.free();
            }
        }
    }
}

Example 17 with Alias

use of org.h2.expression.Alias in project h2database by h2database.

the class TestCallableStatement method testClassLoader.

private void testClassLoader(Connection conn) throws SQLException {
    Utils.ClassFactory myFactory = new TestClassFactory();
    JdbcUtils.addClassFactory(myFactory);
    try {
        Statement stat = conn.createStatement();
        stat.execute("CREATE ALIAS T_CLASSLOADER FOR \"TestClassFactory.testClassF\"");
        ResultSet rs = stat.executeQuery("SELECT T_CLASSLOADER(true)");
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
    } finally {
        JdbcUtils.removeClassFactory(myFactory);
    }
}

Example 18 with Alias

use of org.h2.expression.Alias in project h2database by h2database.

the class TestView method testChangeSchemaSearchPath.

private void testChangeSchemaSearchPath() throws SQLException {
    deleteDb("view");
    Connection conn = getConnection("view;FUNCTIONS_IN_SCHEMA=TRUE");
    Statement stat = conn.createStatement();
    stat.execute("CREATE ALIAS X AS $$ int x() { return 1; } $$;");
    stat.execute("CREATE SCHEMA S");
    stat.execute("CREATE VIEW S.TEST AS SELECT X() FROM DUAL");
    stat.execute("SET SCHEMA=S");
    stat.execute("SET SCHEMA_SEARCH_PATH=S");
    stat.execute("SELECT * FROM TEST");
    conn.close();
}

Example 19 with Alias

use of org.h2.expression.Alias 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 Alias

use of org.h2.expression.Alias in project h2database by h2database.

the class TestFunctions method callCompiledFunction.

private void callCompiledFunction(String functionName) throws SQLException {
    deleteDb("functions");
    try (Connection conn = getConnection("functions")) {
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("create alias " + functionName + " AS " + "$$ boolean " + functionName + "() " + "{ return true; } $$;");
        PreparedStatement stmt = conn.prepareStatement("select " + functionName + "() from dual");
        rs = stmt.executeQuery();
        rs.next();
        assertEquals(Boolean.class.getName(), rs.getObject(1).getClass().getName());
        stat.execute("drop alias " + functionName + "");
    }
}