How to use the sign " in a query submittion?

[thelordoftherings]

Hello,

How do I execute a query to the Database (MSSQL) that contains the " sign? I tried replacing " with \" but it didn't work...

 

[sedj]

Use a PreparedStatement.

Instead of :

Connection c ..
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("select * from bla where qqq = \"hello\"");

do :

Connection c ..
PreparedStatement ps = c.prepareStatement("select * from bla where car = ?");
ps.setString(1, "\"hello\"");
ResultSet rs = s.executeQuery();

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[thelordoftherings]

Hello sedj,

First, thank you for the quick response (as usuall...
Second, Why using executeQuery won't work? And what if my expression is long and contains few " in the middle, for example:

abc"def"ghi"j kl mno"p qrst"u v" w xyz"

 

[sedj]

>>>> Why using executeQuery won't work?
Not entirely sure, but I think it is the way the driver prepares the SQL statement on the db server - I think it is done slightly differently using a PreparedStatement.

>>>> And what if my expression is long and contains few " in the middle,
Use a PreparedStatement, I don't believe this would be a problem.



--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[thelordoftherings]

Could it be because a PreparedStatement is compiled before the actual submitiion it the parameters are trasferring link in stored procedure? Do you think I'm right?
And regarding the solution: Use a PreparedStatement and simply bound the whole expression with \"\" like this: \"abc"def"ghi"j kl mno"p qrst"u v" w xyz"\" Or should I do it for every place that " exist?

 

[sedj]

You could possibly be right !


Depends on how you are getting the string - if it is being passed from another function, then do it like :

void doit(String param) {
  Connection c ..
  PreparedStatement ps = c.prepareStatement("select * from bla where car = ?");
  ps.setString(1, param);
  ResultSet rs = s.executeQuery();
}

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[thelordoftherings]

So simply put the String as is using the setString? Interesting. I will try it and let you know...