CODEWITHSUNDEEP
sqlrubyoracle-databaseactiverecordoracle11g
Matt Westlake
Matt Westlake

Reputation: 3651

where is the invalid character? oracle 11g on simple sql

I'm using oracle 11G with OJDBC6.jar adapter. when I try to run this very simple insert statement, it throws an invalid character exception. 

ActiveRecord::JDBCError: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
: insert into user_defaults values (8526547125,'H214Qa99547')

My gemfile is as follows:

gem 'activerecord', '4.0.4'
gem 'ActiveRecord-JDBC', '0.5'
gem 'activerecord-jdbc-adapter', '1.3.6'

This is on Oracle 11g with OJDBC6.jar

Upvotes: 0

Views: 768

Answers (4)

carbonr
carbonr

Reputation: 6067

add this line statement_escape_processing: true in the database configuration file, like this. This should sort the issue

development:
  adapter: jdbc
  driver: oracle.jdbc.OracleDriver
  statement_escape_processing: true

Upvotes: 0

Lynn Crumbling
Lynn Crumbling

Reputation: 13377

I've been burned by out-of-range ASCII in the past. It would be best to rule that out, especially if you've tried everything else that you can think of.

Delete and retype the entire line or lines. Resist the temptation to paste the line after you delete it. If there's a non-printable ASCII character in there, it'll get rid of it.

Make sure that you do this for any line of code that interacts with the SQL statement, whether it be building the string in a variable or actually passing the argument to run the query.

Upvotes: 0

Steve Chambers
Steve Chambers

Reputation: 39464

Could this be to do with the inferred column order? Try specifying the columns to remove any ambiguity:

INSERT INTO user_defaults (user_id, home_office_id)
VALUES (8526547125, 'H214Qa99547')

Upvotes: 1

ngrashia
ngrashia

Reputation: 9904

  1. Check if the query is able to be executed directly in the developer tool rather than via java. If query fails, then check for invalid data types and correct it.
  2. If query is executed successfully in developer tool, then probably some issue in coding, then
    1. Check for query terminator string like semi colon in the string passed.
    2. Try using SOP in your java program to print the query just before inserting.

Upvotes: 0

Related Questions