MySQL/JDBC Prepared Statement Select query

[thelinuxguy]

Hi
I am having trouble with a select query using a prepared Statement.
I have as follows:-
String query = "select statement.dateOut, statement.amountOut from statement, account where "
+ " account.account_number = statement.account_number and account.account_number = ?";
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, accountNumber);
ResultSet rs = ps.executeQuery(query);
while(rs.next())
{
dateWithdrawn = rs.getDate("dateOut");
checkStatement = rs.getInt("amountOut");
System.out.println("\n Balance " + accountBalance +
"\nAmount Out " + checkStatement +
"\tdate" + dateWithdrawn);
}

I keep getting the message:-
com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: 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 '?' at line 1

Any help appreciated

the LinuxGuy

[wolfcro]

try to put "" around the prepared variable

String query = "select statement.dateOut, statement.amountOut "
   +"from statement, account "
   +"where account.account_number = statement.account_number "
   +"and account.account_number = \"?\"";

maybe works like that.... if not try to check the exact SQL syntax of your MySQL version

[thelinuxguy]

Thanks Wolfcro
It still doesnt work, I checked the MySQL syntax and I dont think select queries are supported by prepared Statements.

theLinuxGuy

[ProjectKaiser]

Use

ResultSet rs = ps.executeQuery();

instead of

ResultSet rs = ps.executeQuery(query);

In other words do not pass query string.

[thelinuxguy]

Hi ProjectKaiser

Thanks for your input it works perfectly.

String query = "select dateOut, amountOut from statement, account" +
" where account.account_number = statement.account_number and " +
"account.account_number = ? ";
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, accountNumber);
result = ps.executeQuery();
while(result.next())
{
dateWithdrawn = result.getDate("dateOut");
checkStatement = result.getInt("amountOut");
System.out.println(
"\nDate" + dateWithdrawn +
"\tAmount" + checkStatement);
}


theLinuxGuy