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

Example 66 with Insert

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

the class JdbcResultSet method updateClob.

/**
 * Updates a column in the current or insert row.
 *
 * @param columnLabel the column label
 * @param x the value
 * @throws SQLException if the result set is closed or not updatable
 */
@Override
public void updateClob(String columnLabel, Clob x) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("updateClob(" + quote(columnLabel) + ", x);");
        }
        checkClosed();
        Value v;
        if (x == null) {
            v = ValueNull.INSTANCE;
        } else {
            v = conn.createClob(x.getCharacterStream(), -1);
        }
        update(columnLabel, v);
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 67 with Insert

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

the class JdbcDatabaseMetaData method getFunctions.

private String getFunctions(String section) throws SQLException {
    try {
        checkClosed();
        PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT TOPIC " + "FROM INFORMATION_SCHEMA.HELP WHERE SECTION = ?");
        prep.setString(1, section);
        ResultSet rs = prep.executeQuery();
        StatementBuilder buff = new StatementBuilder();
        while (rs.next()) {
            String s = rs.getString(1).trim();
            String[] array = StringUtils.arraySplit(s, ',', true);
            for (String a : array) {
                buff.appendExceptFirst(",");
                String f = a.trim();
                if (f.indexOf(' ') >= 0) {
                    // remove 'Function' from 'INSERT Function'
                    f = f.substring(0, f.indexOf(' ')).trim();
                }
                buff.append(f);
            }
        }
        rs.close();
        prep.close();
        return buff.toString();
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 68 with Insert

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

the class JdbcDatabaseMetaData method getTablePrivileges.

/**
 * Gets the list of table privileges. The result set is sorted by
 * TABLE_SCHEM, TABLE_NAME, and PRIVILEGE.
 *
 * <ol>
 * <li>TABLE_CAT (String) table catalog</li>
 * <li>TABLE_SCHEM (String) table schema</li>
 * <li>TABLE_NAME (String) table name</li>
 * <li>GRANTOR (String) grantor of access</li>
 * <li>GRANTEE (String) grantee of access</li>
 * <li>PRIVILEGE (String) SELECT, INSERT, UPDATE, DELETE or REFERENCES
 * (only one per row)</li>
 * <li>IS_GRANTABLE (String) YES means the grantee can grant access to
 * others</li>
 * </ol>
 *
 * @param catalogPattern null (to get all objects) or the catalog name
 * @param schemaPattern null (to get all objects) or a schema name
 *            (uppercase for unquoted names)
 * @param tableNamePattern null (to get all objects) or a table name
 *            (uppercase for unquoted names)
 * @return the list of privileges
 * @throws SQLException if the connection is closed
 */
@Override
public ResultSet getTablePrivileges(String catalogPattern, String schemaPattern, String tableNamePattern) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("getTablePrivileges(" + quote(catalogPattern) + ", " + quote(schemaPattern) + ", " + quote(tableNamePattern) + ");");
        }
        checkClosed();
        PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT " + "TABLE_CATALOG TABLE_CAT, " + "TABLE_SCHEMA TABLE_SCHEM, " + "TABLE_NAME, " + "GRANTOR, " + "GRANTEE, " + "PRIVILEGE_TYPE PRIVILEGE, " + "IS_GRANTABLE " + "FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES " + "WHERE TABLE_CATALOG LIKE ? ESCAPE ? " + "AND TABLE_SCHEMA LIKE ? ESCAPE ? " + "AND TABLE_NAME LIKE ? ESCAPE ? " + "ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE");
        prep.setString(1, getCatalogPattern(catalogPattern));
        prep.setString(2, "\\");
        prep.setString(3, getSchemaPattern(schemaPattern));
        prep.setString(4, "\\");
        prep.setString(5, getPattern(tableNamePattern));
        prep.setString(6, "\\");
        return prep.executeQuery();
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 69 with Insert

use of org.h2.command.dml.Insert 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 70 with Insert

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

the class TestStatement method testIdentity.

private void testIdentity() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE SEQUENCE SEQ");
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());
    stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
    rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(2, rs.getInt(1));
    assertFalse(rs.next());
    stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[] { 1 });
    rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(3, rs.getInt(1));
    assertFalse(rs.next());
    stat.execute("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String[] { "ID" });
    rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(4, rs.getInt(1));
    assertFalse(rs.next());
    stat.executeUpdate("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
    rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(5, rs.getInt(1));
    assertFalse(rs.next());
    stat.executeUpdate("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[] { 1 });
    rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(6, rs.getInt(1));
    assertFalse(rs.next());
    stat.executeUpdate("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String[] { "ID" });
    rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(7, rs.getInt(1));
    assertFalse(rs.next());
    stat.execute("CREATE TABLE TEST2(ID identity primary key)");
    stat.execute("INSERT INTO TEST2 VALUES()");
    stat.execute("SET @X = IDENTITY()");
    rs = stat.executeQuery("SELECT @X");
    rs.next();
    assertEquals(1, rs.getInt(1));
    stat.execute("DROP TABLE TEST");
    stat.execute("DROP TABLE TEST2");
}