Forums Register Login

SQL syntax error in java driving me mad

+Pie Number of slices to send: Send
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
leaving: getPreviousSelectedAnswer
inside fillPreviouslyCheckedCheckBoxes -->fillAnswers:
Leaving fillPreviouslyCheckedCheckBoxes()
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)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2728)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2678)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
at RetrieveData.getPreviousSelectedAnswer(RetrieveData.java:267)
+Pie Number of slices to send: Send
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.

Regards,
Ravi

+Pie Number of slices to send: Send
Your code now includes this line:
But you still haven't told us what the SQL looks like. Do you have any loose quotes in there, for example? Missing spaces around SQL key-words? Who knows?
1
+Pie Number of slices to send: Send
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 = ?.
+Pie Number of slices to send: Send
Hi,
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:
questionNum: 1
testerName: John Doe
testDate: 12-17-2012
testTime: 13:00

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.

+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
Hi Rob,

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.
+Pie Number of slices to send: Send
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):
1
+Pie Number of slices to send: Send
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();
Instead of:
ResultSet rs = gpsaPrepStmt.executeQuery(queryString);

Also, your third query string is incorrect as the question marks in queryString should not have quotes around them.
+Pie Number of slices to send: Send
Hi Vanessa,

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). :-)

Thanks again your eye found the fix! :-)

Joe
1
+Pie Number of slices to send: Send
Good catch, Vanessa!

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.)
1
+Pie Number of slices to send: Send
It actually is for some drivers. When I try something similar with an MS SQL Server driver I get the following exception:
brevity is the soul of wit - shakepeare. Tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 30485 times.
Similar Threads
wild cards in MySQL using jdbc
Using PreparedStatement
java sql, what am I doing wrong in my update query?
How to get input from user and search from database and display results in a table format?
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 05:35:36.