with Java: how to update a row in sybase with string having a single quote

[sri pat]

Hi,

I am trying to update a row in sybase database using a stored procedure with a string value( ex: john O'reilly)having a single quote in it using java. I am using the statement object to execute the stored procedure and getting the syntax error near the single quote thrown by the sybase driver. And there is no problem to update a database row if I use without a single quote. I appreciate your help if you throw me some ideas.

Thanks

[David Harkness]

You can use a PreparedStatement with ?s as substitution parameters to execute plain SQL, stored procedures/functions (CallableStatement for functions). It will automatically escape special characters based on the driver for you.

It's been a while since I've done hand-rolled JDBC, but it should be close to the following. The JavaDocs for PreparedStatement should demonstrate how to call a stored procedure/function.

String sql = "select id from store where name = ?";
String name = "Billy Bob's Sport Shop";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name);
ResultSet rs = ps.execute();

[sri pat]

Thanks for the reply. Actually I am executing a stored procedure(huge) with several parameters getting dynamically as part of the SQL. Do I need to change the stored procedure all the way(variables to ? marks) or is there anyway I can change the input parameters which I am passing dynamically.

[David Harkness]

Originally posted by sri pat:
Actually I am executing a stored procedure(huge) with several parameters getting dynamically as part of the SQL. Do I need to change the stored procedure all the way(variables to ? marks) or is there anyway I can change the input parameters which I am passing dynamically.

Just to be clear, you don't need to change the stored procedure it self at all. It's only the String that you are giving to JDBC. Instead of building the SQL as it would look if you typed it into isql directly, you build a String with ?s where the parameters would be.

You can write (admittedly tricky and error-prone) Java code to build the query dynamically based on input parameters as you are now. After preparing the statement with the Connection, set the parameter values on it and execute it.

I just looked up the JavaDocs again and I was mistaken: you need to use CallableStatement for both stored procedures and functions. The queries look like this:

{?= call <procedure-name>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}

My work machine appears to be down now, but if I can get to it tonight I'll post some code I wrote about nine months ago to call some stored procedures and functions. I do remember having to futz with the syntax a bit to get it to work.
[ December 22, 2004: Message edited by: David Harkness ]

[sri pat]

Thanks for the clarification but I am getting "java.sql.SQLException: JZ0SB: Parameter index out of range: 1." and used index starting from 1 even the parameter values(?) are really started from index(24) in stored procedure. I tried giving parameter index 24 in setString method, but still getting the same error saying: parameter index out of rance: 24"..any ideas

[David Harkness]

Hmm, it's been a while, but I thought that the indices start at 1 (instead of 0 like you'd expect). But perhaps I'm mistaken. Did you try 0? If that doesn't work, try looking at the docs for the driver itself, if there are any. The example in the JavaDocs shows them starting at 1.

You could try calling resetParameters() before setting the parameter values, but that shouldn't affect a newly prepared statement/call.

I've only done this with Oracle, so I won't be much more help.

[Jeanne Boyarsky]

David,
Parameters definitely start with 1 in JDBC.

Sri,
What happens if you switch the stored proc to have parameters starting with 1 too?

[Francis Shillitoe]

You can escape a single quote in Sybase by using two single quotes together. So try replacing your ' with ''

Francis

[sri pat]

Thanks alot for your help guys! Actually indexing starting from 1 is correct and working fine now but what happend is by some reason the CallableStatement object is not recognising the '?' symbols in SQL Query String while executing(used executeUpdate method)the query string. So I copy the dynamic values into local variables externally and passed it as a new parameters and it worked finally.