Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

how to write the query with "'" in the middle of the value for Oracle ?

experts,

i am try to write this query, but keep getting Oracle ora-1008, NOT ALL VARIABLES BOUND error when I am trying to select a row with name like " D'Andrea Mary".

 String query = "select id from users where last||', '||first = ?";
      PreparedStatement pst = conn.prepareStatement(query.toString());
      pst = conn.prepareStatement(query);
      pst.setString(1, name);
      ResultSet rs = pst.executeQuery(query);

how to re-write this query to make it work?
thanks.
0
panpanW
Asked:
panpanW
1 Solution
 
Helena Markováprogrammer-analystCommented:
If you want to use such a string in Oracle:
'select id from users where name=D'Andrea Mary'
it ought to be like this:
'select id from users where name=D'''Andrea Mary' -> it is 3 times '

I hope this will be a little help for you.

0
 
panpanWAuthor Commented:
thanks. actually, it should be '' , 2 times for this query to work.

but, my case is I need to write something "generic", that fits all kind of values, which is a variable. any advice?

0
 
girionisCommented:
I suggest you do a search and replace on the ' character. Look at the replaceAll method of the String class and replace the single quote with two single quotes so you will tell oracle to escape the next single quote.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now