Examples with Prepared org.h2.test.db.Db.Prepared used on opensource projects

Example 36 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class JdbcConnection method prepareStatement.

/**
 * Creates a prepared statement with the specified result set type,
 * concurrency, and holdability.
 *
 * @param sql the SQL statement
 * @param resultSetType the result set type (ResultSet.TYPE_*)
 * @param resultSetConcurrency the concurrency (ResultSet.CONCUR_*)
 * @param resultSetHoldability the holdability (ResultSet.HOLD* / CLOSE*)
 * @return the prepared statement
 * @throws SQLException if the connection is closed or the result set type,
 *             concurrency, or holdability are not supported
 */
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
    try {
        int id = getNextId(TraceObject.PREPARED_STATEMENT);
        if (isDebugEnabled()) {
            debugCodeAssign("PreparedStatement", TraceObject.PREPARED_STATEMENT, id, "prepareStatement(" + quote(sql) + ", " + resultSetType + ", " + resultSetConcurrency + ", " + resultSetHoldability + ")");
        }
        checkTypeConcurrency(resultSetType, resultSetConcurrency);
        checkHoldability(resultSetHoldability);
        checkClosed();
        sql = translateSQL(sql);
        return new JdbcPreparedStatement(this, sql, id, resultSetType, resultSetConcurrency, false, false);
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 37 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class JdbcConnection method prepareStatement.

/**
 * Creates a new prepared statement.
 *
 * @param sql the SQL statement
 * @param columnNames
 *            an array of column names indicating the columns with generated
 *            keys that should be returned from the inserted row
 * @return the prepared statement
 * @throws SQLException if the connection is closed
 */
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
    try {
        int id = getNextId(TraceObject.PREPARED_STATEMENT);
        if (isDebugEnabled()) {
            debugCodeAssign("PreparedStatement", TraceObject.PREPARED_STATEMENT, id, "prepareStatement(" + quote(sql) + ", " + quoteArray(columnNames) + ");");
        }
        checkClosed();
        sql = translateSQL(sql);
        return new JdbcPreparedStatement(this, sql, id, ResultSet.TYPE_FORWARD_ONLY, Constants.DEFAULT_RESULT_SET_CONCURRENCY, false, columnNames);
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 38 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class JdbcConnection method prepareAutoCloseStatement.

/**
 * Prepare a statement that will automatically close when the result set is
 * closed. This method is used to retrieve database meta data.
 *
 * @param sql the SQL statement
 * @return the prepared statement
 */
PreparedStatement prepareAutoCloseStatement(String sql) throws SQLException {
    try {
        int id = getNextId(TraceObject.PREPARED_STATEMENT);
        if (isDebugEnabled()) {
            debugCodeAssign("PreparedStatement", TraceObject.PREPARED_STATEMENT, id, "prepareStatement(" + quote(sql) + ")");
        }
        checkClosed();
        sql = translateSQL(sql);
        return new JdbcPreparedStatement(this, sql, id, ResultSet.TYPE_FORWARD_ONLY, Constants.DEFAULT_RESULT_SET_CONCURRENCY, true, false);
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 39 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class TestStatement method testPreparedStatement.

private void testPreparedStatement() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name varchar(255))");
    stat.execute("insert into test values(1, 'Hello')");
    stat.execute("insert into test values(2, 'World')");
    PreparedStatement ps = conn.prepareStatement("select name from test where id in (select id from test where name REGEXP ?)");
    ps.setString(1, "Hello");
    ResultSet rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("Hello", rs.getString("name"));
    assertFalse(rs.next());
    ps.setString(1, "World");
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("World", rs.getString("name"));
    assertFalse(rs.next());
    // Changes the table structure
    stat.execute("create index t_id on test(name)");
    // Test the prepared statement again to check if the internal cache attributes were reset
    ps.setString(1, "Hello");
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("Hello", rs.getString("name"));
    assertFalse(rs.next());
    ps.setString(1, "World");
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals("World", rs.getString("name"));
    assertFalse(rs.next());
    ps = conn.prepareStatement("insert into test values(?, ?)");
    ps.setInt(1, 3);
    ps.setString(2, "v3");
    ps.addBatch();
    ps.setInt(1, 4);
    ps.setString(2, "v4");
    ps.addBatch();
    assertTrue(Arrays.equals(new int[] { 1, 1 }, ps.executeBatch()));
    ps.setInt(1, 5);
    ps.setString(2, "v5");
    ps.addBatch();
    ps.setInt(1, 6);
    ps.setString(2, "v6");
    ps.addBatch();
    assertTrue(Arrays.equals(new long[] { 1, 1 }, ((JdbcStatementBackwardsCompat) ps).executeLargeBatch()));
    ps.setInt(1, 7);
    ps.setString(2, "v7");
    assertEquals(1, ps.executeUpdate());
    assertEquals(1, ps.getUpdateCount());
    ps.setInt(1, 8);
    ps.setString(2, "v8");
    assertEquals(1, ((JdbcPreparedStatementBackwardsCompat) ps).executeLargeUpdate());
    assertEquals(1, ((JdbcStatementBackwardsCompat) ps).getLargeUpdateCount());
    stat.execute("drop table test");
}

Example 40 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class TestWeb method testWebApp.

private void testWebApp() throws Exception {
    Server server = new Server();
    server.setOut(new PrintStream(new ByteArrayOutputStream()));
    server.runTool("-web", "-webPort", "8182", "-properties", "null", "-tcp", "-tcpPort", "9101");
    try {
        String url = "http://localhost:8182";
        WebClient client;
        String result;
        client = new WebClient();
        result = client.get(url);
        client.readSessionId(result);
        client.get(url, "login.jsp");
        client.get(url, "adminSave.do");
        result = client.get(url, "index.do?language=de");
        result = client.get(url, "login.jsp");
        assertContains(result, "Einstellung");
        result = client.get(url, "index.do?language=en");
        result = client.get(url, "login.jsp");
        assertTrue(result.indexOf("Einstellung") < 0);
        result = client.get(url, "test.do?driver=abc" + "&url=jdbc:abc:mem: " + getTestName() + "&user=sa&password=sa&name=_test_");
        assertContains(result, "Exception");
        result = client.get(url, "test.do?driver=org.h2.Driver" + "&url=jdbc:h2:mem:" + getTestName() + "&user=sa&password=sa&name=_test_");
        assertTrue(result.indexOf("Exception") < 0);
        result = client.get(url, "login.do?driver=org.h2.Driver" + "&url=jdbc:h2:mem:" + getTestName() + "&user=sa&password=sa&name=_test_");
        result = client.get(url, "header.jsp");
        result = client.get(url, "query.do?sql=" + "create table test(id int primary key, name varchar);" + "insert into test values(1, 'Hello')");
        result = client.get(url, "query.do?sql=create sequence test_sequence");
        result = client.get(url, "query.do?sql=create schema test_schema");
        result = client.get(url, "query.do?sql=" + "create view test_view as select * from test");
        result = client.get(url, "tables.do");
        result = client.get(url, "query.jsp");
        result = client.get(url, "query.do?sql=select * from test");
        assertContains(result, "Hello");
        result = client.get(url, "query.do?sql=select * from test");
        result = client.get(url, "query.do?sql=@META select * from test");
        assertContains(result, "typeName");
        result = client.get(url, "query.do?sql=delete from test");
        result = client.get(url, "query.do?sql=@LOOP 1000 " + "insert into test values(?, 'Hello ' || ?/*RND*/)");
        assertContains(result, "1000 * (Prepared)");
        result = client.get(url, "query.do?sql=select * from test");
        result = client.get(url, "query.do?sql=@list select * from test");
        assertContains(result, "Row #");
        result = client.get(url, "query.do?sql=@parameter_meta " + "select * from test where id = ?");
        assertContains(result, "INTEGER");
        result = client.get(url, "query.do?sql=@edit select * from test");
        assertContains(result, "editResult.do");
        result = client.get(url, "query.do?sql=" + StringUtils.urlEncode("select space(100001) a, 1 b"));
        assertContains(result, "...");
        result = client.get(url, "query.do?sql=" + StringUtils.urlEncode("call '<&>'"));
        assertContains(result, "&lt;&amp;&gt;");
        result = client.get(url, "query.do?sql=@HISTORY");
        result = client.get(url, "getHistory.do?id=4");
        assertContains(result, "select * from test");
        result = client.get(url, "query.do?sql=delete from test");
        // op 1 (row -1: insert, otherwise update): ok,
        // 2: delete  3: cancel,
        result = client.get(url, "editResult.do?sql=@edit " + "select * from test&op=1&row=-1&r-1c1=1&r-1c2=Hello");
        assertContains(result, "1");
        assertContains(result, "Hello");
        result = client.get(url, "editResult.do?sql=@edit " + "select * from test&op=1&row=1&r1c1=1&r1c2=Hallo");
        assertContains(result, "1");
        assertContains(result, "Hallo");
        result = client.get(url, "query.do?sql=select * from test");
        assertContains(result, "1");
        assertContains(result, "Hallo");
        result = client.get(url, "editResult.do?sql=@edit " + "select * from test&op=2&row=1");
        result = client.get(url, "query.do?sql=select * from test");
        assertContains(result, "no rows");
        // autoComplete
        result = client.get(url, "autoCompleteList.do?query=select 'abc");
        assertContains(StringUtils.urlDecode(result), "'");
        result = client.get(url, "autoCompleteList.do?query=select 'abc''");
        assertContains(StringUtils.urlDecode(result), "'");
        result = client.get(url, "autoCompleteList.do?query=select 'abc' ");
        assertContains(StringUtils.urlDecode(result), "||");
        result = client.get(url, "autoCompleteList.do?query=select 'abc' |");
        assertContains(StringUtils.urlDecode(result), "|");
        result = client.get(url, "autoCompleteList.do?query=select 'abc' || ");
        assertContains(StringUtils.urlDecode(result), "'");
        result = client.get(url, "autoCompleteList.do?query=call timestamp '2");
        assertContains(result, "20");
        result = client.get(url, "autoCompleteList.do?query=call time '1");
        assertContains(StringUtils.urlDecode(result), "12:00:00");
        result = client.get(url, "autoCompleteList.do?query=" + "call timestamp '2001-01-01 12:00:00.");
        assertContains(result, "nanoseconds");
        result = client.get(url, "autoCompleteList.do?query=" + "call timestamp '2001-01-01 12:00:00.00");
        assertContains(result, "nanoseconds");
        result = client.get(url, "autoCompleteList.do?query=" + "call $$ hello world");
        assertContains(StringUtils.urlDecode(result), "$$");
        result = client.get(url, "autoCompleteList.do?query=alter index ");
        assertContains(StringUtils.urlDecode(result), "character");
        result = client.get(url, "autoCompleteList.do?query=alter index idx");
        assertContains(StringUtils.urlDecode(result), "character");
        result = client.get(url, "autoCompleteList.do?query=alter index \"IDX_");
        assertContains(StringUtils.urlDecode(result), "\"");
        result = client.get(url, "autoCompleteList.do?query=alter index \"IDX_\"\"");
        assertContains(StringUtils.urlDecode(result), "\"");
        result = client.get(url, "autoCompleteList.do?query=help ");
        assertContains(result, "anything");
        result = client.get(url, "autoCompleteList.do?query=help select");
        assertContains(result, "anything");
        result = client.get(url, "autoCompleteList.do?query=call ");
        assertContains(result, "0x");
        result = client.get(url, "autoCompleteList.do?query=call 0");
        assertContains(result, ".");
        result = client.get(url, "autoCompleteList.do?query=se");
        assertContains(result, "select");
        assertContains(result, "set");
        result = client.get(url, "tables.do");
        assertContains(result, "TEST");
        result = client.get(url, "autoCompleteList.do?query=" + "select * from ");
        assertContains(result, "test");
        result = client.get(url, "autoCompleteList.do?query=" + "select * from test t where t.");
        assertContains(result, "id");
        result = client.get(url, "autoCompleteList.do?query=" + "select id x from test te where t");
        assertContains(result, "te");
        result = client.get(url, "autoCompleteList.do?query=" + "select * from test where name = '");
        assertContains(StringUtils.urlDecode(result), "'");
        result = client.get(url, "autoCompleteList.do?query=" + "select * from information_schema.columns where columns.");
        assertContains(result, "column_name");
        result = client.get(url, "query.do?sql=delete from test");
        // special commands
        result = client.get(url, "query.do?sql=@autocommit_true");
        assertContains(result, "Auto commit is now ON");
        result = client.get(url, "query.do?sql=@autocommit_false");
        assertContains(result, "Auto commit is now OFF");
        result = client.get(url, "query.do?sql=@cancel");
        assertContains(result, "There is currently no running statement");
        result = client.get(url, "query.do?sql=@generated insert into test(id) values(test_sequence.nextval)");
        assertContains(result, "<tr><th>ID</th></tr><tr><td>1</td></tr>");
        result = client.get(url, "query.do?sql=@maxrows 2000");
        assertContains(result, "Max rowcount is set");
        result = client.get(url, "query.do?sql=@password_hash user password");
        assertContains(result, "501cf5c163c184c26e62e76d25d441979f8f25dfd7a683484995b4a43a112fdf");
        result = client.get(url, "query.do?sql=@sleep 1");
        assertContains(result, "Ok");
        result = client.get(url, "query.do?sql=@catalogs");
        assertContains(result, "PUBLIC");
        result = client.get(url, "query.do?sql=@column_privileges null null null TEST null");
        assertContains(result, "PRIVILEGE");
        result = client.get(url, "query.do?sql=@cross_references null null null TEST");
        assertContains(result, "PKTABLE_NAME");
        result = client.get(url, "query.do?sql=@exported_keys null null null TEST");
        assertContains(result, "PKTABLE_NAME");
        result = client.get(url, "query.do?sql=@imported_keys null null null TEST");
        assertContains(result, "PKTABLE_NAME");
        result = client.get(url, "query.do?sql=@primary_keys null null null TEST");
        assertContains(result, "PK_NAME");
        result = client.get(url, "query.do?sql=@procedures null null null");
        assertContains(result, "PROCEDURE_NAME");
        result = client.get(url, "query.do?sql=@procedure_columns");
        assertContains(result, "PROCEDURE_NAME");
        result = client.get(url, "query.do?sql=@schemas");
        assertContains(result, "PUBLIC");
        result = client.get(url, "query.do?sql=@table_privileges");
        assertContains(result, "PRIVILEGE");
        result = client.get(url, "query.do?sql=@table_types");
        assertContains(result, "SYSTEM TABLE");
        result = client.get(url, "query.do?sql=@type_info");
        assertContains(result, "CLOB");
        result = client.get(url, "query.do?sql=@version_columns");
        assertContains(result, "PSEUDO_COLUMN");
        result = client.get(url, "query.do?sql=@attributes");
        assertContains(result, "Feature not supported: &quot;attributes&quot;");
        result = client.get(url, "query.do?sql=@super_tables");
        assertContains(result, "SUPERTABLE_NAME");
        result = client.get(url, "query.do?sql=@super_types");
        assertContains(result, "Feature not supported: &quot;superTypes&quot;");
        result = client.get(url, "query.do?sql=@prof_start");
        assertContains(result, "Ok");
        result = client.get(url, "query.do?sql=@prof_stop");
        assertContains(result, "Top Stack Trace(s)");
        result = client.get(url, "query.do?sql=@best_row_identifier null null TEST");
        assertContains(result, "SCOPE");
        assertContains(result, "COLUMN_NAME");
        assertContains(result, "ID");
        result = client.get(url, "query.do?sql=@udts");
        assertContains(result, "CLASS_NAME");
        result = client.get(url, "query.do?sql=@udts null null null 1,2,3");
        assertContains(result, "CLASS_NAME");
        result = client.get(url, "query.do?sql=@LOOP 10 " + "@STATEMENT insert into test values(?, 'Hello')");
        result = client.get(url, "query.do?sql=select * from test");
        assertContains(result, "8");
        result = client.get(url, "query.do?sql=@EDIT select * from test");
        assertContains(result, "editRow");
        result = client.get(url, "query.do?sql=@AUTOCOMMIT TRUE");
        result = client.get(url, "query.do?sql=@AUTOCOMMIT FALSE");
        result = client.get(url, "query.do?sql=@TRANSACTION_ISOLATION");
        result = client.get(url, "query.do?sql=@SET MAXROWS 1");
        result = client.get(url, "query.do?sql=select * from test order by id");
        result = client.get(url, "query.do?sql=@SET MAXROWS 1000");
        result = client.get(url, "query.do?sql=@TABLES");
        assertContains(result, "TEST");
        result = client.get(url, "query.do?sql=@COLUMNS null null TEST");
        assertContains(result, "ID");
        result = client.get(url, "query.do?sql=@INDEX_INFO null null TEST");
        assertContains(result, "PRIMARY");
        result = client.get(url, "query.do?sql=@CATALOG");
        assertContains(result, "PUBLIC");
        result = client.get(url, "query.do?sql=@MEMORY");
        assertContains(result, "Used");
        result = client.get(url, "query.do?sql=@INFO");
        assertContains(result, "getCatalog");
        result = client.get(url, "logout.do");
        result = client.get(url, "login.do?driver=org.h2.Driver&" + "url=jdbc:h2:mem:" + getTestName() + "&user=sa&password=sa&name=_test_");
        result = client.get(url, "logout.do");
        result = client.get(url, "settingRemove.do?name=_test_");
        client.get(url, "admin.do");
    } finally {
        server.shutdown();
    }
}