Having problem figuring out why this prepared statement won't work...
Any help would be greatly appreciated.
SQL error message and Stack trace are printed below code.
I'm using a MySQL 5.1 database with my java 7.
Error I'm recieving:
SQLException: getPreviousSelectedAnswer return -> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND TestDate = ? AND TestTime = ?' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND TestDate = ? AND TestTime = ?' at line 1
inside fillPreviouslyCheckedCheckBoxes -->fillAnswers:
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
As per stack trace, issue is with SQL query. Its difficult to provide you any solution, it depend on your method parameter that you are passing to method 'getPreviousSelectedAnswer'. Please check what query created at line number 11 in above code and modify the code if required. Issue may be with
1. Run time values passed to method 'getPreviousSelectedAnswer' OR
2. query which is getting created at line number 11 in above code.
For question 1, your query is SELECT Answer 1 FROM SavedAnswers WHERE TesterName = ? AND TestDate = ? AND TestTime = ?. That Answer 1 part is not allowed.
If the column actually includes a space you need to put double quotes or back ticks around the column name:
SELECT "Answer 1" FROM SavedAnswers WHERE TesterName = ? AND TestDate = ? AND TestTime = ? SELECT `Answer 1` FROM SavedAnswers WHERE TesterName = ? AND TestDate = ? AND TestTime = ?.
Actually I removed the System.out.println from the output, sorry just trying to clean it up for you guys.
Here's what the System.out.println read:
queryString: SELECT Answer_1 FROM SavedAnswers WHERE TesterName = ? AND TestDate = ? AND TestTime = ?
I also added in some system print lines to show the varible values:
testerName: John Doe
The queryString should be the following which works when I use it from the mysql commandline:
queryString: SELECT Answer_1 FROM SavedAnswers WHERE TesterName = "John Doe" AND TestDate = "12-17-2012" AND TestTime = "13:00";
Any help would be appreciated in solving this delima...
Happy Holidays and thanks for your continued help on this.
Ah right, that _ was invisible to me (as is more often the case...). You can ignore my previous post.
What are the types of TestDate and TestTime? Are they VARCHAR columns, or do they have a proper date/time type? In case of the latter you shouldn't use Strings to set them but the proper objects (java.sql.Date, java.sql.Time or java.sql.Timestamp). You can use DateFormat and java.util.Date to create those from Strings.
Joined: Mar 05, 2012
Here are the value types of those fields in mysql:
Testername is VARCHAR(40)
TestDate is VARCHAR(20)
TestTime is VARCHAR(20)
Answer_1 is VARCHAR(140)
Thanks for the suggestions, I plan on updating those later, I just need this to work before moving to the next level of refinement. :-)
Let me know if you or anyone else can help with on the cuurent problem.
Joined: Mar 05, 2012
Why is it that the following first three <queryStrings> do not work, while the last one does?, (as long as I comment out the gpsaPrepStmt.setString statements):
Joe, what happens if you use the non-parameterised version of executeQuery as defined for PreparedStatement, instead of using the inherited version of the executeQuery method from Statement?
That is, what happens if you use:
ResultSet rs = gpsaPrepStmt.executeQuery();
ResultSet rs = gpsaPrepStmt.executeQuery(queryString);
Also, your third query string is incorrect as the question marks in queryString should not have quotes around them.
Joined: Mar 05, 2012
Thanks so much for seeing through the mists for me.
I think I must've wrote that code at 3 am and wasn't thinking good at that time.
Funny thing I wrote multiple other methods to do similar prepared statement queries and wrote those correctly.
I really should think about taking up drinking coffee again (at least in the wee hours when coding). :-)
Joe, you're not the first one who asked for help with the very same issue here. Unfortunately, in your case it took some time to nab it....
(Side note: I think that the executeQuery(String sql) in PreparedStatement should be overriden to throw an error. It doesn't make sense to use this method in PreparedStatement, one should always create a Statement for that.)