Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL syntax error in java driving me mad

 
Joe McTigue
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
Ravishanker kumar
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Rob Spoor
Sheriff
Pie
Posts: 20546
57
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 = ?.
 
Joe McTigue
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Rob Spoor
Sheriff
Pie
Posts: 20546
57
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Joe McTigue
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Joe McTigue
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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):
 
Vanessa Danin
Ranch Hand
Posts: 34
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Joe McTigue
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.)
 
Rob Spoor
Sheriff
Pie
Posts: 20546
57
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It actually is for some drivers. When I try something similar with an MS SQL Server driver I get the following exception:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic