Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

prepared statement problem + special character or something else

 
Tom Reese
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all,
I am currently trying to get these images out of this really simple database I created. The problem is I cannot get all of the images exported because some of the rows have name's such as, Bandit's Knife, notice the apostrophe. Everytime I try to export a image with a name that includes a apostrophe I will get a error, "Invalid sql syntax at or near s". Its a fairly small database with a couple hundred images or so. I thought that if I tried using a PreparedStatement instead that would help but I ran into another error, "The column index is out of range: 1, number of columns: 0". I also tried using the escape character that is mentioned here on the postgresql 9.1 docs I though that if I inserted the letter "E" in the correct place it would escape that single quote in the string but that did nothing. I really open to suggestions, here is what I have code wise.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Firstly, I'm not sure whether you can use PreparedStatement to execute several SQL statements in PostgreSQL, but maybe you can. Even so, I'd suggest to remove the BEGIN TRANSACTION and COMMIT from there and use standard Java mechanism for that. See Connection.commit and Connection.setAutoCommit methods for details.

The error is caused by using the question mark inside a string literal; in this case it does not denote a PreparedStatement parameter. The PreparedStatement therefore does not expect you to set any parameter, it does not "see" them. You need to place it outside of the String literal. You can use string concatenation to concatenate the value of the parameter to the rest of your strings. The query could therefore look like this:


Note that I have removed the apostrophes around the question marks, they stand alone now.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
... and welcome to the Ranch!
 
Tom Reese
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot Martin, that worked, I was not familiar enough with how the PreparedStatement operated so that is why I had trouble with it. Thanks again!

By the way, what does the "||" operator do, i noticed that if I removed them that the sql failed, is that for the preparedstatement to parse?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The || operator is a string concatenation operator (this is the SQL standard, though some databases use something different, like "+"). It has little to do with the PreparedStatement. You have to use this operator since the question mark is actually a variable inside the SQL statement and you need to use it according to SQL syntax of the database.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic