SQL syntax error in java driving me mad

[Joe McTigue]

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.

	public String getPreviousSelectedAnswer(int questionNum, 
                                                                            String testerName, 
                                                                            String testDate, 
                                                                            String testTime) {

		String columnName = "Answer_" + Integer.toString(questionNum);
		String questionString = "";
		String queryString = "SELECT " + columnName + 
                                                               " FROM SavedAnswers " +
                                                               "WHERE TesterName = ? AND TestDate = ? AND TestTime = ?";
		System.out.println("queryString: " + queryString);
		Connection conn = null;
				
		try {
			conn = this.getDatabaseConnection();
			PreparedStatement gpsaPrepStmt = conn.prepareStatement(queryString);
			gpsaPrepStmt.setString(1, testerName);
			gpsaPrepStmt.setString(2, testDate);
			gpsaPrepStmt.setString(3, testTime);
		    
			ResultSet rs = gpsaPrepStmt.executeQuery(queryString);
			
			while ( rs.next() ) {
				questionString = rs.getString(columnName);  //<--- this is line number 267
			}
		} catch (SQLException sqe) {
			System.out.println("SQLException: getPreviousSelectedAnswer return -> " + sqe.getMessage());
			sqe.printStackTrace();
		} catch (NullPointerException npe) {
			System.out.println("NullPointerException: setPreviousSelectedStarterInfo return -> " + npe.getMessage());
			npe.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}       
		return questionString;
	}

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]

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]

Your code now includes this line:

System.out.println("queryString: " + queryString); 

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]

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]

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]

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]

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]

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

	public String getPreviousSelectedAnswer(int questionNum, String testerName, String testDate, String testTime) {

		String columnName = "Answer_" + Integer.toString(questionNum);
		String questionString = "";
   // does not work -->String queryString = 
           //"SELECT " + columnName + " FROM savedanswers WHERE TesterName = ? AND TestDate = ? AND TestTime = ?";
   // does not work -->String queryString = 
           //"SELECT * FROM savedanswers WHERE TesterName = ? AND TestDate = ? AND TestTime = ?";
   // does not work -->String queryString = 
          //"SELECT * FROM savedanswers WHERE TesterName = '?' AND TestDate = '?' AND TestTime = '?'";
	   works-->String queryString = "SELECT * FROM savedanswers WHERE TesterName = '" + 
                                                                    testerName + "' AND TestDate = '" + 
                                                                    testDate + "' AND TestTime = '" + testTime + "'";

		Connection conn = null;
				
		try {
			conn = this.getDatabaseConnection();
			PreparedStatement gpsaPrepStmt = conn.prepareStatement(queryString);
			//gpsaPrepStmt.setString(1, testerName);  //--> "Joseph McTigue"
			//gpsaPrepStmt.setString(2, testDate);      //--> "12-27-2003"
			//gpsaPrepStmt.setString(3, testTime);      //--> "13:00"
			
			ResultSet rs = gpsaPrepStmt.executeQuery(queryString);
			
			while ( rs.next() ) {
				
				questionString = rs.getString(columnName);  //--> "answer_1"
			}
			rs.close();
		} catch (SQLException sqe) {
			System.out.println("SQLException: getPreviousSelectedAnswer return -> " + sqe.getMessage());
			sqe.printStackTrace();
		} catch (NullPointerException npe) {
			System.out.println("NullPointerException: getPreviousSelectedAnswer return -> " + npe.getMessage());
			npe.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}       
		System.out.println("leaving: getPreviousSelectedAnswer()");
		return questionString;
	}

[Vanessa Danin]

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]

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]

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]

It actually is for some drivers. When I try something similar with an MS SQL Server driver I get the following exception:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The method executeQuery() cannot take arguments on a PreparedStatement or CallableStatement.
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)