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

Example 26 with Insert

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

the class TestTools method testBackupRestore.

private void testBackupRestore() throws SQLException {
    org.h2.Driver.load();
    String url = "jdbc:h2:" + getBaseDir() + "/testBackupRestore";
    String user = "sa", password = "abc";
    final String fileName = getBaseDir() + "/b2.zip";
    DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testBackupRestore", "-quiet");
    Connection conn = getConnection(url, user, password);
    conn.createStatement().execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
    conn.createStatement().execute("INSERT INTO TEST VALUES(1, 'Hello')");
    conn.close();
    Backup.main("-file", fileName, "-dir", getBaseDir(), "-db", "testBackupRestore", "-quiet");
    DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testBackupRestore", "-quiet");
    Restore.main("-file", fileName, "-dir", getBaseDir(), "-db", "testBackupRestore", "-quiet");
    conn = getConnection("jdbc:h2:" + getBaseDir() + "/testBackupRestore", "sa", "abc");
    ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
    assertTrue(rs.next());
    assertFalse(rs.next());
    new AssertThrows(ErrorCode.CANNOT_CHANGE_SETTING_WHEN_OPEN_1) {

        @Override
        public void test() throws SQLException {
            // must fail when the database is in use
            Backup.main("-file", fileName, "-dir", getBaseDir(), "-db", "testBackupRestore");
        }
    };
    conn.close();
    DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testBackupRestore", "-quiet");
}

Example 27 with Insert

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

the class TestTools method testScriptRunscript.

private void testScriptRunscript() throws SQLException {
    org.h2.Driver.load();
    String url = getURL("jdbc:h2:" + getBaseDir() + "/testScriptRunscript", true);
    String user = "sa", password = "abc";
    String fileName = getBaseDir() + "/b2.sql";
    DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testScriptRunscript", "-quiet");
    Connection conn = getConnection(url, user, password);
    conn.createStatement().execute("CREATE TABLE \u00f6()");
    conn.createStatement().execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
    conn.createStatement().execute("INSERT INTO TEST VALUES(1, 'Hello')");
    conn.close();
    Script.main("-url", url, "-user", user, "-password", password, "-script", fileName, "-options", "nodata", "compression", "lzf", "cipher", "aes", "password", "'123'", "charset", "'utf-8'");
    Script.main("-url", url, "-user", user, "-password", password, "-script", fileName + ".txt");
    DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testScriptRunscript", "-quiet");
    RunScript.main("-url", url, "-user", user, "-password", password, "-script", fileName, "-options", "compression", "lzf", "cipher", "aes", "password", "'123'", "charset", "'utf-8'");
    conn = getConnection("jdbc:h2:" + getBaseDir() + "/testScriptRunscript", "sa", "abc");
    ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
    assertFalse(rs.next());
    rs = conn.createStatement().executeQuery("SELECT * FROM \u00f6");
    assertFalse(rs.next());
    conn.close();
    DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testScriptRunscript", "-quiet");
    RunScript tool = new RunScript();
    ByteArrayOutputStream buff = new ByteArrayOutputStream();
    tool.setOut(new PrintStream(buff));
    tool.runTool("-url", url, "-user", user, "-password", password, "-script", fileName + ".txt", "-showResults");
    assertContains(buff.toString(), "Hello");
    // test parsing of BLOCKSIZE option
    DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testScriptRunscript", "-quiet");
    conn = getConnection(url, user, password);
    conn.createStatement().execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
    conn.close();
    Script.main("-url", url, "-user", user, "-password", password, "-script", fileName, "-options", "simple", "blocksize", "8192");
}

Example 28 with Insert

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

the class TestTools method testRemove.

private void testRemove() throws SQLException {
    if (config.mvStore) {
        return;
    }
    deleteDb("toolsRemove");
    org.h2.Driver.load();
    String url = "jdbc:h2:" + getBaseDir() + "/toolsRemove";
    Connection conn = getConnection(url, "sa", "sa");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name varchar)");
    stat.execute("insert into test values(1, 'Hello')");
    conn.close();
    Recover.main("-dir", getBaseDir(), "-db", "toolsRemove", "-removePassword");
    conn = getConnection(url, "sa", "");
    stat = conn.createStatement();
    ResultSet rs;
    rs = stat.executeQuery("select * from test");
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    conn.close();
    deleteDb("toolsRemove");
    FileUtils.delete(getBaseDir() + "/toolsRemove.h2.sql");
}

Example 29 with Insert

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

the class TestTools method testScriptRunscriptLob.

private void testScriptRunscriptLob() throws Exception {
    org.h2.Driver.load();
    String url = getURL("jdbc:h2:" + getBaseDir() + "/testScriptRunscriptLob", true);
    String user = "sa", password = "abc";
    String fileName = getBaseDir() + "/b2.sql";
    Connection conn = getConnection(url, user, password);
    conn.createStatement().execute("CREATE TABLE TEST(ID INT PRIMARY KEY, BDATA BLOB, CDATA CLOB)");
    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
    prep.setInt(1, 1);
    prep.setNull(2, Types.BLOB);
    prep.setNull(3, Types.CLOB);
    prep.execute();
    prep.setInt(1, 2);
    prep.setString(2, "face");
    prep.setString(3, "face");
    prep.execute();
    Random random = new Random(1);
    prep.setInt(1, 3);
    byte[] large = new byte[getSize(10 * 1024, 100 * 1024)];
    random.nextBytes(large);
    prep.setBytes(2, large);
    String largeText = new String(large, StandardCharsets.ISO_8859_1);
    prep.setString(3, largeText);
    prep.execute();
    for (int i = 0; i < 2; i++) {
        ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TEST ORDER BY ID");
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertNull(rs.getString(2));
        assertNull(rs.getString(3));
        rs.next();
        assertEquals(2, rs.getInt(1));
        assertEquals("face", rs.getString(2));
        assertEquals("face", rs.getString(3));
        rs.next();
        assertEquals(3, rs.getInt(1));
        assertEquals(large, rs.getBytes(2));
        assertEquals(largeText, rs.getString(3));
        assertFalse(rs.next());
        conn.close();
        Script.main("-url", url, "-user", user, "-password", password, "-script", fileName);
        DeleteDbFiles.main("-dir", getBaseDir(), "-db", "testScriptRunscriptLob", "-quiet");
        RunScript.main("-url", url, "-user", user, "-password", password, "-script", fileName);
        conn = getConnection("jdbc:h2:" + getBaseDir() + "/testScriptRunscriptLob", "sa", "abc");
    }
    conn.close();
}

Example 30 with Insert

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