File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes with Java: how to update a row in sybase with string having a single quote Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "with Java: how to update a row in sybase with string having a single quote" Watch "with Java: how to update a row in sybase with string having a single quote" New topic
Author

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

sri pat
Greenhorn

Joined: Oct 01, 2004
Posts: 8
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
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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.
sri pat
Greenhorn

Joined: Oct 01, 2004
Posts: 8
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
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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: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
Greenhorn

Joined: Oct 01, 2004
Posts: 8
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
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

David,
Parameters definitely start with 1 in JDBC.

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Francis Shillitoe
Greenhorn

Joined: Aug 30, 2002
Posts: 22
You can escape a single quote in Sybase by using two single quotes together. So try replacing your ' with ''

Francis


<a href="http://www.shillitoe.com" target="_blank" rel="nofollow">http://www.shillitoe.com</a>
sri pat
Greenhorn

Joined: Oct 01, 2004
Posts: 8
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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: with Java: how to update a row in sybase with string having a single quote