aspose file tools*
The moose likes JDBC and the fly likes SQL syntax error in java driving me mad Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL syntax error in java driving me mad" Watch "SQL syntax error in java driving me mad" New topic
Author

SQL syntax error in java driving me mad

Joe McTigue
Ranch Hand

Joined: Mar 05, 2012
Posts: 52
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

Joined: Jul 20, 2006
Posts: 53
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

Joined: Mar 01, 2009
Posts: 1772
    
  14

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?


No more Blub for me, thank you, Vicar.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19718
    
  20

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 = ?.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Joe McTigue
Ranch Hand

Joined: Mar 05, 2012
Posts: 52
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

Joined: Oct 27, 2005
Posts: 19718
    
  20

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

Joined: Mar 05, 2012
Posts: 52
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

Joined: Mar 05, 2012
Posts: 52
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

Joined: Oct 04, 2004
Posts: 34
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

Joined: Mar 05, 2012
Posts: 52
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Oct 27, 2005
Posts: 19718
    
  20

It actually is for some drivers. When I try something similar with an MS SQL Server driver I get the following exception:
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL syntax error in java driving me mad