Examples with Update org.h2.command.dml.Update used on opensource projects

Example 91 with Update

use of org.h2.command.dml.Update 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();
    }
}

Example 92 with Update

use of org.h2.command.dml.Update in project h2database by h2database.

the class TestTriggersConstraints method testTriggers.

private void testTriggers() throws SQLException {
    mustNotCallTrigger = false;
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
    // CREATE TRIGGER trigger {BEFORE|AFTER}
    // {INSERT|UPDATE|DELETE|ROLLBACK} ON table
    // [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL triggeredClass
    stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE " + "BEFORE INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE " + "BEFORE INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER INS_AFTER " + "" + "AFTER INSERT ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER UPD_BEFORE " + "BEFORE UPDATE ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("CREATE TRIGGER INS_AFTER_ROLLBACK " + "AFTER INSERT, ROLLBACK ON TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\"");
    stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
    ResultSet rs;
    rs = stat.executeQuery("SCRIPT");
    checkRows(rs, new String[] { "CREATE FORCE TRIGGER PUBLIC.INS_BEFORE " + "BEFORE INSERT ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.INS_AFTER " + "AFTER INSERT ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.UPD_BEFORE " + "BEFORE UPDATE ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";", "CREATE FORCE TRIGGER PUBLIC.INS_AFTER_ROLLBACK " + "AFTER INSERT, ROLLBACK ON PUBLIC.TEST " + "FOR EACH ROW NOWAIT CALL \"" + getClass().getName() + "\";" });
    while (rs.next()) {
        String sql = rs.getString(1);
        if (sql.startsWith("CREATE TRIGGER")) {
            System.out.println(sql);
        }
    }
    rs = stat.executeQuery("SELECT * FROM TEST");
    rs.next();
    assertEquals("Hello-updated", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("UPDATE TEST SET NAME=NAME||'-upd'");
    rs = stat.executeQuery("SELECT * FROM TEST");
    rs.next();
    assertEquals("Hello-updated-upd-updated2", rs.getString(2));
    assertFalse(rs.next());
    mustNotCallTrigger = true;
    stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
    stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
    stat.execute("DROP TRIGGER IF EXISTS INS_AFTER_ROLLBACK");
    assertThrows(ErrorCode.TRIGGER_NOT_FOUND_1, stat).execute("DROP TRIGGER INS_BEFORE");
    stat.execute("DROP TRIGGER  INS_AFTER");
    stat.execute("DROP TRIGGER  UPD_BEFORE");
    stat.execute("UPDATE TEST SET NAME=NAME||'-upd-no_trigger'");
    stat.execute("INSERT INTO TEST VALUES(100, 'Insert-no_trigger')");
    conn.close();
    conn = getConnection("trigger");
    mustNotCallTrigger = false;
    conn.close();
}

Example 93 with Update

use of org.h2.command.dml.Update in project h2database by h2database.

the class TestTriggersConstraints method testDeleteInTrigger.

private void testDeleteInTrigger() throws SQLException {
    if (config.mvcc || config.mvStore) {
        return;
    }
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("create table test(id int) as select 1");
    stat.execute("create trigger test_u before update on test " + "for each row call \"" + DeleteTrigger.class.getName() + "\"");
    // this threw a NullPointerException
    assertThrows(ErrorCode.ROW_NOT_FOUND_WHEN_DELETING_1, stat).execute("update test set id = 2");
    stat.execute("drop table test");
    conn.close();
}

Example 94 with Update

use of org.h2.command.dml.Update in project h2database by h2database.

the class TestIndex method testHashIndex.

private void testHashIndex() throws SQLException {
    reconnect();
    stat.execute("create table testA(id int primary key, name varchar)");
    stat.execute("create table testB(id int primary key hash, name varchar)");
    int len = getSize(300, 3000);
    stat.execute("insert into testA select x, 'Hello' from " + "system_range(1, " + len + ")");
    stat.execute("insert into testB select x, 'Hello' from " + "system_range(1, " + len + ")");
    Random rand = new Random(1);
    for (int i = 0; i < len; i++) {
        int x = rand.nextInt(len);
        String sql = "";
        switch(rand.nextInt(3)) {
            case 0:
                sql = "delete from testA where id = " + x;
                break;
            case 1:
                sql = "update testA set name = " + rand.nextInt(100) + " where id = " + x;
                break;
            case 2:
                sql = "select name from testA where id = " + x;
                break;
            default:
        }
        boolean result = stat.execute(sql);
        if (result) {
            ResultSet rs = stat.getResultSet();
            String s1 = rs.next() ? rs.getString(1) : null;
            rs = stat.executeQuery(sql.replace('A', 'B'));
            String s2 = rs.next() ? rs.getString(1) : null;
            assertEquals(s1, s2);
        } else {
            int count1 = stat.getUpdateCount();
            int count2 = stat.executeUpdate(sql.replace('A', 'B'));
            assertEquals(count1, count2);
        }
    }
    stat.execute("drop table testA, testB");
    conn.close();
}

Example 95 with Update

use of org.h2.command.dml.Update in project h2database by h2database.

the class TestLob method testUpdatingLobRow.

/**
 * Test the combination of updating a table which contains an LOB, and
 * reading from the LOB at the same time
 */
private void testUpdatingLobRow() throws Exception {
    if (config.memory) {
        return;
    }
    deleteDb("lob");
    Connection conn = getConnection("lob");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, " + "name clob, counter int)");
    stat.execute("insert into test(id, name) select x, " + "space(100000) from system_range(1, 3)");
    ResultSet rs = stat.executeQuery("select name " + "from test where id = 1");
    rs.next();
    Reader r = rs.getClob("name").getCharacterStream();
    Random random = new Random();
    char[] tmp = new char[256];
    while (r.read(tmp) > 0) {
        stat.execute("update test set counter = " + random.nextInt(1000) + " where id = 1");
    }
    r.close();
    conn.close();
}