Examples with JdbcStatement org.h2.jdbc.JdbcStatement used on opensource projects

Example 1 with JdbcStatement

use of org.h2.jdbc.JdbcStatement in project h2database by h2database.

the class JdbcConnection method createStatement.

/**
 * Creates a statement with the specified result set type and concurrency.
 *
 * @param resultSetType the result set type (ResultSet.TYPE_*)
 * @param resultSetConcurrency the concurrency (ResultSet.CONCUR_*)
 * @return the statement
 * @throws SQLException if the connection is closed or the result set type
 *             or concurrency are not supported
 */
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
    try {
        int id = getNextId(TraceObject.STATEMENT);
        if (isDebugEnabled()) {
            debugCodeAssign("Statement", TraceObject.STATEMENT, id, "createStatement(" + resultSetType + ", " + resultSetConcurrency + ")");
        }
        checkTypeConcurrency(resultSetType, resultSetConcurrency);
        checkClosed();
        return new JdbcStatement(this, id, resultSetType, resultSetConcurrency, false);
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 2 with JdbcStatement

use of org.h2.jdbc.JdbcStatement in project h2database by h2database.

the class JdbcConnection method getGeneratedKeys.

/**
 * INTERNAL
 */
ResultSet getGeneratedKeys(JdbcStatement stat, int id) {
    getGeneratedKeys = prepareCommand("SELECT SCOPE_IDENTITY() " + "WHERE SCOPE_IDENTITY() IS NOT NULL", getGeneratedKeys);
    ResultInterface result = getGeneratedKeys.executeQuery(0, false);
    return new JdbcResultSet(this, stat, getGeneratedKeys, result, id, false, true, false);
}

Example 3 with JdbcStatement

use of org.h2.jdbc.JdbcStatement in project h2database by h2database.

the class TestStatement method testStatement.

private void testStatement() throws SQLException {
    Statement stat = conn.createStatement();
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability());
    conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability());
    assertFalse(stat.isPoolable());
    stat.setPoolable(true);
    assertFalse(stat.isPoolable());
    // ignored
    stat.setCursorName("x");
    // fixed return value
    assertEquals(stat.getFetchDirection(), ResultSet.FETCH_FORWARD);
    // ignored
    stat.setFetchDirection(ResultSet.FETCH_REVERSE);
    // ignored
    stat.setMaxFieldSize(100);
    assertEquals(SysProperties.SERVER_RESULT_SET_FETCH_SIZE, stat.getFetchSize());
    stat.setFetchSize(10);
    assertEquals(10, stat.getFetchSize());
    stat.setFetchSize(0);
    assertEquals(SysProperties.SERVER_RESULT_SET_FETCH_SIZE, stat.getFetchSize());
    assertEquals(ResultSet.TYPE_FORWARD_ONLY, stat.getResultSetType());
    Statement stat2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
    assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, stat2.getResultSetType());
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, stat2.getResultSetHoldability());
    assertEquals(ResultSet.CONCUR_READ_ONLY, stat2.getResultSetConcurrency());
    assertEquals(0, stat.getMaxFieldSize());
    assertFalse(((JdbcStatement) stat2).isClosed());
    stat2.close();
    assertTrue(((JdbcStatement) stat2).isClosed());
    ResultSet rs;
    int count;
    long largeCount;
    boolean result;
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("SELECT * FROM TEST");
    stat.execute("DROP TABLE TEST");
    conn.getTypeMap();
    // this method should not throw an exception - if not supported, this
    // calls are ignored
    assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, stat.getResultSetHoldability());
    assertEquals(ResultSet.CONCUR_READ_ONLY, stat.getResultSetConcurrency());
    stat.cancel();
    stat.setQueryTimeout(10);
    assertTrue(stat.getQueryTimeout() == 10);
    stat.setQueryTimeout(0);
    assertTrue(stat.getQueryTimeout() == 0);
    assertThrows(ErrorCode.INVALID_VALUE_2, stat).setQueryTimeout(-1);
    assertTrue(stat.getQueryTimeout() == 0);
    trace("executeUpdate");
    count = stat.executeUpdate("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
    assertEquals(0, count);
    count = stat.executeUpdate("INSERT INTO TEST VALUES(1,'Hello')");
    assertEquals(1, count);
    count = stat.executeUpdate("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
    assertEquals(1, count);
    count = stat.executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE ID=2 OR ID=1");
    assertEquals(2, count);
    count = stat.executeUpdate("UPDATE TEST SET VALUE='\\LDBC\\' WHERE VALUE LIKE 'LDBC' ");
    assertEquals(2, count);
    count = stat.executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE VALUE LIKE '\\\\LDBC\\\\'");
    trace("count:" + count);
    assertEquals(2, count);
    count = stat.executeUpdate("DELETE FROM TEST WHERE ID=-1");
    assertEquals(0, count);
    count = stat.executeUpdate("DELETE FROM TEST WHERE ID=2");
    assertEquals(1, count);
    JdbcStatementBackwardsCompat statBC = (JdbcStatementBackwardsCompat) stat;
    largeCount = statBC.executeLargeUpdate("DELETE FROM TEST WHERE ID=-1");
    assertEquals(0, largeCount);
    assertEquals(0, statBC.getLargeUpdateCount());
    largeCount = statBC.executeLargeUpdate("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
    assertEquals(1, largeCount);
    assertEquals(1, statBC.getLargeUpdateCount());
    largeCount = statBC.executeLargeUpdate("DELETE FROM TEST WHERE ID=2");
    assertEquals(1, largeCount);
    assertEquals(1, statBC.getLargeUpdateCount());
    assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_QUERY, stat).executeUpdate("SELECT * FROM TEST");
    count = stat.executeUpdate("DROP TABLE TEST");
    assertTrue(count == 0);
    trace("execute");
    result = stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
    assertFalse(result);
    result = stat.execute("INSERT INTO TEST VALUES(1,'Hello')");
    assertFalse(result);
    result = stat.execute("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)");
    assertFalse(result);
    result = stat.execute("UPDATE TEST SET VALUE='LDBC' WHERE ID=2");
    assertFalse(result);
    result = stat.execute("DELETE FROM TEST WHERE ID=3");
    assertFalse(result);
    result = stat.execute("SELECT * FROM TEST");
    assertTrue(result);
    result = stat.execute("DROP TABLE TEST");
    assertFalse(result);
    assertThrows(ErrorCode.METHOD_ONLY_ALLOWED_FOR_QUERY, stat).executeQuery("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
    assertThrows(ErrorCode.METHOD_ONLY_ALLOWED_FOR_QUERY, stat).executeQuery("INSERT INTO TEST VALUES(1,'Hello')");
    assertThrows(ErrorCode.METHOD_ONLY_ALLOWED_FOR_QUERY, stat).executeQuery("UPDATE TEST SET VALUE='LDBC' WHERE ID=2");
    assertThrows(ErrorCode.METHOD_ONLY_ALLOWED_FOR_QUERY, stat).executeQuery("DELETE FROM TEST WHERE ID=3");
    stat.executeQuery("SELECT * FROM TEST");
    assertThrows(ErrorCode.METHOD_ONLY_ALLOWED_FOR_QUERY, stat).executeQuery("DROP TABLE TEST");
    // getMoreResults
    rs = stat.executeQuery("SELECT * FROM TEST");
    assertFalse(stat.getMoreResults());
    assertThrows(ErrorCode.OBJECT_CLOSED, rs).next();
    assertTrue(stat.getUpdateCount() == -1);
    count = stat.executeUpdate("DELETE FROM TEST");
    assertFalse(stat.getMoreResults());
    assertTrue(stat.getUpdateCount() == -1);
    stat.execute("DROP TABLE TEST");
    stat.executeUpdate("DROP TABLE IF EXISTS TEST");
    assertNull(stat.getWarnings());
    stat.clearWarnings();
    assertNull(stat.getWarnings());
    assertTrue(conn == stat.getConnection());
    assertEquals("SOME_ID", statBC.enquoteIdentifier("SOME_ID", false));
    assertEquals("\"SOME ID\"", statBC.enquoteIdentifier("SOME ID", false));
    assertEquals("\"SOME_ID\"", statBC.enquoteIdentifier("SOME_ID", true));
    assertEquals("\"FROM\"", statBC.enquoteIdentifier("FROM", false));
    assertEquals("\"Test\"", statBC.enquoteIdentifier("Test", false));
    assertEquals("\"TODAY\"", statBC.enquoteIdentifier("TODAY", false));
    assertTrue(statBC.isSimpleIdentifier("SOME_ID"));
    assertFalse(statBC.isSimpleIdentifier("SOME ID"));
    assertFalse(statBC.isSimpleIdentifier("FROM"));
    assertFalse(statBC.isSimpleIdentifier("Test"));
    assertFalse(statBC.isSimpleIdentifier("TODAY"));
    stat.close();
}

Example 4 with JdbcStatement

use of org.h2.jdbc.JdbcStatement in project h2database by h2database.

the class TestGetGeneratedKeys method testStatementExecuteLargeUpdate_StringArray.

/**
 * Test method for {@link Statement#executeLargeUpdate(String, String[])}.
 *
 * @param conn
 *            connection
 * @throws Exception
 *             on exception
 */
private void testStatementExecuteLargeUpdate_StringArray(Connection conn) throws Exception {
    JdbcStatement stat = (JdbcStatement) conn.createStatement();
    stat.execute("CREATE TABLE TEST (ID BIGINT PRIMARY KEY AUTO_INCREMENT," + "UID UUID NOT NULL DEFAULT RANDOM_UUID(), VALUE INT NOT NULL)");
    stat.executeLargeUpdate("INSERT INTO TEST(VALUE) VALUES (10)", new String[0]);
    ResultSet rs = stat.getGeneratedKeys();
    assertFalse(rs.next());
    rs.close();
    stat.executeLargeUpdate("INSERT INTO TEST(VALUE) VALUES (20)", new String[] { "ID", "UID" });
    rs = stat.getGeneratedKeys();
    assertEquals(2, rs.getMetaData().getColumnCount());
    assertEquals("ID", rs.getMetaData().getColumnName(1));
    assertEquals("UID", rs.getMetaData().getColumnName(2));
    assertTrue(rs.next());
    assertEquals(2L, rs.getLong(1));
    assertEquals(UUID.class, rs.getObject(2).getClass());
    assertFalse(rs.next());
    rs.close();
    stat.executeLargeUpdate("INSERT INTO TEST(VALUE) VALUES (30)", new String[] { "UID", "ID" });
    rs = stat.getGeneratedKeys();
    assertEquals(2, rs.getMetaData().getColumnCount());
    assertEquals("UID", rs.getMetaData().getColumnName(1));
    assertEquals("ID", rs.getMetaData().getColumnName(2));
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertEquals(3L, rs.getLong(2));
    assertFalse(rs.next());
    rs.close();
    stat.executeLargeUpdate("INSERT INTO TEST(VALUE) VALUES (40)", new String[] { "UID" });
    rs = stat.getGeneratedKeys();
    assertEquals(1, rs.getMetaData().getColumnCount());
    assertEquals("UID", rs.getMetaData().getColumnName(1));
    assertTrue(rs.next());
    assertEquals(UUID.class, rs.getObject(1).getClass());
    assertFalse(rs.next());
    rs.close();
    stat.execute("DROP TABLE TEST");
}

Example 5 with JdbcStatement

use of org.h2.jdbc.JdbcStatement in project h2database by h2database.

the class PgServerThread method process.

private void process() throws IOException {
    int x;
    if (initDone) {
        x = dataInRaw.read();
        if (x < 0) {
            stop = true;
            return;
        }
    } else {
        x = 0;
    }
    int len = dataInRaw.readInt();
    len -= 4;
    byte[] data = Utils.newBytes(len);
    dataInRaw.readFully(data, 0, len);
    dataIn = new DataInputStream(new ByteArrayInputStream(data, 0, len));
    switch(x) {
        case 0:
            server.trace("Init");
            int version = readInt();
            if (version == 80877102) {
                server.trace("CancelRequest");
                int pid = readInt();
                int key = readInt();
                PgServerThread c = server.getThread(pid);
                if (c != null && key == c.secret) {
                    c.cancelRequest();
                } else {
                    // According to the PostgreSQL documentation, when canceling
                    // a request, if an invalid secret is provided then no
                    // exception should be sent back to the client.
                    server.trace("Invalid CancelRequest: pid=" + pid + ", key=" + key);
                }
                close();
            } else if (version == 80877103) {
                server.trace("SSLRequest");
                out.write('N');
            } else {
                server.trace("StartupMessage");
                server.trace(" version " + version + " (" + (version >> 16) + "." + (version & 0xff) + ")");
                while (true) {
                    String param = readString();
                    if (param.length() == 0) {
                        break;
                    }
                    String value = readString();
                    if ("user".equals(param)) {
                        this.userName = value;
                    } else if ("database".equals(param)) {
                        this.databaseName = server.checkKeyAndGetDatabaseName(value);
                    } else if ("client_encoding".equals(param)) {
                        // UTF8
                        clientEncoding = value;
                    } else if ("DateStyle".equals(param)) {
                        if (value.indexOf(',') < 0) {
                            value += ", MDY";
                        }
                        dateStyle = value;
                    }
                    // extra_float_digits 2
                    // geqo on (Genetic Query Optimization)
                    server.trace(" param " + param + "=" + value);
                }
                sendAuthenticationCleartextPassword();
                initDone = true;
            }
            break;
        case 'p':
            {
                server.trace("PasswordMessage");
                String password = readString();
                try {
                    Properties info = new Properties();
                    info.put("MODE", "PostgreSQL");
                    info.put("USER", userName);
                    info.put("PASSWORD", password);
                    String url = "jdbc:h2:" + databaseName;
                    ConnectionInfo ci = new ConnectionInfo(url, info);
                    String baseDir = server.getBaseDir();
                    if (baseDir == null) {
                        baseDir = SysProperties.getBaseDir();
                    }
                    if (baseDir != null) {
                        ci.setBaseDir(baseDir);
                    }
                    if (server.getIfExists()) {
                        ci.setProperty("IFEXISTS", "TRUE");
                    }
                    conn = new JdbcConnection(ci, false);
                    // can not do this because when called inside
                    // DriverManager.getConnection, a deadlock occurs
                    // conn = DriverManager.getConnection(url, userName, password);
                    initDb();
                    sendAuthenticationOk();
                } catch (Exception e) {
                    e.printStackTrace();
                    stop = true;
                }
                break;
            }
        case 'P':
            {
                server.trace("Parse");
                Prepared p = new Prepared();
                p.name = readString();
                p.sql = getSQL(readString());
                int paramTypesCount = readShort();
                int[] paramTypes = null;
                if (paramTypesCount > 0) {
                    paramTypes = new int[paramTypesCount];
                    for (int i = 0; i < paramTypesCount; i++) {
                        paramTypes[i] = readInt();
                    }
                }
                try {
                    p.prep = (JdbcPreparedStatement) conn.prepareStatement(p.sql);
                    ParameterMetaData meta = p.prep.getParameterMetaData();
                    p.paramType = new int[meta.getParameterCount()];
                    for (int i = 0; i < p.paramType.length; i++) {
                        int type;
                        if (i < paramTypesCount && paramTypes[i] != 0) {
                            type = paramTypes[i];
                            server.checkType(type);
                        } else {
                            type = PgServer.convertType(meta.getParameterType(i + 1));
                        }
                        p.paramType[i] = type;
                    }
                    prepared.put(p.name, p);
                    sendParseComplete();
                } catch (Exception e) {
                    sendErrorResponse(e);
                }
                break;
            }
        case 'B':
            {
                server.trace("Bind");
                Portal portal = new Portal();
                portal.name = readString();
                String prepName = readString();
                Prepared prep = prepared.get(prepName);
                if (prep == null) {
                    sendErrorResponse("Prepared not found");
                    break;
                }
                portal.prep = prep;
                portals.put(portal.name, portal);
                int formatCodeCount = readShort();
                int[] formatCodes = new int[formatCodeCount];
                for (int i = 0; i < formatCodeCount; i++) {
                    formatCodes[i] = readShort();
                }
                int paramCount = readShort();
                try {
                    for (int i = 0; i < paramCount; i++) {
                        setParameter(prep.prep, prep.paramType[i], i, formatCodes);
                    }
                } catch (Exception e) {
                    sendErrorResponse(e);
                    break;
                }
                int resultCodeCount = readShort();
                portal.resultColumnFormat = new int[resultCodeCount];
                for (int i = 0; i < resultCodeCount; i++) {
                    portal.resultColumnFormat[i] = readShort();
                }
                sendBindComplete();
                break;
            }
        case 'C':
            {
                char type = (char) readByte();
                String name = readString();
                server.trace("Close");
                if (type == 'S') {
                    Prepared p = prepared.remove(name);
                    if (p != null) {
                        JdbcUtils.closeSilently(p.prep);
                    }
                } else if (type == 'P') {
                    portals.remove(name);
                } else {
                    server.trace("expected S or P, got " + type);
                    sendErrorResponse("expected S or P");
                    break;
                }
                sendCloseComplete();
                break;
            }
        case 'D':
            {
                char type = (char) readByte();
                String name = readString();
                server.trace("Describe");
                if (type == 'S') {
                    Prepared p = prepared.get(name);
                    if (p == null) {
                        sendErrorResponse("Prepared not found: " + name);
                    } else {
                        try {
                            sendParameterDescription(p.prep.getParameterMetaData(), p.paramType);
                            sendRowDescription(p.prep.getMetaData());
                        } catch (Exception e) {
                            sendErrorResponse(e);
                        }
                    }
                } else if (type == 'P') {
                    Portal p = portals.get(name);
                    if (p == null) {
                        sendErrorResponse("Portal not found: " + name);
                    } else {
                        PreparedStatement prep = p.prep.prep;
                        try {
                            ResultSetMetaData meta = prep.getMetaData();
                            sendRowDescription(meta);
                        } catch (Exception e) {
                            sendErrorResponse(e);
                        }
                    }
                } else {
                    server.trace("expected S or P, got " + type);
                    sendErrorResponse("expected S or P");
                }
                break;
            }
        case 'E':
            {
                String name = readString();
                server.trace("Execute");
                Portal p = portals.get(name);
                if (p == null) {
                    sendErrorResponse("Portal not found: " + name);
                    break;
                }
                int maxRows = readShort();
                Prepared prepared = p.prep;
                JdbcPreparedStatement prep = prepared.prep;
                server.trace(prepared.sql);
                try {
                    prep.setMaxRows(maxRows);
                    setActiveRequest(prep);
                    boolean result = prep.execute();
                    if (result) {
                        try {
                            ResultSet rs = prep.getResultSet();
                            // the meta-data is sent in the prior 'Describe'
                            while (rs.next()) {
                                sendDataRow(rs, p.resultColumnFormat);
                            }
                            sendCommandComplete(prep, 0);
                        } catch (Exception e) {
                            sendErrorResponse(e);
                        }
                    } else {
                        sendCommandComplete(prep, prep.getUpdateCount());
                    }
                } catch (Exception e) {
                    if (prep.isCancelled()) {
                        sendCancelQueryResponse();
                    } else {
                        sendErrorResponse(e);
                    }
                } finally {
                    setActiveRequest(null);
                }
                break;
            }
        case 'S':
            {
                server.trace("Sync");
                sendReadyForQuery();
                break;
            }
        case 'Q':
            {
                server.trace("Query");
                String query = readString();
                ScriptReader reader = new ScriptReader(new StringReader(query));
                while (true) {
                    JdbcStatement stat = null;
                    try {
                        String s = reader.readStatement();
                        if (s == null) {
                            break;
                        }
                        s = getSQL(s);
                        stat = (JdbcStatement) conn.createStatement();
                        setActiveRequest(stat);
                        boolean result = stat.execute(s);
                        if (result) {
                            ResultSet rs = stat.getResultSet();
                            ResultSetMetaData meta = rs.getMetaData();
                            try {
                                sendRowDescription(meta);
                                while (rs.next()) {
                                    sendDataRow(rs, null);
                                }
                                sendCommandComplete(stat, 0);
                            } catch (Exception e) {
                                sendErrorResponse(e);
                                break;
                            }
                        } else {
                            sendCommandComplete(stat, stat.getUpdateCount());
                        }
                    } catch (SQLException e) {
                        if (stat != null && stat.isCancelled()) {
                            sendCancelQueryResponse();
                        } else {
                            sendErrorResponse(e);
                        }
                        break;
                    } finally {
                        JdbcUtils.closeSilently(stat);
                        setActiveRequest(null);
                    }
                }
                sendReadyForQuery();
                break;
            }
        case 'X':
            {
                server.trace("Terminate");
                close();
                break;
            }
        default:
            server.trace("Unsupported: " + x + " (" + (char) x + ")");
            break;
    }
}