prepare statement query contains question mark
Hello,
I am trying to get my prepare statement to execute the following sql query:
String query ="UPDATE TEST SET FieldOne=\"HOW DO I DO THIS?\"";
PrepareStatement _ps = Con.prepareStatement(query);
_ps.executeUpdate();
When the executeUpdate() is performed, the program throws the following exception:
Parameter 1 not set or registered for output
The prepare statement keeps barfing on the "?" inside the value being assigned to the field. Any thoughts on how to get around this?
Thanks,
I've never had this problem but I did find this that might help: http://java.sun.com/developer/onlineTraining/Programming/JDCBook/jdbc.html#esc
I tried the {escape} but I got the same error. I used the following update string:Update Detail200 SET InvoiceNumber="\?123444" WHERE Indexid=2835557 {escape "\"}
Question marks have a special meaning in PreparedStatements. You might find that in the JDBC tutorial since I am not in a mood to explain all that.
Since you are using PreparedStatement, it would be nice if you considered using it in a better way.
String query = "UPDATE TEST SET FieldOne=?";
PrepareStatement _ps = Con.prepareStatement(query);
_ps.setString(1, "\"HOW DO I DO THIS?\"");
_ps.executeUpdate();
This should fix your problem and make your query String more elegant (especially with bigger queries)
I understand that "?" mark is special. However, I have a single class in my code where I funnel all sql commands. I usually use the prepared statement on those queries that use the "?" parameter. But, sometimes I want to pass just a single query to my class, that might contain the above scenario. I am trying to accomplish this by just using the prepared statement.
> Hello,
>
> I am trying to get my prepare statement to execute
> the following sql query:
>
> String query = "UPDATE TEST SET FieldOne=\"HOW
> DO I DO THIS?\"";
> PrepareStatement _ps = Con.prepareStatement(query);
> _ps.executeUpdate();
>
>
> When the executeUpdate() is performed, the program
> throws the following exception:
>
> Parameter 1 not set or registered for output
>
> The prepare statement keeps barfing on the "?" inside
> the value being assigned to the field. Any thoughts
> on how to get around this?
>
> Thanks,
Try this
String query = "UPDATE TEST SET FieldOne=\'HOW
DO I DO THIS?\'"
thanks ,
nvseenu
Thanks for all the help. I believe that I have found a solution.
Since I know at this point in my class that the incoming statement is not a prepared statement I can use the following code snipet:
int _iQuestionMarkCount = CharacterCount.getCharCount(getSQLStatement().toCharArray(), "?".charAt(0));
for (int q=1;q<=_iQuestionMarkCount;q++)
__PreparedStmt.setString(q,"?");
This will "fill in" the prepared statments value for each ? with ?.
That's a horrible solution. Why not use the prepared statement properly as suggested in reply 3 above?
It's ironic that you've come up with a nasty hack to "fix" the problem when the very reason it's barfing on the question mark is a feature that allows you to work around escaping issues.
(edit) I read your explanation, incidentally, but it didn't seem to rule out using the parameterised statements as such.
A thorn for a thorn. A ? for a ?:o)
I have no control over the incoming SQL query. Since I know that the incoming query IS NOT A PREPARD STATEMENT, the only way to do what was suggested in step 3 would be to parse the string for all "=" and determine the value.
All I was attempting to do here was to use the prepared statement for all cases in my class, instead of switching from preparestatement to statement.
> I have no control over the incoming SQL query. Since
> I know that the incoming query IS NOT A PREPARD
> STATEMENT, the only way to do what was suggested in
> step 3 would be to parse the string for all "=" and
> determine the value.
>
> All I was attempting to do here was to use the
> prepared statement for all cases in my class, instead
> of switching from preparestatement to statement.
You might want to keep in mind that queries like that can present a security risk if they are constructed rather than being used as static strings.
Prepared statement usage with arguments do not have that risk.
wow... i spent several hours looking for a solution to this problem. Im actually using a regular Statement, and it was still substituting the question marks ('?') for funk.
fixed it like this:
Statment stmt;
stmt..setEscapeProcessing(false); // turn off escape processing
not sure if this works for PrepareStatement, but i dont really care cause it fixes my problem.
oops got too many '.' in there!should be:Statment stmt;stmt.setEscapeProcessing(false); // turn off escape processing
