SQLSyntaxErrorException: ORA-00911: invalid character

Question

I'm trying to INSERT records to an Oracle DB using PreparedStatement but I only get this error. At this point, my effort to overcome it far outweighs my progress so another set of eyes might help. Where is the invalid character?

A lot of what I've found suggests that a trailing ";" inside of your sql String can be the culprit, but I haven't had one in my statement from the outset.

My connection itself, which works perfectly at several other places in the program:

Properties props = new Properties();
props.setProperty( "user", username );
props.setProperty( "password", password );
props.setProperty( "defaultRowPrefetch", "10" );
props.setProperty( "defaultBatchValue", "10" );
props.setProperty( "processEscapes", "false" );

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection(DB_URL_SVC, props);

The way I'd like to get it done (except that I would wrap it in a method that accepts three Strings) but it throws a SQLSyntaxErrorException

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // ORA-00911: invalid character error

This works, but defeats the purpose of using PreparedStatement since the parameters are hard-coded:

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( 'JHT' , 'USA' , '2500' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.executeUpdate();

Works. However, I understand that concatenating variables with single and double quotes also isn't really the best way because PreparedStatement should alleviate us from having to deal with that part of the syntax:

String value1 = "JHT";
String value2 = "USA";
int value3 = 2500;
String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '" + value1 + "', '" + value2 + "', '" + value3 + "' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.executeUpdate();

Fails with a SQLSyntaxErrorException. So even if code quotation syntax myself, I'm still unable to place those variables in the preStatement.setString(), which would at least allow a little flexibility.

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "' + value1 + '");
preStatement.setString(2, "' + value2 + '");
preStatement.setInt(3, "' + value3 + '");
preStatement.executeUpdate();    // ORA-00911: invalid character error

Fails. Enclosing the placeholders in my String with single quotes results in SQLException.

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '?' , '?' , '?' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // invalid column index

Fails. Enclosing the two String (but not the int) placeholders in my String with single quotes results in SQLException.

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '?' , '?' , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // invalid column index

This doesn't fail, but doesn't write to database either (even though I haven't disabled auto-commit).

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeBatch();

I've also tried all matter of escapes with backslashes, double backslashes, backticks, quitsies, no-startsies, erasies, double-stamps, and tofus-make-it-true! Maybe somebody out there knows the voodoo that will help me?!

Solution

Is there a reason why you have props.setProperty( "processEscapes", "false" );?

I believe that this turns off the ability to use ? as bind parameter placeholder. I believe that if escape processing is enabled, JDBC does some 'magic' with the ? placeholders before passing the SQL string to Oracle. Otherwise, the ? character is sent to the database as-is.

There are occasional uses for disabling escape processing. I used it in a previous answer to a question involving ? characters in passwords. I believe it can be disabled at the connection or the statement level; to re-enable escape processing on a PreparedStatement, try calling preStatement.setEscapeProcessing(true);. I would expect the first of your failing examples to succeed with this option set.

As for your failing examples, those with unescaped ?s will cause problems as ? is not a valid character in SQL. Surrounding ? in single quotes turns it into a single-character string, so it wouldn't be a bind parameter even if escape processing is enabled. I can't say why the last one doesn't write to the database.