Menu
I am using a PreparedStatement with sql such as:
String sql = "insert into foo (a,b,c) values (?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(psIndex++, a);
ps.setString(psIndex++, b);
ps.setString(psIndex++, c);
But if any of the variables is an empty string the resulting statement gets two single quotes. As in: VALUES ('foo','','') Then I get an exception since two single quotes is an escape sequence.
I can't believe I couldn't find anything on this through searching, but I could not. What is going on here?
512
setString(1, usernameObject); setString(2, privilegeObject); The purpose of PreparedStatement is to reduce the difficulty and readability of the database connection code.
Prepared statements are much faster when you have to run the same statement multiple times, with different data. Thats because SQL will validate the query only once, whereas if you just use a statement it will validate the query each time.
Once a PreparedStatement is prepared, it can be reused after execution. You reuse a PreparedStatement by setting new values for the parameters and then execute it again.
As the OP doesn't do what @Adam suggested in the comments, I'll do it. It's useful for future readers. Thanks to @user119179 for the idea.
It could be a bug in the JDBC driver we are using. The provider of the driver should know that '' is an escape sequence.
Actually, updating the driver seems to solve the bug for the OP.
129
As in: VALUES ('foo','','') Then I get an exception since two single quotes is an escape sequence.
There is a misunderstanding here. The two single quotes is the empty string. There is no escape sequence happening. It is an escaped quote only if it is in another single quote. If you are getting an exception, it is probably elsewhere, such as a constraint on the column in the database.
The statement
insert into foo (a,b,c) values ('foo','','')
is very valid SQL.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
