SQLSyntaxErrorException: ORA-00911: invalid character
database, java, jdbc, oracle, sql
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.
Problem
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?!