com.mysql.cj.jdbc.ClientPreparedStatement Java Examples

The following examples show how to use com.mysql.cj.jdbc.ClientPreparedStatement. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: UtilsTest.java    From FoxTelem with GNU General Public License v3.0 6 votes vote down vote up
/**
 * Tests Util.isJdbcInterface()
 * 
 * @throws Exception
 */
public void testIsJdbcInterface() throws Exception {
    // Classes directly or indirectly implementing JDBC interfaces.
    assertTrue(Util.isJdbcInterface(ClientPreparedStatement.class));
    assertTrue(Util.isJdbcInterface(StatementImpl.class));
    assertTrue(Util.isJdbcInterface(JdbcStatement.class));
    assertTrue(Util.isJdbcInterface(ResultSetImpl.class));
    JdbcStatement s = (JdbcStatement) Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class<?>[] { JdbcStatement.class },
            new InvocationHandler() {
                public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                    return null;
                }
            });
    assertTrue(Util.isJdbcInterface(s.getClass()));

    // Classes not implementing JDBC interfaces.
    assertFalse(Util.isJdbcInterface(Util.class));
    assertFalse(Util.isJdbcInterface(UtilsTest.class));

}
 
Example #2
Source File: UpdatableResultSet.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
/**
 * Reset UPDATE prepared statement to value in current row. This_Row MUST
 * point to current, valid row.
 * 
 * @throws SQLException
 */
protected void syncUpdate() throws SQLException {
    if (this.updater == null) {
        if (this.updateSQL == null) {
            generateStatements();
        }

        this.updater = (ClientPreparedStatement) this.getConnection().clientPrepareStatement(this.updateSQL);
    }

    Field[] fields = this.getMetadata().getFields();
    int numFields = fields.length;
    this.updater.clearParameters();

    for (int i = 0; i < numFields; i++) {
        if (this.thisRow.getBytes(i) != null) {
            this.updater.setObject(i + 1, getObject(i + 1), fields[i].getMysqlType());
        } else {
            this.updater.setNull(i + 1, 0);
        }
    }

    int numKeys = this.primaryKeyIndicies.size();

    if (numKeys == 1) {
        int index = this.primaryKeyIndicies.get(0).intValue();
        this.setParamValue(this.updater, numFields + 1, this.thisRow, index, fields[index].getMysqlType());
    } else {
        for (int i = 0; i < numKeys; i++) {
            int idx = this.primaryKeyIndicies.get(i).intValue();
            this.setParamValue(this.updater, numFields + i + 1, this.thisRow, idx, fields[idx].getMysqlType());
        }
    }
}
 
Example #3
Source File: ConnectionTest.java    From FoxTelem with GNU General Public License v3.0 5 votes vote down vote up
@Override
public <T extends Resultset> T preProcess(Supplier<String> str, Query interceptedQuery) {
    String sql = str == null ? null : str.get();
    if (sql == null) {
        try {
            if (interceptedQuery instanceof ClientPreparedStatement) {
                sql = ((ClientPreparedStatement) interceptedQuery).asSql();
            } else if (interceptedQuery instanceof PreparedQuery<?>) {
                sql = ((PreparedQuery<?>) interceptedQuery).asSql();
            }
        } catch (SQLException ex) {
            throw ExceptionFactory.createException(ex.getMessage(), ex);
        }
    }

    int p;
    if (sql != null && (p = sql.indexOf("testEnableEscapeProcessing:")) != -1) {
        int tst = Integer.parseInt(sql.substring(sql.indexOf('(', p) + 1, sql.indexOf(')', p)));
        boolean enableEscapeProcessing = (tst & 0x1) != 0;
        boolean processEscapeCodesForPrepStmts = (tst & 0x2) != 0;
        boolean useServerPrepStmts = (tst & 0x4) != 0;
        boolean isPreparedStatement = interceptedQuery instanceof PreparedStatement || interceptedQuery instanceof PreparedQuery<?>;

        String testCase = String.format("Case: %d [ %s | %s | %s ]/%s", tst, enableEscapeProcessing ? "enEscProc" : "-",
                processEscapeCodesForPrepStmts ? "procEscProcPS" : "-", useServerPrepStmts ? "useSSPS" : "-",
                isPreparedStatement ? "PreparedStatement" : "Statement");

        boolean escapeProcessingDone = sql.indexOf('{') == -1;
        assertTrue(testCase, isPreparedStatement && processEscapeCodesForPrepStmts == escapeProcessingDone
                || !isPreparedStatement && enableEscapeProcessing == escapeProcessingDone);
    }
    final String fsql = sql;
    return super.preProcess(() -> {
        return fsql;
    }, interceptedQuery);
}
 
Example #4
Source File: UpdatableResultSet.java    From lams with GNU General Public License v2.0 4 votes vote down vote up
private void setParamValue(ClientPreparedStatement ps, int psIdx, Row row, int rsIdx, MysqlType mysqlType) throws SQLException {
    byte[] val = row.getBytes(rsIdx);
    if (val == null) {
        ps.setNull(psIdx, MysqlType.NULL);
        return;
    }
    switch (mysqlType) {
        case NULL:
            ps.setNull(psIdx, MysqlType.NULL);
            break;
        case TINYINT:
        case TINYINT_UNSIGNED:
        case SMALLINT:
        case SMALLINT_UNSIGNED:
        case MEDIUMINT:
        case MEDIUMINT_UNSIGNED:
        case INT:
        case INT_UNSIGNED:
        case YEAR:
            ps.setInt(psIdx, getInt(rsIdx + 1));
            break;
        case BIGINT:
            ps.setLong(psIdx, getLong(rsIdx + 1));
            break;
        case BIGINT_UNSIGNED:
            ps.setBigInteger(psIdx, getBigInteger(rsIdx + 1));
            break;
        case CHAR:
        case ENUM:
        case SET:
        case VARCHAR:
        case JSON:
        case TINYTEXT:
        case TEXT:
        case MEDIUMTEXT:
        case LONGTEXT:
        case DECIMAL:
        case DECIMAL_UNSIGNED:
            ps.setString(psIdx, getString(rsIdx + 1));
            break;
        case DATE:
            ps.setDate(psIdx, getDate(rsIdx + 1));
            break;
        case TIMESTAMP:
        case DATETIME:
            ps.setTimestamp(psIdx, getTimestamp(rsIdx + 1));
            break;
        case TIME:
            ps.setTime(psIdx, getTime(rsIdx + 1));
            break;
        case DOUBLE:
        case DOUBLE_UNSIGNED:
        case FLOAT:
        case FLOAT_UNSIGNED:
        case BOOLEAN:
        case BIT:
            ps.setBytesNoEscapeNoQuotes(psIdx, val);
            break;
        /*
         * default, but also explicitly for following types:
         * case Types.BINARY:
         * case Types.BLOB:
         */
        default:
            ps.setBytes(psIdx, val);
            break;
    }

}
 
Example #5
Source File: UpdatableResultSet.java    From lams with GNU General Public License v2.0 4 votes vote down vote up
@Override
public void moveToInsertRow() throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        if (!this.isUpdatable) {
            throw new NotUpdatable(this.notUpdatableReason);
        }

        if (this.inserter == null) {
            if (this.insertSQL == null) {
                generateStatements();
            }

            this.inserter = (ClientPreparedStatement) this.getConnection().clientPrepareStatement(this.insertSQL);
            if (this.populateInserterWithDefaultValues) {
                extractDefaultValues();
            }

            resetInserter();
        } else {
            resetInserter();
        }

        Field[] fields = this.getMetadata().getFields();
        int numFields = fields.length;

        this.onInsertRow = true;
        this.doingUpdates = false;
        this.savedCurrentRow = this.thisRow;
        byte[][] newRowData = new byte[numFields][];
        this.thisRow = new ByteArrayRow(newRowData, getExceptionInterceptor());
        this.thisRow.setMetadata(this.getMetadata());

        for (int i = 0; i < numFields; i++) {
            if (!this.populateInserterWithDefaultValues) {
                this.inserter.setBytesNoEscapeNoQuotes(i + 1, StringUtils.getBytes("DEFAULT"));
                newRowData = null;
            } else {
                if (this.defaultColumnValue[i] != null) {
                    Field f = fields[i];

                    switch (f.getMysqlTypeId()) {
                        case MysqlType.FIELD_TYPE_DATE:
                        case MysqlType.FIELD_TYPE_DATETIME:
                        case MysqlType.FIELD_TYPE_TIME:
                        case MysqlType.FIELD_TYPE_TIMESTAMP:

                            if (this.defaultColumnValue[i].length > 7 && this.defaultColumnValue[i][0] == (byte) 'C'
                                    && this.defaultColumnValue[i][1] == (byte) 'U' && this.defaultColumnValue[i][2] == (byte) 'R'
                                    && this.defaultColumnValue[i][3] == (byte) 'R' && this.defaultColumnValue[i][4] == (byte) 'E'
                                    && this.defaultColumnValue[i][5] == (byte) 'N' && this.defaultColumnValue[i][6] == (byte) 'T'
                                    && this.defaultColumnValue[i][7] == (byte) '_') {
                                this.inserter.setBytesNoEscapeNoQuotes(i + 1, this.defaultColumnValue[i]);

                            } else {
                                this.inserter.setBytes(i + 1, this.defaultColumnValue[i], false, false);
                            }
                            break;

                        default:
                            this.inserter.setBytes(i + 1, this.defaultColumnValue[i], false, false);
                    }

                    // This value _could_ be changed from a getBytes(), so we need a copy....
                    byte[] defaultValueCopy = new byte[this.defaultColumnValue[i].length];
                    System.arraycopy(this.defaultColumnValue[i], 0, defaultValueCopy, 0, defaultValueCopy.length);
                    newRowData[i] = defaultValueCopy;
                } else {
                    this.inserter.setNull(i + 1, MysqlType.NULL);
                    newRowData[i] = null;
                }
            }
        }
    }
}
 
Example #6
Source File: UpdatableResultSet.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
@Override
public void deleteRow() throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        if (!this.isUpdatable) {
            throw new NotUpdatable(this.notUpdatableReason);
        }

        if (this.onInsertRow) {
            throw SQLError.createSQLException(Messages.getString("UpdatableResultSet.1"), getExceptionInterceptor());
        } else if (this.rowData.size() == 0) {
            throw SQLError.createSQLException(Messages.getString("UpdatableResultSet.2"), getExceptionInterceptor());
        } else if (isBeforeFirst()) {
            throw SQLError.createSQLException(Messages.getString("UpdatableResultSet.3"), getExceptionInterceptor());
        } else if (isAfterLast()) {
            throw SQLError.createSQLException(Messages.getString("UpdatableResultSet.4"), getExceptionInterceptor());
        }

        if (this.deleter == null) {
            if (this.deleteSQL == null) {
                generateStatements();
            }

            this.deleter = (ClientPreparedStatement) this.connection.clientPrepareStatement(this.deleteSQL);
        }

        this.deleter.clearParameters();

        int numKeys = this.primaryKeyIndicies.size();
        for (int i = 0; i < numKeys; i++) {
            int index = this.primaryKeyIndicies.get(i).intValue();
            this.setParamValue(this.deleter, i + 1, this.thisRow, index, this.getMetadata().getFields()[index]);

        }

        this.deleter.executeUpdate();
        this.rowData.remove();

        // position on previous row - Bug#27431
        previous();
    }
}
 
Example #7
Source File: UpdatableResultSet.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
private void setParamValue(ClientPreparedStatement ps, int psIdx, Row row, int rsIdx, Field field) throws SQLException {
    byte[] val = row.getBytes(rsIdx);
    if (val == null) {
        ps.setNull(psIdx, MysqlType.NULL);
        return;
    }
    switch (field.getMysqlType()) {
        case NULL:
            ps.setNull(psIdx, MysqlType.NULL);
            break;
        case TINYINT:
        case TINYINT_UNSIGNED:
        case SMALLINT:
        case SMALLINT_UNSIGNED:
        case MEDIUMINT:
        case MEDIUMINT_UNSIGNED:
        case INT:
        case INT_UNSIGNED:
        case YEAR:
            ps.setInt(psIdx, getInt(rsIdx + 1));
            break;
        case BIGINT:
            ps.setLong(psIdx, getLong(rsIdx + 1));
            break;
        case BIGINT_UNSIGNED:
            ps.setBigInteger(psIdx, getBigInteger(rsIdx + 1));
            break;
        case CHAR:
        case ENUM:
        case SET:
        case VARCHAR:
        case JSON:
        case TINYTEXT:
        case TEXT:
        case MEDIUMTEXT:
        case LONGTEXT:
        case DECIMAL:
        case DECIMAL_UNSIGNED:
            ps.setString(psIdx, getString(rsIdx + 1));
            break;
        case DATE:
            ps.setDate(psIdx, getDate(rsIdx + 1));
            break;
        case TIMESTAMP:
        case DATETIME:
            ps.setTimestamp(psIdx, getTimestamp(rsIdx + 1), null, field.getDecimals());
            break;
        case TIME:
            // TODO adjust nanos to decimal numbers
            ps.setTime(psIdx, getTime(rsIdx + 1));
            break;
        case DOUBLE:
        case DOUBLE_UNSIGNED:
        case FLOAT:
        case FLOAT_UNSIGNED:
        case BOOLEAN:
        case BIT:
            ps.setBytesNoEscapeNoQuotes(psIdx, val);
            break;
        /*
         * default, but also explicitly for following types:
         * case Types.BINARY:
         * case Types.BLOB:
         */
        default:
            ps.setBytes(psIdx, val);
            break;
    }

}
 
Example #8
Source File: UpdatableResultSet.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
@Override
public void moveToInsertRow() throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        if (!this.isUpdatable) {
            throw new NotUpdatable(this.notUpdatableReason);
        }

        if (this.inserter == null) {
            if (this.insertSQL == null) {
                generateStatements();
            }

            this.inserter = (ClientPreparedStatement) this.getConnection().clientPrepareStatement(this.insertSQL);
            this.inserter.getQueryBindings().setColumnDefinition(this.getMetadata());

            if (this.populateInserterWithDefaultValues) {
                extractDefaultValues();
            }

            resetInserter();
        } else {
            resetInserter();
        }

        Field[] fields = this.getMetadata().getFields();
        int numFields = fields.length;

        this.onInsertRow = true;
        this.doingUpdates = false;
        this.savedCurrentRow = this.thisRow;
        byte[][] newRowData = new byte[numFields][];
        this.thisRow = new ByteArrayRow(newRowData, getExceptionInterceptor());
        this.thisRow.setMetadata(this.getMetadata());

        for (int i = 0; i < numFields; i++) {
            if (!this.populateInserterWithDefaultValues) {
                this.inserter.setBytesNoEscapeNoQuotes(i + 1, StringUtils.getBytes("DEFAULT"));
                newRowData = null;
            } else {
                if (this.defaultColumnValue[i] != null) {
                    Field f = fields[i];

                    switch (f.getMysqlTypeId()) {
                        case MysqlType.FIELD_TYPE_DATE:
                        case MysqlType.FIELD_TYPE_DATETIME:
                        case MysqlType.FIELD_TYPE_TIME:
                        case MysqlType.FIELD_TYPE_TIMESTAMP:

                            if (this.defaultColumnValue[i].length > 7 && this.defaultColumnValue[i][0] == (byte) 'C'
                                    && this.defaultColumnValue[i][1] == (byte) 'U' && this.defaultColumnValue[i][2] == (byte) 'R'
                                    && this.defaultColumnValue[i][3] == (byte) 'R' && this.defaultColumnValue[i][4] == (byte) 'E'
                                    && this.defaultColumnValue[i][5] == (byte) 'N' && this.defaultColumnValue[i][6] == (byte) 'T'
                                    && this.defaultColumnValue[i][7] == (byte) '_') {
                                this.inserter.setBytesNoEscapeNoQuotes(i + 1, this.defaultColumnValue[i]);

                            } else {
                                this.inserter.setBytes(i + 1, this.defaultColumnValue[i], false, false);
                            }
                            break;

                        default:
                            this.inserter.setBytes(i + 1, this.defaultColumnValue[i], false, false);
                    }

                    // This value _could_ be changed from a getBytes(), so we need a copy....
                    byte[] defaultValueCopy = new byte[this.defaultColumnValue[i].length];
                    System.arraycopy(this.defaultColumnValue[i], 0, defaultValueCopy, 0, defaultValueCopy.length);
                    newRowData[i] = defaultValueCopy;
                } else {
                    this.inserter.setNull(i + 1, MysqlType.NULL);
                    newRowData[i] = null;
                }
            }
        }
    }
}
 
Example #9
Source File: UpdatableResultSet.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
/**
 * Reset UPDATE prepared statement to value in current row. This_Row MUST
 * point to current, valid row.
 * 
 * @throws SQLException
 *             if an error occurs
 */
protected void syncUpdate() throws SQLException {
    if (this.updater == null) {
        if (this.updateSQL == null) {
            generateStatements();
        }

        this.updater = (ClientPreparedStatement) this.getConnection().clientPrepareStatement(this.updateSQL);
        this.updater.getQueryBindings().setColumnDefinition(this.getMetadata());
    }

    Field[] fields = this.getMetadata().getFields();
    int numFields = fields.length;
    this.updater.clearParameters();

    for (int i = 0; i < numFields; i++) {
        if (this.thisRow.getBytes(i) != null) {
            switch (fields[i].getMysqlType()) {
                case DATE:
                case DATETIME:
                case TIME:
                case TIMESTAMP:
                    // TODO this is a temporary workaround until Bug#71143 "Calling ResultSet.updateRow should not set all field values in UPDATE" is fixed.
                    // We handle these types separately to avoid fractional seconds truncation (when sendFractionalSeconds=true)
                    // that happens for fields we don't touch with ResultSet.updateNN(). For those fields we should pass the value as is or,
                    // better don't put them into final updater statement as requested by Bug#71143.
                    this.updater.setString(i + 1, getString(i + 1));
                    break;
                default:
                    this.updater.setObject(i + 1, getObject(i + 1), fields[i].getMysqlType());
                    break;
            }

        } else {
            this.updater.setNull(i + 1, 0);
        }
    }

    int numKeys = this.primaryKeyIndicies.size();
    for (int i = 0; i < numKeys; i++) {
        int idx = this.primaryKeyIndicies.get(i).intValue();
        this.setParamValue(this.updater, numFields + i + 1, this.thisRow, idx, fields[idx]);
    }
}
 
Example #10
Source File: StatementsTest.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
public void testParameterBindings() throws Exception {
    // Need to check character set stuff, so need a new connection
    Connection utfConn = getConnectionWithProps("characterEncoding=utf-8,treatUtilDateAsTimestamp=false,autoDeserialize=true");

    java.util.Date now = new java.util.Date();

    Object[] valuesToTest = new Object[] { new Byte(Byte.MIN_VALUE), new Short(Short.MIN_VALUE), new Integer(Integer.MIN_VALUE), new Long(Long.MIN_VALUE),
            new Double(Double.MIN_VALUE), "\u4E2D\u6587", new BigDecimal(Math.PI), null, // to test isNull
            now // to test serialization
    };

    StringBuilder statementText = new StringBuilder("SELECT ?");

    for (int i = 1; i < valuesToTest.length; i++) {
        statementText.append(",?");
    }

    this.pstmt = utfConn.prepareStatement(statementText.toString());

    for (int i = 0; i < valuesToTest.length; i++) {
        this.pstmt.setObject(i + 1, valuesToTest[i]);
    }

    ParameterBindings bindings = ((ClientPreparedStatement) this.pstmt).getParameterBindings();

    for (int i = 0; i < valuesToTest.length; i++) {
        Object boundObject = bindings.getObject(i + 1);

        if (boundObject == null || valuesToTest[i] == null) {
            continue;
        }

        Class<?> boundObjectClass = boundObject.getClass();
        Class<?> testObjectClass = valuesToTest[i].getClass();

        if (boundObject instanceof Number) {
            assertEquals("For binding #" + (i + 1) + " of class " + boundObjectClass + " compared to " + testObjectClass, boundObject.toString(),
                    valuesToTest[i].toString());
        } else if (boundObject instanceof Date) {

        } else {
            assertEquals("For binding #" + (i + 1) + " of class " + boundObjectClass + " compared to " + testObjectClass, boundObject, valuesToTest[i]);
        }
    }
}
 
Example #11
Source File: StatementsTest.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
/**
 * Tests for PreparedStatement.setNCharacterSteam()
 * 
 * @throws Exception
 */
public void testSetNCharacterStream() throws Exception {
    // suppose sql_mode don't include "NO_BACKSLASH_ESCAPES"

    createTable("testSetNCharacterStream", "(c1 NATIONAL CHARACTER(10), c2 NATIONAL CHARACTER(10), " + "c3 NATIONAL CHARACTER(10)) ENGINE=InnoDB");
    Properties props1 = new Properties();
    props1.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "false"); // use client-side prepared statement
    props1.setProperty(PropertyKey.characterEncoding.getKeyName(), "latin1"); // ensure charset isn't utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    ClientPreparedStatement pstmt1 = (ClientPreparedStatement) conn1.prepareStatement("INSERT INTO testSetNCharacterStream (c1, c2, c3) VALUES (?, ?, ?)");
    pstmt1.setNCharacterStream(1, null, 0);
    pstmt1.setNCharacterStream(2, new StringReader("aaa"), 3);
    pstmt1.setNCharacterStream(3, new StringReader("\'aaa\'"), 5);
    pstmt1.execute();
    ResultSet rs1 = this.stmt.executeQuery("SELECT c1, c2, c3 FROM testSetNCharacterStream");
    rs1.next();
    assertEquals(null, rs1.getString(1));
    assertEquals("aaa", rs1.getString(2));
    assertEquals("\'aaa\'", rs1.getString(3));
    rs1.close();
    pstmt1.close();
    conn1.close();

    createTable("testSetNCharacterStream", "(c1 NATIONAL CHARACTER(10), c2 NATIONAL CHARACTER(10), " + "c3 NATIONAL CHARACTER(10)) ENGINE=InnoDB");
    Properties props2 = new Properties();
    props2.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "false"); // use client-side prepared statement
    props2.setProperty(PropertyKey.characterEncoding.getKeyName(), "UTF-8"); // ensure charset is utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    ClientPreparedStatement pstmt2 = (ClientPreparedStatement) conn2.prepareStatement("INSERT INTO testSetNCharacterStream (c1, c2, c3) VALUES (?, ?, ?)");
    pstmt2.setNCharacterStream(1, null, 0);
    pstmt2.setNCharacterStream(2, new StringReader("aaa"), 3);
    pstmt2.setNCharacterStream(3, new StringReader("\'aaa\'"), 5);
    pstmt2.execute();
    ResultSet rs2 = this.stmt.executeQuery("SELECT c1, c2, c3 FROM testSetNCharacterStream");
    rs2.next();
    assertEquals(null, rs2.getString(1));
    assertEquals("aaa", rs2.getString(2));
    assertEquals("\'aaa\'", rs2.getString(3));
    rs2.close();
    pstmt2.close();
    conn2.close();
}
 
Example #12
Source File: SyntaxRegressionTest.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
/**
 * ALTER TABLE syntax changed in 5.6GA
 * 
 * ALTER TABLE ... , algorithm, concurrency
 * 
 * algorithm:
 * | ALGORITHM [=] DEFAULT
 * | ALGORITHM [=] INPLACE
 * | ALGORITHM [=] COPY
 * 
 * concurrency:
 * | LOCK [=] DEFAULT
 * | LOCK [=] NONE
 * | LOCK [=] SHARED
 * | LOCK [=] EXCLUSIVE
 * 
 * @throws SQLException
 */
public void testAlterTableAlgorithmLock() throws SQLException {
    if (!versionMeetsMinimum(5, 6, 6)) {
        return;
    }
    Connection c = null;
    Properties props = new Properties();
    props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true");

    try {
        c = getConnectionWithProps(props);

        String[] algs = { "", ", ALGORITHM DEFAULT", ", ALGORITHM = DEFAULT", ", ALGORITHM INPLACE", ", ALGORITHM = INPLACE", ", ALGORITHM COPY",
                ", ALGORITHM = COPY" };

        String[] lcks = { "", ", LOCK DEFAULT", ", LOCK = DEFAULT", ", LOCK NONE", ", LOCK = NONE", ", LOCK SHARED", ", LOCK = SHARED", ", LOCK EXCLUSIVE",
                ", LOCK = EXCLUSIVE" };

        createTable("testAlterTableAlgorithmLock", "(x VARCHAR(10) NOT NULL DEFAULT '') CHARSET=latin2");

        int i = 1;
        for (String alg : algs) {
            for (String lck : lcks) {
                i = i ^ 1;

                // TODO: 5.7.5 reports: "LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED."
                //       We should check if situation change in future
                if (!(lck.contains("NONE") && alg.contains("COPY"))) {

                    String sql = "ALTER TABLE testAlterTableAlgorithmLock CHARSET=latin" + (i + 1) + alg + lck;
                    this.stmt.executeUpdate(sql);

                    this.pstmt = this.conn.prepareStatement("ALTER TABLE testAlterTableAlgorithmLock CHARSET=?" + alg + lck);
                    assertTrue(this.pstmt instanceof ClientPreparedStatement);

                    this.pstmt = c.prepareStatement(sql);
                    assertTrue(this.pstmt instanceof ServerPreparedStatement);
                }
            }
        }

    } finally {
        if (c != null) {
            c.close();
        }
    }
}
 
Example #13
Source File: SyntaxRegressionTest.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
/**
 * CREATE TABLE syntax changed in 5.6GA
 * 
 * InnoDB: Allow the location of file-per-table tablespaces to be chosen
 * CREATE TABLE ... DATA DIRECTORY = 'absolute/path/to/directory/'
 * 
 * Notes:
 * - DATA DIRECTORY option can't be used with temporary tables.
 * - DATA DIRECTORY and INDEX DIRECTORY can't be used together for InnoDB.
 * - Using these options result in an 'option ignored' warning for servers below MySQL 5.7.7. This syntax isn't allowed for MySQL 5.7.7 and higher.
 * 
 * @throws SQLException
 */
public void testCreateTableDataDirectory() throws SQLException {
    if (!versionMeetsMinimum(5, 6, 6)) {
        return;
    }

    try {
        String tmpdir = null;
        String separator = File.separatorChar == '\\' ? File.separator + File.separator : File.separator;
        this.rs = this.stmt.executeQuery("SHOW VARIABLES WHERE Variable_name='tmpdir' or Variable_name='innodb_file_per_table'");
        while (this.rs.next()) {
            if ("tmpdir".equals(this.rs.getString(1))) {
                tmpdir = this.rs.getString(2);
                if (tmpdir.endsWith(File.separator)) {
                    tmpdir = tmpdir.substring(0, tmpdir.length() - 1);
                }
                if (File.separatorChar == '\\') {
                    tmpdir = StringUtils.escapeQuote(tmpdir, File.separator);
                }
            } else if ("innodb_file_per_table".equals(this.rs.getString(1))) {
                if (!this.rs.getString(2).equals("ON")) {
                    fail("You need to set innodb_file_per_table to ON before running this test!");
                }
            }
        }

        dropTable("testCreateTableDataDirectorya");
        dropTable("testCreateTableDataDirectoryb");
        dropTable("testCreateTableDataDirectoryc");
        dropTable("testCreateTableDataDirectoryd");

        createTable("testCreateTableDataDirectorya", "(x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir + "'");
        createTable("testCreateTableDataDirectoryb", "(x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir + separator + "'");
        this.stmt.executeUpdate("CREATE TEMPORARY TABLE testCreateTableDataDirectoryc (x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir
                + (versionMeetsMinimum(5, 7, 7) ? "' ENGINE = MyISAM" : "'"));
        createTable("testCreateTableDataDirectoryd", "(x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir + separator + "' INDEX DIRECTORY = '"
                + tmpdir + (versionMeetsMinimum(5, 7, 7) ? "' ENGINE = MyISAM" : "'"));
        this.stmt.executeUpdate("ALTER TABLE testCreateTableDataDirectorya DISCARD TABLESPACE");

        this.pstmt = this.conn
                .prepareStatement("CREATE TABLE testCreateTableDataDirectorya (x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir + "'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);

        this.pstmt = this.conn.prepareStatement(
                "CREATE TABLE testCreateTableDataDirectorya (x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir + separator + "'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);

        this.pstmt = this.conn.prepareStatement(
                "CREATE TEMPORARY TABLE testCreateTableDataDirectorya (x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir + "'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);

        this.pstmt = this.conn.prepareStatement("CREATE TABLE testCreateTableDataDirectorya (x VARCHAR(10) NOT NULL DEFAULT '') DATA DIRECTORY = '" + tmpdir
                + "' INDEX DIRECTORY = '" + tmpdir + "'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);

        this.pstmt = this.conn.prepareStatement("ALTER TABLE testCreateTableDataDirectorya DISCARD TABLESPACE");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);

    } finally {
        // we need to drop them even if retainArtifacts=true, otherwise temp files could be deleted by OS and DB became corrupted
        dropTable("testCreateTableDataDirectorya");
        dropTable("testCreateTableDataDirectoryb");
        dropTable("testCreateTableDataDirectoryc");
        dropTable("testCreateTableDataDirectoryd");
    }

}
 
Example #14
Source File: SyntaxRegressionTest.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
/**
 * Test case for ALTER [IGNORE] TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2 syntax
 * 
 * @throws SQLException
 */
public void testExchangePartition() throws Exception {
    if (!versionMeetsMinimum(5, 6, 6)) {
        return;
    }
    createTable("testExchangePartition1", "(id int(11) NOT NULL AUTO_INCREMENT, year year(4) DEFAULT NULL,"
            + " modified timestamp NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB ROW_FORMAT=COMPACT PARTITION BY HASH (id) PARTITIONS 2");
    createTable("testExchangePartition2", "LIKE testExchangePartition1");

    this.stmt.executeUpdate("ALTER TABLE testExchangePartition2 REMOVE PARTITIONING");

    // Using Statement, with and without validation.
    if (versionMeetsMinimum(5, 7, 5)) {
        this.stmt.executeUpdate("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2 WITH VALIDATION");
        this.stmt.executeUpdate("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2 WITHOUT VALIDATION");
    } else if (versionMeetsMinimum(5, 7, 4)) {
        this.stmt.executeUpdate("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");
    } else {
        this.stmt.executeUpdate("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");
        this.stmt.executeUpdate("ALTER IGNORE TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");
    }

    // Using Client PreparedStatement, with validation.
    if (versionMeetsMinimum(5, 7, 5)) {
        this.pstmt = this.conn
                .prepareStatement("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2 WITH VALIDATION");
    } else if (versionMeetsMinimum(5, 7, 4)) {
        this.pstmt = this.conn.prepareStatement("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");
    } else {
        this.pstmt = this.conn.prepareStatement("ALTER TABLE testExchangePartition1 " + "EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");
    }
    assertEquals(ClientPreparedStatement.class, this.pstmt.getClass());
    this.pstmt.executeUpdate();

    // Using Client PreparedStatement, without validation.
    if (versionMeetsMinimum(5, 7, 5)) {
        this.pstmt = this.conn
                .prepareStatement("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2 WITHOUT VALIDATION");
    } else {
        this.pstmt = this.conn.prepareStatement("ALTER IGNORE TABLE testExchangePartition1 " + "EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");
    }
    assertEquals(ClientPreparedStatement.class, this.pstmt.getClass());
    this.pstmt.executeUpdate();

    Connection testConn = null;
    try {
        testConn = getConnectionWithProps("useServerPrepStmts=true,emulateUnsupportedPstmts=false");

        // Using Server PreparedStatement, with validation.
        if (versionMeetsMinimum(5, 7, 5)) {
            this.pstmt = testConn
                    .prepareStatement("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2 WITH VALIDATION");
        } else if (versionMeetsMinimum(5, 7, 4)) {
            this.pstmt = testConn.prepareStatement("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");
        } else {
            this.pstmt = testConn
                    .prepareStatement("ALTER IGNORE TABLE testExchangePartition1 " + "EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");

        }
        assertEquals(com.mysql.cj.jdbc.ServerPreparedStatement.class, this.pstmt.getClass());
        this.pstmt.executeUpdate();

        // Using Server PreparedStatement, without validation.
        if (versionMeetsMinimum(5, 7, 5)) {
            this.pstmt = testConn
                    .prepareStatement("ALTER TABLE testExchangePartition1 EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2 WITHOUT VALIDATION");
        } else {
            this.pstmt = testConn.prepareStatement("ALTER TABLE testExchangePartition1 " + "EXCHANGE PARTITION p1 WITH TABLE testExchangePartition2");

        }
        assertEquals(com.mysql.cj.jdbc.ServerPreparedStatement.class, this.pstmt.getClass());
        this.pstmt.executeUpdate();
    } finally {
        if (testConn != null) {
            testConn.close();
        }
    }
}