• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using Oracle Q Quote operator in Prepared Statement

 
karthik Suryanarayanan
Ranch Hand
Posts: 121
Java Opera Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I have a situation where we are trying to use the Q quote operator for inserting french characters in to Oracle 11 G DB via Java JDBC.

SQL Query is as follows






When we try to run this code, we are getting SQL exception. Is there any way we can use the q quote operator in preparedStatements without replacing the direct values with the question mark ? at the SQL.

We are trying to use Q quote as french characters are getting inserted as junk characters in DB.

Thanks.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does this query work if you do this using direct SQL?
I'm just asking because I thought q'[]' was simply to avoid having to escape single quote characters, and I cannot see it having any effect on what looks like an encoding issue.

In any case, the way you are currently doing it will treat the q'[]' as part of the bind parameter, and not as an operator.
And since binding will automatically escape any quote characters then the q'[]' operator isn't required.

This really does look like a "simple" encoding problem.
I've quoted simple because encoding problems can take some effort to track down.
You need to check what encoding is being used in the database, in the source string, in the tool you're using to check the database.
 
karthik Suryanarayanan
Ranch Hand
Posts: 121
Java Opera Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Dave,
When the query is applied directly , it works. The encoding used in database is UTF-8.

But when using prepared statement and preparing the query using bind parameter without substituting the values, it doesn't work.

Also , when the the query is executed without using the q quote operator , JDBC by default converts all french characters into some other encoding format and not as of what we have supplied.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. I cannot see how q'[]' is going to fix an encoding issue.
2. What are you using to test your query, outside of your Java app?
3. How have you logged the data coming into the bit of your app that has this PreparedStatement in it?
4. What exactly gets messed up?

I'm asking all these because I have not had a problem with storing French stuff in Oracle via JDBC.
 
karthik Suryanarayanan
Ranch Hand
Posts: 121
Java Opera Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Dave,
I am using Oracle SQL Developer to run the query manually and test. That works!!!

And before executing the query , I have logged the data and found it to be correct.

Exactly , when the prepared statement binds the query with actual data , it encloses the q quote within single quotes like ' q'[data]' ' and not as q'[data]'.

I tried using q quote before setting the data using setString and that gives me an exception.

My database uses UTF8 and so I dont think any encoding issues might be there during insert.


 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But that's all the Oracle Thin driver does.
Translate from UTF-16 to UTF-8.

You haven't shown what data is causing the problem.

And you use q' in SQL Developer, but when you don't use it it fails to insert correctly?
 
karthik Suryanarayanan
Ranch Hand
Posts: 121
Java Opera Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:But that's all the Oracle Thin driver does.
Translate from UTF-16 to UTF-8.

You haven't shown what data is causing the problem.

And you use q' in SQL Developer, but when you don't use it it fails to insert correctly?



Dear Dave,
I have shared below on what data is passed and how it is getting stored without using q quote operator.

Passed Data

âäàå
ëèïéë

Data getting Stored in DB

âäàå
ëèïéë




 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
karthik Suryanarayanan wrote:
Exactly , when the prepared statement binds the query with actual data , it encloses the q quote within single quotes like ' q'[data]' ' and not as q'[data]'.


Since you want q'[data]' but you actually got 'q'[data]'', will escaping the single quote work? Such that when passed in the data looks like 'q\'[data]\''.

 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
karthik Suryanarayanan wrote:
Dave Tolls wrote:But that's all the Oracle Thin driver does.
Translate from UTF-16 to UTF-8.

You haven't shown what data is causing the problem.

And you use q' in SQL Developer, but when you don't use it it fails to insert correctly?



Dear Dave,
I have shared below on what data is passed and how it is getting stored without using q quote operator.

Passed Data

âäàå
ëèïéë

Data getting Stored in DB

âäàå
ëèïéë






And for SQLDeveloper.
When you do:
INSERT INTO MAIL_TBL (<snip other fields> OD_MAIL_BODY <snip other fields>) values ('âäàå')
do you get âäàå in the database?
And when you do:
INSERT INTO MAIL_TBL (<snip other fields> OD_MAIL_BODY <snip other fields>) values (q'[âäàå]')
do you get âäàå in the database?

I'm trying to see whether all the bases have been covered here.

As I keep saying, q' was written simply to handle quote characters in text. Essentially it allows you to redefine what character counts as the quote delimiter.
Here's the docs for it, just the Text Literals section.
There is nothing there about it changing how character encoding is handled and, to be frank, I would consider that to be a bug in the q-quote functionality.

I know it probably seems like I'm badgering, but it really looks to me like you are using an incorrect technique to get around some other problem that is utterly unrelated to q-quote.

And the reason you are having problems trying to use it in JDBC is that it makes no sense for you to redefine the quote delimiter as the binding mechanism handles all character escaping for you.
 
karthik Suryanarayanan
Ranch Hand
Posts: 121
Java Opera Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:

And for SQLDeveloper.
When you do:
INSERT INTO MAIL_TBL (<snip other fields> OD_MAIL_BODY <snip other fields>) values ('âäàå')
do you get âäàå in the database?
And when you do:
INSERT INTO MAIL_TBL (<snip other fields> OD_MAIL_BODY <snip other fields>) values (q'[âäàå]')
do you get âäàå in the database?

I'm trying to see whether all the bases have been covered here.

.



Dear Dave,
Executing both the above insert queries through SQL Developer , the data gets inserted correctly in DB as âäàå .

I would like to know if there is any other way to insert the french characters correctly without using q quote (as it gets inserted correctly through SQL deveoper without q quote)or is there something to do with the JDBC driver.


 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would now go back and revisit your assumptions.

Since you are dealing with an encoding issue, write a small test scenario where you insert those characters directly. If you have access to the db to set up a tiny test table to insert into that would be best.

I'm convinced it's a simple encoding issue.
Somewhere your data is getting messed up, and I doubt it's the Oracle driver.
 
karthik Suryanarayanan
Ranch Hand
Posts: 121
Java Opera Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Dave,
I finally found a solution. As you said , its not something to do with the q quote as it is unnecessary. The problem was with my String where the french data was in ISO-8859-1 encoding.

Hence , instead of passing my data directly for insert into DB , i did something like the below and it worked. Thanks !!!

 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
karthik Suryanarayanan wrote:Dear Dave,
I finally found a solution. As you said , its not something to do with the q quote as it is unnecessary. The problem was with my String where the french data was in ISO-8859-1 encoding.

Hence , instead of passing my data directly for insert into DB , i did something like the below and it worked. Thanks !!!



Good job.
We all end up going down blind alleys once in a while...
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So... your database uses UTF-8, but your database driver is using ISO-8859-1. And you wrote some code which reverses that incorrect assumption. Sure, it works, but I think a better solution would be to tell the database driver to use UTF-8. I expect there's a parameter of the database URL which allows you to do that.
 
karthik Suryanarayanan
Ranch Hand
Posts: 121
Java Opera Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Paul,
Sure. I will do more analysis and post the results.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic