protected void drop(Statement statement) { if (statement == null) { return; } dropStatement("drop table valid_sql_logged", statement); }
@Test public void testPreparedStatementExecUpdateWithNulls() throws SQLException { try { testPreparedStatementWithNulls(true); } catch (Exception e) { fail(e.getMessage() + " due to error: " + getStackTrace(e)); } }
private PreparedStatement getPreparedStatement(boolean isUpdate) throws SQLException { if (isUpdate) { return connection .prepareStatement("insert into valid_sql_logged (col_varchar, col_integer, col_decimal" + (isDateTimeSupported() ? ", col_date, col_timestamp" : "") + ", col_smallint" + (isBooleanSupported() ? ", col_boolean" : "") + ") values (?,?,?,?" + (isDateTimeSupported() ? ",?,?" : "") + (isBooleanSupported() ? ",?" : "") + ")"); } else { return connection .prepareStatement("select * from valid_sql_logged where col_varchar = ? and col_integer = ? and col_decimal = ? " + (isDateTimeSupported() ? " and col_date = ? and col_timestamp = ? " : "") + " and col_smallint = ? " + (isBooleanSupported() ? " and col_boolean = ?" : "")); } }
private void testPreparedStatementWithNulls(boolean isUpdate) throws SQLException { final PreparedStatement prep = getPreparedStatement(isUpdate); int i = 0; prep.setNull(++i, java.sql.Types.VARCHAR); prep.setNull(++i, java.sql.Types.INTEGER); prep.setNull(++i, java.sql.Types.INTEGER); if (isDateTimeSupported()) { prep.setNull(++i, java.sql.Types.DATE); prep.setNull(++i, java.sql.Types.TIMESTAMP); } prep.setNull(++i, java.sql.Types.INTEGER); if (isBooleanSupported()) { prep.setNull(++i, java.sql.Types.BOOLEAN); } if (isUpdate) { prep.executeUpdate(); } else { prep.executeQuery(); } prep.close(); reRunStatement(isUpdate); }
@Before public void setUpStatement() throws SQLException { Statement statement = connection.createStatement(); drop(statement); // here are some basic ones, inspired by the list on: // http://dba.stackexchange.com/questions/53317/databases-are-there-universal-datatypes // VARCHAR // INTEGER // DECIMAL // DATE (with surprises: Oracle has it but includes a time) // TIMESTAMP (does something different than expected on SQL Server an MySQL) // some extra added anyway statement .execute("create table valid_sql_logged (col_varchar varchar(255), col_integer integer, col_decimal decimal " + (isDateTimeSupported() ? ", col_date date, col_timestamp timestamp" : "") + ", col_smallint smallint " + (isBooleanSupported() ? ", col_boolean boolean" : "") + ")"); statement.close(); super.clearLogEntries(); }
@Test public void testSingleQuotePresentInValueOneTimeEscaped() throws SQLException { try { final PreparedStatement prep = connection.prepareStatement("select * from valid_sql_logged where col_varchar = ?"); prep.setString(1, "foo'value"); prep.executeQuery(); prep.close(); } catch (Exception e) { fail(e.getMessage() + " due to error: " + getStackTrace(e)); } reRunStatement(false); }
@Test @Ignore("could not figure out the proper re-parsable 'databaseDialectDateFormat' for all the DBs in CI") public void testPreparedStatementExecQuery() throws SQLException { try { testPreparedStatement(false); } catch (Exception e) { fail(e.getMessage() + " due to error: " + getStackTrace(e)); } }
private void testPreparedStatement(boolean isUpdate) throws SQLException { try { final PreparedStatement prep = getPreparedStatement(isUpdate); int i = 0; prep.setString(++i, "prepstmt_test_col1"); prep.setInt(++i, 1); prep.setInt(++i, 1); if (isDateTimeSupported()) { prep.setDate(++i, new Date(System.currentTimeMillis())); prep.setTimestamp(++i, new Timestamp(System.currentTimeMillis())); } prep.setInt(++i, 1); if (isBooleanSupported()) { prep.setBoolean(++i, true); } if (isUpdate) { prep.executeUpdate(); } else { prep.executeQuery(); } prep.close(); } catch (Exception e) { fail(e.getMessage() + " due to error: " + getStackTrace(e)); } reRunStatement(isUpdate); }
@Test public void testSingleQuotePresentInValueMultipleTimesEscaped() throws SQLException { try { final PreparedStatement prep = connection.prepareStatement("select * from valid_sql_logged where col_varchar = ?"); prep.setString(1, "foo''value'"); prep.executeQuery(); prep.close(); } catch (Exception e) { fail(e.getMessage() + " due to error: " + getStackTrace(e)); } reRunStatement(false); }
@Test @Ignore("could not figure out the proper re-parsable 'databaseDialectDateFormat' for all the DBs in CI") public void testPreparedStatementExecUpdate() throws SQLException { try { testPreparedStatement(true); } catch (Exception e) { fail(e.getMessage() + " due to error: " + getStackTrace(e)); } }
@Test public void testPreparedStatementExecQueryWithNulls() throws SQLException { // Derby fails on this one => let's just skip it if ("Derby".equals(db)) { return; } try { testPreparedStatementWithNulls(false); } catch (Exception e) { fail(e.getMessage() + " due to error: " + getStackTrace(e)); } }