Using JDBC how to put single quotes(')

[Imran Ahmed Khan]

I am having this problem with JDBC ... Using INSERT INTO statement i want to insert a row in my database and the variables that contain data might contain a single quote (') within them ... whenever i use a single quote in any of the text feilds that are to be inserted in the database i get an error i.e Misssing Operator or something like that, without any quotes the statement is working correctly..........
So please if anyone can tell me how to insert a value in the database which contains a single quote(').... here is the code. I am using a class which has this function.

public boolean sendMail(String to,String sender,String message,String subject,String folder)throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Connection conn = DriverManager.getConnection("Jdbc :-o	 dbc:email");
Statement state= conn.createStatement();
java.util.Date mydt=new java.util.Date();
String dates = mydt.toString();
boolean read=false;
PreparedStatement ps = conn.prepareStatement("INSERT INTO Messages ( Userid, Message, Msg_dates, Sender,Read, Folder, Subject )VALUES (?,?,?,?,?,?,?)");
ps.setString(1,to);
ps.setString(2,message);
ps.setString(3,dates);
ps.setString(4,sender);
ps.setBoolean(5,read);
ps.setString(6,folder);
ps.setString(7,subject);
int modified = ps.executeUpdate();
if (modified == 1)
{
state.close();
conn.close();
return true;
}
else
{
state.close();
conn.close();
return false;
}
}
}  

[ernest fakudze]

Okay Imran, I've experienced the same problem myself before. Here is a a piece of code that should do the trick.
<%!
/**
* replaceAll() - Replace all occurences of old string with new string in
* a given source string.
*/
public static String replaceAll(String source, String oldString, String newString) {
int idx = 0;
while ( (idx = source.indexOf(oldString, idx)) >= 0 ) {
source = source.substring(0, idx) + newString +
source.substring(idx + oldString.length());
idx += newString.length();
} // end while.
return source;
}// end replaceAll().

/**
* wrapField() - Wraps a field with single quotes if needed.
*/
public static String wrapField(String value) {
// If there is a single quote in the string, replace it with 2 single quotes.
if (value.indexOf("'") >= 0) {
return (replaceAll(value, "'", "''"));
} // end if
return value;
} // end wrapField().
%>
All you gotta do is to call the wrapField() method in your insert stmt for the fieldname that contains a single quote e.g....
insert into tblName(...) values() ... you get my drift. I use this code all the time because it does the trick.

[Michael Ernest]

This topic has been cross-posted in the JDBC forum, where it is appropriate to the theme. Since both topics have inspired replies, I am closing this one but not moving it. Please continue this discussion in JDBC as you wish, and please refrain from posting the same issue in multiple forums.
Thanks,
[ May 05, 2002: Message edited by: Michael Ernest ]