Hi!
I want to insert some data on a database. the query looks like this:
INSERT INTO Table Name=' " + name + ' '" ......
this works when name contains something like "paul"..
but when there are characters like ' an exception occurs.
how can I insert data like " my ' jone's' "?
thx in advance!
chris
You could use escape characters, but the simplest way is to use PreparedStatement with ? in the query string.
String myQuery = "INSERT INTO Table Name=?";
PreparedStatement stmt = connection.prepareStatement( myQuery );
stmt.setString(1, "O'Reilly");
stmt.executeUpdate();
JCG
Well, you are running into or you will run into a problem know to SQL Developers as the "Double Single Quote Problem". In SQL when ever you have a single quote ' it is taken as an escape character, so you must escape the escape character with another single quote '' (2 single quotes, not a double quote), and hence, the name "Double Single Quote Problem".You should not have to do the prepared statements as long as you remeber to search each string and add a single quote to each existing single quote. I do it with a small routine that I call before each SQL statement.
BTW in the SQL I've done so far, it does not matter if you have a statement to be interpreted, a prepared statement, or a stored procedure: a single quote in SQL is an escape character and results in an error unless they are paired.