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

Example 81 with Update

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

the class JdbcDatabaseMetaData method getVersionColumns.

/**
 * Get the list of columns that are update when any value is updated.
 * The result set is always empty.
 *
 * <ol>
 * <li>1 SCOPE (int) not used</li>
 * <li>2 COLUMN_NAME (String) column name</li>
 * <li>3 DATA_TYPE (int) SQL data type - see also java.sql.Types</li>
 * <li>4 TYPE_NAME (String) data type name</li>
 * <li>5 COLUMN_SIZE (int) precision
 *         (values larger than 2 GB are returned as 2 GB)</li>
 * <li>6 BUFFER_LENGTH (int) length (bytes)</li>
 * <li>7 DECIMAL_DIGITS (int) scale</li>
 * <li>8 PSEUDO_COLUMN (int) is this column a pseudo column</li>
 * </ol>
 *
 * @param catalog null (to get all objects) or the catalog name
 * @param schema null (to get all objects) or a schema name
 * @param tableName table name (must be specified)
 * @return an empty result set
 * @throws SQLException if the connection is closed
 */
@Override
public ResultSet getVersionColumns(String catalog, String schema, String tableName) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("getVersionColumns(" + quote(catalog) + ", " + quote(schema) + ", " + quote(tableName) + ");");
        }
        checkClosed();
        PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT " + "ZERO() SCOPE, " + "COLUMN_NAME, " + "CAST(DATA_TYPE AS INT) DATA_TYPE, " + "TYPE_NAME, " + "NUMERIC_PRECISION COLUMN_SIZE, " + "NUMERIC_PRECISION BUFFER_LENGTH, " + "NUMERIC_PRECISION DECIMAL_DIGITS, " + "ZERO() PSEUDO_COLUMN " + "FROM INFORMATION_SCHEMA.COLUMNS " + "WHERE FALSE");
        return prep.executeQuery();
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 82 with Update

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

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

the class JdbcBlob method setBinaryStream.

/**
 * Get a writer to update the Blob. This is only supported for new, empty
 * Blob objects that were created with Connection.createBlob(). The Blob is
 * created in a separate thread, and the object is only updated when
 * OutputStream.close() is called. The position must be 1, meaning the whole
 * Blob data is set.
 *
 * @param pos where to start writing (the first byte is at position 1)
 * @return an output stream
 */
@Override
public OutputStream setBinaryStream(long pos) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("setBinaryStream(" + pos + ");");
        }
        checkClosed();
        if (pos != 1) {
            throw DbException.getInvalidValueException("pos", pos);
        }
        if (value.getPrecision() != 0) {
            throw DbException.getInvalidValueException("length", value.getPrecision());
        }
        // local variable avoids generating synthetic accessor method
        final JdbcConnection c = conn;
        final PipedInputStream in = new PipedInputStream();
        final Task task = new Task() {

            @Override
            public void call() {
                value = c.createBlob(in, -1);
            }
        };
        PipedOutputStream out = new PipedOutputStream(in) {

            @Override
            public void close() throws IOException {
                super.close();
                try {
                    task.get();
                } catch (Exception e) {
                    throw DbException.convertToIOException(e);
                }
            }
        };
        task.execute();
        return new BufferedOutputStream(out);
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 84 with Update

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

the class JdbcClob method setCharacterStream.

/**
 * Get a writer to update the Clob. This is only supported for new, empty
 * Clob objects that were created with Connection.createClob() or
 * createNClob(). The Clob is created in a separate thread, and the object
 * is only updated when Writer.close() is called. The position must be 1,
 * meaning the whole Clob data is set.
 *
 * @param pos where to start writing (the first character is at position 1)
 * @return a writer
 */
@Override
public Writer setCharacterStream(long pos) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCodeCall("setCharacterStream(" + pos + ");");
        }
        checkClosed();
        if (pos != 1) {
            throw DbException.getInvalidValueException("pos", pos);
        }
        if (value.getPrecision() != 0) {
            throw DbException.getInvalidValueException("length", value.getPrecision());
        }
        // required to avoid synthetic method creation
        final JdbcConnection c = conn;
        // PipedReader / PipedWriter are a lot slower
        // than PipedInputStream / PipedOutputStream
        // (Sun/Oracle Java 1.6.0_20)
        final PipedInputStream in = new PipedInputStream();
        final Task task = new Task() {

            @Override
            public void call() {
                value = c.createClob(IOUtils.getReader(in), -1);
            }
        };
        PipedOutputStream out = new PipedOutputStream(in) {

            @Override
            public void close() throws IOException {
                super.close();
                try {
                    task.get();
                } catch (Exception e) {
                    throw DbException.convertToIOException(e);
                }
            }
        };
        task.execute();
        return IOUtils.getBufferedWriter(out);
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 85 with Update

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