Geek Answers Handbook

SQLSyntaxErrorException: ORA-00911: invalid character

I am trying to write an INSERT to Oracle DB using PreparedStatement, but I only get this error. At the moment, my efforts to overcome it far outweigh my progress, so a different set of eyes can help. Where is an invalid character?

A lot of what I found suggests that the final " ; " inside your sql string may be the culprit, but I did not have it in my statement from the very beginning.

My very connection, which works fine in 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);

How I would like to do this (except that I would wrap it in a method that takes three lines), but it throws an 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 one 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();

Work . However, I understand that combining variables with single and double quotes is also not really the best way, since PreparedStatement should save 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();

Crash with SQLSyntaxErrorException . So even if the syntax for code quotes is by itself, I still cannot place these variables in preStatement.setString (), which at least allows 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

Failed. Attaching placeholders in my single-quoted string results in a 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

Failure . Attaching two String (but not int ) placeholders in my single-quoted string results in a 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 one does not work, but does not write to the database (even if I did not turn off 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 also tried all questions about escapes with backslashes, double backslashes, backticks, quitsies, no-startsies, erasies, double-stamp and tofus-make-it-true ! Maybe someone out there knows voodoo that will help me ?!

+4
source share
1 answer

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

I believe this disables usability ? as a binding parameter binding. I believe that if evacuation processing is enabled, does JDBC do some magic with placeholders ? before passing the SQL string to Oracle. Otherwise, the symbol ? sent to the database as is.

Periodically used to disable evacuation processing. Did I use it in a previous answer to a question with participation ? characters in passwords. I believe that it can be disabled at the connection or approval level; to re-enable evacuation processing on PreparedStatement, try calling preStatement.setEscapeProcessing(true); . I would expect the first of your unsuccessful examples to succeed in this set of parameters.

As for your unsuccessful examples, are those with unescaped ? cause problems because ? is not a valid character in SQL. Environment ? in single quotes, turns it into a single-character string, so it will not be a binding parameter, even if evacuation processing is enabled. I can not say why the latter does not write to the database.

+4
source

More articles:


All Articles