Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to escape ? in commons.dbutils.QueryRunner

 
Abhijith Prabhakar
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I am using commons.dbutils.QueryRunner.update(String) method to update a row. This works perfectly fine, if String parameter query doesn't contain any "?" marks charecter. If String contains "?", then update throws an SQLException
-No value specified for parameter 1

I tried escaping "?" with backslash "\". Can someone please tell me how can I escape "?" while using QueryRunner.update?

Thanks in advance
Abhijith
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Normally, I'd say call stmt.setEscapeProcessing(false) before executing the query, but since you are using QueryRunner I'm guessing you don't have access to the statement object. Another option is to set the ? and its surrounding parameters as a ? such as:
In this way, the first question mark is replaced by the first item in the parameters array, which is untouched.
 
Ted Smyth
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could also extend QueryRunner and override/add methods to get access to the Statement object for setEscapeProcessing(false);
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott, I think Abhijith's problem is that from time to time a query pops up like this one:
Select publisher from books where title='What is the name of this book?'
Note that it has a question mark which is part of the text, not meant to be a parameter at all.
[ July 08, 2008: Message edited by: Paul Clapham ]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not really, he specifically uses update() instead of select, but either way, how is this solution any different?
Should still work since after the parameters are inserted, the processing is complete. Keep in mind this code will be sent to the database as: Select publisher from books where title='What is the name of this book?'

The problem with sending the second the statement directly is that JDBC PreparedStatement muddles things up and if you can't turn processing off on the statement, this is the next best thing.
[ July 08, 2008: Message edited by: Scott Selikoff ]
 
Santhosh Kumar
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The fact that you are using prepare statement, you shouldn't be hardcoding the dynamic content in it instead should be making use of parameters. That is what they were designed for.

However if you still want to hardcode the values in the preparedStatement itself, you can enclose the Strings in the single quotes ('), and it should work fine. Check out this example.



which prints

[ July 08, 2008: Message edited by: Santhosh Kumar ]
 
Abhijith Prabhakar
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear all,

Thanks for the reply.

My problem is bit different. I have to update a row in database not select it.
It is something like this
String strQuery = "UPDATE tablename set columnname = 'there is ? in middle'";
QueryRunner queryRunner = new QueryRunner(this.getDataSource());
queryRunner.update(strQuery);

This is working perfectly fine for all other strings. It is throwing exception only when there is a question mark present in String like given above.

Thanks,
Abhijith
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried?
 
Abhijith Prabhakar
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, this works...thanks
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic