Results 1 to 5 of 5
  1. #1
    thelinuxguy is offline Member
    Join Date
    Dec 2008
    Posts
    44
    Rep Power
    0

    Default MySQL/JDBC Prepared Statement Select query

    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

  2. #2
    wolfcro is offline Member
    Join Date
    Feb 2009
    Location
    Italy
    Posts
    51
    Rep Power
    0

    Default

    try to put "" around the prepared variable

    Java Code:
    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

  3. #3
    thelinuxguy is offline Member
    Join Date
    Dec 2008
    Posts
    44
    Rep Power
    0

    Default

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

    theLinuxGuy

  4. #4
    Join Date
    Aug 2008
    Location
    Saint-Petersburg, Russia
    Posts
    66
    Rep Power
    0

    Default

    Use
    Java Code:
    ResultSet rs = ps.executeQuery();
    instead of
    Java Code:
    ResultSet rs = ps.executeQuery(query);
    In other words do not pass query string.

  5. #5
    thelinuxguy is offline Member
    Join Date
    Dec 2008
    Posts
    44
    Rep Power
    0

    Default

    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

Similar Threads

  1. MySQL/JDBC prepared statement problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 11:21 PM
  2. Mysql/JDBC update query problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 09:56 PM
  3. Prblem in Prepared Statement
    By haneeshrawther in forum JDBC
    Replies: 2
    Last Post: 04-25-2008, 09:49 AM
  4. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •