MySQL / Java / PreparedStatement Quoting
Posted by Custard (Custard), 8 December 2003Hi guys.
I wasn't 100% sure whether this was java or Mysql, but I have a feeling it's mysql, so I posted here.
In the code below, there is an UPDATE and a SELECT.
Following an SQLException on running the SELECT, I found I had to quote the ? in the PreparedStatement.
That worked fine.
For consistency, It seemed logical to quote the string literal in the UPDATE line. This fails to work, but quietly without exception.
Now I'm not sure what the 'Right Thing' to do is.
And, should the INSERT statement have quoted values?
Examples and documentation I have seen so far don't put quotes in like I did.
To Quote or Not To Quote.
Posted by admin (Graham Ellis), 8 December 2003I would expect to have to put quotes around any literal text value or anything that's resolved to a literal text value - so I would expect you to need them as you found on the SELECT. UPDATE has always struck me as a bit of an oddity where the more normal syntax rules don't apply so I'm not 100% suprised - only 80%
By the way - am I reading your code right? Do you have a tablename column within your table, and you're dealing only with references to a table within itself? Wow ... reminds me of a Donald Rumsfeld: "We know what we know and we don't know what we don't know. We do know there are some things we don't know ....."
Posted by Custard (Custard), 8 December 2003He he. I tricked you by not giving the context for tableName.
I have a bunch of classes, one for each table, and to assist in trying to keep some of the names consistent, I have a private final tableName="idGenerator" at the top.
They are sort of DAO classes, but home-grown. This one is an ID generator class, so it breaks some of my normal conventions by a) not having 'findBy' methods, and b) by updating its table, but It doesn't make sense for this class, and think I can get away with it the once
Anyway, regarding the quotes. I have left the code as it is, 'because it works' at least for now with mysql. If it fails in the future when we change db, then I will cross that bridge at that time.
This is one of those projects that you just want to get over and done with asap any way that you can.
Thanks for your help.
Posted by Custard (Custard), 12 December 2003Ok. Here's the _answer_.
It's a bit OT, as it turned our to be a Java issue, but here goes.
The PreparedStatement does quote correctly, and you don't need to put quotes around the ? like I did.
The error I got was a red-herring, and my mistake was not calling rs.next() before reading the contents of the record.
The code should read...
The ResultSet is returned with the cursor positioned before the first record, not at it as I thought.
So, slap on the wrist to me for not RTFM!
Posted by admin (Graham Ellis), 13 December 2003Thanks for the update and completing the loop for us!
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: firstname.lastname@example.org • WEB: http://www.wellho.net • SKYPE: wellho