Search in sources :

Example 1 with PgServer

use of org.h2.server.pg.PgServer in project h2database by h2database.

the class TestPgServer method testDateTime.

private void testDateTime() throws SQLException {
    if (!getPgJdbcDriver()) {
        return;
    }
    Server server = createPgServer("-pgPort", "5535", "-pgDaemon", "-key", "pgserver", "mem:pgserver");
    try {
        Properties props = new Properties();
        props.setProperty("user", "sa");
        props.setProperty("password", "sa");
        // force binary
        props.setProperty("prepareThreshold", "-1");
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/pgserver", props);
        Statement stat = conn.createStatement();
        stat.execute("create table test(x1 date, x2 time, x3 timestamp)");
        Date[] dates = { null, Date.valueOf("2017-02-20"), Date.valueOf("1970-01-01"), Date.valueOf("1969-12-31"), Date.valueOf("1940-01-10"), Date.valueOf("1950-11-10"), Date.valueOf("1500-01-01") };
        Time[] times = { null, Time.valueOf("14:15:16"), Time.valueOf("00:00:00"), Time.valueOf("23:59:59"), Time.valueOf("00:10:59"), Time.valueOf("08:30:42"), Time.valueOf("10:00:00") };
        Timestamp[] timestamps = { null, Timestamp.valueOf("2017-02-20 14:15:16.763"), Timestamp.valueOf("1970-01-01 00:00:00"), Timestamp.valueOf("1969-12-31 23:59:59"), Timestamp.valueOf("1940-01-10 00:10:59"), Timestamp.valueOf("1950-11-10 08:30:42.12"), Timestamp.valueOf("1500-01-01 10:00:10") };
        int count = dates.length;
        PreparedStatement ps = conn.prepareStatement("insert into test values (?,?,?)");
        for (int i = 0; i < count; i++) {
            ps.setDate(1, dates[i]);
            ps.setTime(2, times[i]);
            ps.setTimestamp(3, timestamps[i]);
            ps.execute();
        }
        ResultSet rs = stat.executeQuery("select * from test");
        for (int i = 0; i < count; i++) {
            assertTrue(rs.next());
            assertEquals(dates[i], rs.getDate(1));
            assertEquals(times[i], rs.getTime(2));
            assertEquals(timestamps[i], rs.getTimestamp(3));
        }
        assertFalse(rs.next());
        conn.close();
    } finally {
        server.stop();
    }
}
Also used : Server(org.h2.tools.Server) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) Connection(java.sql.Connection) Time(java.sql.Time) PreparedStatement(java.sql.PreparedStatement) Properties(java.util.Properties) Timestamp(java.sql.Timestamp) Date(java.sql.Date) ResultSet(java.sql.ResultSet)

Example 2 with PgServer

use of org.h2.server.pg.PgServer in project h2database by h2database.

the class TestPgServer method testLowerCaseIdentifiers.

private void testLowerCaseIdentifiers() throws SQLException {
    if (!getPgJdbcDriver()) {
        return;
    }
    deleteDb("pgserver");
    Connection conn = getConnection("mem:pgserver;DATABASE_TO_UPPER=false", "sa", "sa");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int, name varchar(255))");
    Server server = createPgServer("-baseDir", getBaseDir(), "-pgPort", "5535", "-pgDaemon", "-key", "pgserver", "mem:pgserver");
    try {
        Connection conn2;
        conn2 = DriverManager.getConnection("jdbc:postgresql://localhost:5535/pgserver", "sa", "sa");
        stat = conn2.createStatement();
        stat.execute("select * from test");
        conn2.close();
    } finally {
        server.stop();
    }
    conn.close();
    deleteDb("pgserver");
}
Also used : Server(org.h2.tools.Server) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) Connection(java.sql.Connection)

Example 3 with PgServer

use of org.h2.server.pg.PgServer in project h2database by h2database.

the class TestPgServer method testPrepareWithUnspecifiedType.

private void testPrepareWithUnspecifiedType() throws Exception {
    if (!getPgJdbcDriver()) {
        return;
    }
    Server server = createPgServer("-pgPort", "5535", "-pgDaemon", "-key", "pgserver", "mem:pgserver");
    try {
        Properties props = new Properties();
        props.setProperty("user", "sa");
        props.setProperty("password", "sa");
        // force server side prepare
        props.setProperty("prepareThreshold", "1");
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/pgserver", props);
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("create table t1 (id integer, value timestamp)");
        stmt.close();
        PreparedStatement pstmt = conn.prepareStatement("insert into t1 values(100500, ?)");
        // assertTrue(((PGStatement) pstmt).isUseServerPrepare());
        assertEquals(Types.TIMESTAMP, pstmt.getParameterMetaData().getParameterType(1));
        Timestamp t = new Timestamp(System.currentTimeMillis());
        pstmt.setObject(1, t);
        assertEquals(1, pstmt.executeUpdate());
        pstmt.close();
        pstmt = conn.prepareStatement("SELECT * FROM t1 WHERE value = ?");
        assertEquals(Types.TIMESTAMP, pstmt.getParameterMetaData().getParameterType(1));
        pstmt.setObject(1, t);
        ResultSet rs = pstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(100500, rs.getInt(1));
        rs.close();
        pstmt.close();
        conn.close();
    } finally {
        server.stop();
    }
}
Also used : Server(org.h2.tools.Server) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) PreparedStatement(java.sql.PreparedStatement) Properties(java.util.Properties) Timestamp(java.sql.Timestamp)

Example 4 with PgServer

use of org.h2.server.pg.PgServer in project h2database by h2database.

the class TestPgServer method testKeyAlias.

private void testKeyAlias() throws SQLException {
    if (!getPgJdbcDriver()) {
        return;
    }
    Server server = createPgServer("-pgPort", "5535", "-pgDaemon", "-key", "pgserver", "mem:pgserver");
    try {
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/pgserver", "sa", "sa");
        Statement stat = conn.createStatement();
        // confirm that we've got the in memory implementation
        // by creating a table and checking flags
        stat.execute("create table test(id int primary key, name varchar)");
        ResultSet rs = stat.executeQuery("select storage_type from information_schema.tables " + "where table_name = 'TEST'");
        assertTrue(rs.next());
        assertEquals("MEMORY", rs.getString(1));
        conn.close();
    } finally {
        server.stop();
    }
}
Also used : Server(org.h2.tools.Server) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet)

Example 5 with PgServer

use of org.h2.server.pg.PgServer in project h2database by h2database.

the class TestPgServer method testCancelQuery.

private void testCancelQuery() throws Exception {
    if (!getPgJdbcDriver()) {
        return;
    }
    Server server = createPgServer("-pgPort", "5535", "-pgDaemon", "-key", "pgserver", "mem:pgserver");
    ExecutorService executor = Executors.newSingleThreadExecutor();
    try {
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/pgserver", "sa", "sa");
        final Statement stat = conn.createStatement();
        stat.execute("create alias sleep for \"java.lang.Thread.sleep\"");
        // create a table with 200 rows (cancel interval is 127)
        stat.execute("create table test(id int)");
        for (int i = 0; i < 200; i++) {
            stat.execute("insert into test (id) values (rand())");
        }
        Future<Boolean> future = executor.submit(new Callable<Boolean>() {

            @Override
            public Boolean call() throws SQLException {
                return stat.execute("select id, sleep(5) from test");
            }
        });
        // give it a little time to start and then cancel it
        Thread.sleep(100);
        stat.cancel();
        try {
            future.get();
            throw new IllegalStateException();
        } catch (ExecutionException e) {
            assertStartsWith(e.getCause().getMessage(), "ERROR: canceling statement due to user request");
        } finally {
            conn.close();
        }
    } finally {
        server.stop();
        executor.shutdown();
    }
    deleteDb("pgserver");
}
Also used : Server(org.h2.tools.Server) SQLException(java.sql.SQLException) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) ExecutorService(java.util.concurrent.ExecutorService) Connection(java.sql.Connection) ExecutionException(java.util.concurrent.ExecutionException)

Aggregations

Server (org.h2.tools.Server)7 Connection (java.sql.Connection)6 PreparedStatement (java.sql.PreparedStatement)6 Statement (java.sql.Statement)6 ResultSet (java.sql.ResultSet)4 Properties (java.util.Properties)3 Timestamp (java.sql.Timestamp)2 BigDecimal (java.math.BigDecimal)1 Date (java.sql.Date)1 SQLException (java.sql.SQLException)1 Time (java.sql.Time)1 ExecutionException (java.util.concurrent.ExecutionException)1 ExecutorService (java.util.concurrent.ExecutorService)1