aspose file tools*
The moose likes JDBC and the fly likes how to escape ? in commons.dbutils.QueryRunner Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to escape ? in commons.dbutils.QueryRunner" Watch "how to escape ? in commons.dbutils.QueryRunner" New topic
Author

how to escape ? in commons.dbutils.QueryRunner

Abhijith Prabhakar
Ranch Hand

Joined: Dec 29, 2006
Posts: 56
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

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Ted Smyth
Ranch Hand

Joined: May 28, 2008
Posts: 73
You could also extend QueryRunner and override/add methods to get access to the Statement object for setEscapeProcessing(false);


Edward Smith
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18669
    
    8

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

Joined: Oct 23, 2005
Posts: 3710
    
    5

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

Joined: Nov 07, 2000
Posts: 242
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

Joined: Dec 29, 2006
Posts: 56
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

Joined: Oct 23, 2005
Posts: 3710
    
    5

Have you tried?
Abhijith Prabhakar
Ranch Hand

Joined: Dec 29, 2006
Posts: 56
Yes, this works...thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to escape ? in commons.dbutils.QueryRunner