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

Example 66 with Alias

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

the class TestFunctions method testDynamicArgumentAndReturn.

private void testDynamicArgumentAndReturn() throws SQLException {
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    ResultSet rs;
    stat.execute("create alias dynamic deterministic for \"" + getClass().getName() + ".dynamic\"");
    setCount(0);
    rs = stat.executeQuery("call dynamic(('a', 1))[0]");
    rs.next();
    String a = rs.getString(1);
    assertEquals("a1", a);
    stat.execute("drop alias dynamic");
    conn.close();
}

Example 67 with Alias

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

the class TestIndex method testFunctionIndex.

private void testFunctionIndex() throws SQLException {
    testFunctionIndexCounter = 0;
    stat.execute("CREATE ALIAS TEST_INDEX FOR \"" + TestIndex.class.getName() + ".testFunctionIndexFunction\"");
    try (ResultSet rs = stat.executeQuery("SELECT * FROM TEST_INDEX() WHERE ID = 1 OR ID = 3")) {
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(10, rs.getInt(2));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertEquals(30, rs.getInt(2));
        assertFalse(rs.next());
    } finally {
        stat.execute("DROP ALIAS TEST_INDEX");
    }
    assertEquals(1, testFunctionIndexCounter);
}

Example 68 with Alias

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

the class TestCallableStatement method testPrepare.

private void testPrepare(Connection conn) throws Exception {
    Statement stat = conn.createStatement();
    CallableStatement call;
    ResultSet rs;
    stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
    call = conn.prepareCall("INSERT INTO TEST VALUES(?, ?)");
    call.setInt(1, 1);
    call.setString(2, "Hello");
    call.execute();
    call = conn.prepareCall("SELECT * FROM TEST", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    rs = call.executeQuery();
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    call = conn.prepareCall("SELECT * FROM TEST", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    rs = call.executeQuery();
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("CREATE ALIAS testCall FOR \"" + getClass().getName() + ".testCall\"");
    call = conn.prepareCall("{CALL testCall(?, ?, ?, ?)}");
    call.setInt("A", 50);
    call.setString("B", "abc");
    long t = System.currentTimeMillis();
    call.setTimestamp("C", new Timestamp(t));
    call.setTimestamp("D", Timestamp.valueOf("2001-02-03 10:20:30.0"));
    call.registerOutParameter(1, Types.INTEGER);
    call.registerOutParameter("B", Types.VARCHAR);
    call.executeUpdate();
    try {
        call.getTimestamp("C");
        fail("not registered out parameter accessible");
    } catch (SQLException e) {
    // expected exception
    }
    call.registerOutParameter(3, Types.TIMESTAMP);
    call.registerOutParameter(4, Types.TIMESTAMP);
    call.executeUpdate();
    assertEquals(t + 1, call.getTimestamp(3).getTime());
    assertEquals(t + 1, call.getTimestamp("C").getTime());
    assertEquals("2001-02-03 10:20:30.0", call.getTimestamp(4).toString());
    assertEquals("2001-02-03 10:20:30.0", call.getTimestamp("D").toString());
    if (LocalDateTimeUtils.isJava8DateApiPresent()) {
        assertEquals("2001-02-03T10:20:30", call.getObject(4, LocalDateTimeUtils.LOCAL_DATE_TIME).toString());
        assertEquals("2001-02-03T10:20:30", call.getObject("D", LocalDateTimeUtils.LOCAL_DATE_TIME).toString());
    }
    assertEquals("10:20:30", call.getTime(4).toString());
    assertEquals("10:20:30", call.getTime("D").toString());
    if (LocalDateTimeUtils.isJava8DateApiPresent()) {
        assertEquals("10:20:30", call.getObject(4, LocalDateTimeUtils.LOCAL_TIME).toString());
        assertEquals("10:20:30", call.getObject("D", LocalDateTimeUtils.LOCAL_TIME).toString());
    }
    assertEquals("2001-02-03", call.getDate(4).toString());
    assertEquals("2001-02-03", call.getDate("D").toString());
    if (LocalDateTimeUtils.isJava8DateApiPresent()) {
        assertEquals("2001-02-03", call.getObject(4, LocalDateTimeUtils.LOCAL_DATE).toString());
        assertEquals("2001-02-03", call.getObject("D", LocalDateTimeUtils.LOCAL_DATE).toString());
    }
    assertEquals(100, call.getInt(1));
    assertEquals(100, call.getInt("A"));
    assertEquals(100, call.getLong(1));
    assertEquals(100, call.getLong("A"));
    assertEquals("100", call.getBigDecimal(1).toString());
    assertEquals("100", call.getBigDecimal("A").toString());
    assertEquals(100, call.getFloat(1));
    assertEquals(100, call.getFloat("A"));
    assertEquals(100, call.getDouble(1));
    assertEquals(100, call.getDouble("A"));
    assertEquals(100, call.getByte(1));
    assertEquals(100, call.getByte("A"));
    assertEquals(100, call.getShort(1));
    assertEquals(100, call.getShort("A"));
    assertTrue(call.getBoolean(1));
    assertTrue(call.getBoolean("A"));
    assertEquals("ABC", call.getString(2));
    Reader r = call.getCharacterStream(2);
    assertEquals("ABC", IOUtils.readStringAndClose(r, -1));
    r = call.getNCharacterStream(2);
    assertEquals("ABC", IOUtils.readStringAndClose(r, -1));
    assertEquals("ABC", call.getString("B"));
    assertEquals("ABC", call.getNString(2));
    assertEquals("ABC", call.getNString("B"));
    assertEquals("ABC", call.getClob(2).getSubString(1, 3));
    assertEquals("ABC", call.getClob("B").getSubString(1, 3));
    assertEquals("ABC", call.getNClob(2).getSubString(1, 3));
    assertEquals("ABC", call.getNClob("B").getSubString(1, 3));
    try {
        call.getString(100);
        fail("incorrect parameter index value");
    } catch (SQLException e) {
    // expected exception
    }
    try {
        call.getString(0);
        fail("incorrect parameter index value");
    } catch (SQLException e) {
    // expected exception
    }
    try {
        call.getBoolean("X");
        fail("incorrect parameter name value");
    } catch (SQLException e) {
    // expected exception
    }
    call.setCharacterStream("B", new StringReader("xyz"));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setCharacterStream("B", new StringReader("xyz-"), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setCharacterStream("B", new StringReader("xyz-"), 3L);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setAsciiStream("B", new ByteArrayInputStream("xyz".getBytes(StandardCharsets.UTF_8)));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setAsciiStream("B", new ByteArrayInputStream("xyz-".getBytes(StandardCharsets.UTF_8)), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setAsciiStream("B", new ByteArrayInputStream("xyz-".getBytes(StandardCharsets.UTF_8)), 3L);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setClob("B", new StringReader("xyz"));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setClob("B", new StringReader("xyz-"), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setNClob("B", new StringReader("xyz"));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setNClob("B", new StringReader("xyz-"), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setString("B", "xyz");
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setNString("B", "xyz");
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    // test for exceptions after closing
    call.close();
    assertThrows(ErrorCode.OBJECT_CLOSED, call).executeUpdate();
    assertThrows(ErrorCode.OBJECT_CLOSED, call).registerOutParameter(1, Types.INTEGER);
    assertThrows(ErrorCode.OBJECT_CLOSED, call).getString("X");
}

Example 69 with Alias

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

the class TestSpatial method getRandomGeometryTable.

/**
 * Generate a result set with random geometry data.
 * Used as an ALIAS function.
 *
 * @param seed the random seed
 * @param rowCount the number of rows
 * @param minX the smallest x
 * @param maxX the largest x
 * @param minY the smallest y
 * @param maxY the largest y
 * @param maxLength the maximum length
 * @return a result set
 */
public static ResultSet getRandomGeometryTable(final long seed, final long rowCount, final double minX, final double maxX, final double minY, final double maxY, final double maxLength) {
    SimpleResultSet rs = new SimpleResultSet(new SimpleRowSource() {

        private final Random random = new Random(seed);

        private int currentRow;

        @Override
        public Object[] readRow() throws SQLException {
            if (currentRow++ < rowCount) {
                return new Object[] { getRandomGeometry(random, minX, maxX, minY, maxY, maxLength) };
            }
            return null;
        }

        @Override
        public void close() {
        // nothing to do
        }

        @Override
        public void reset() throws SQLException {
            random.setSeed(seed);
        }
    });
    rs.addColumn("the_geom", Types.OTHER, "GEOMETRY", Integer.MAX_VALUE, 0);
    return rs;
}

Example 70 with Alias

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

the class TestSpatial method testJavaAliasTableFunction.

/**
 * Test java alias with Geometry type.
 */
private void testJavaAliasTableFunction() throws SQLException {
    deleteDb("spatial");
    try (Connection conn = getConnection(URL)) {
        Statement stat = conn.createStatement();
        stat.execute("CREATE ALIAS T_RANDOM_GEOM_TABLE FOR \"" + TestSpatial.class.getName() + ".getRandomGeometryTable\"");
        stat.execute("create table test as " + "select * from T_RANDOM_GEOM_TABLE(42,20,-100,100,-100,100,4)");
        stat.execute("DROP ALIAS T_RANDOM_GEOM_TABLE");
        ResultSet rs = stat.executeQuery("select count(*) from test");
        assertTrue(rs.next());
        assertEquals(20, rs.getInt(1));
    }
    deleteDb("spatial");
}