Using prepared statements

[Joseph Rudman]

Hello,

I'm creating a web application which displays record stored in an mysql database. I have created a PreparedStatement method as follows:

public static void doPreparedStatements(Connection conn, ResultSet rsClients, int rowInt){

String sql = "UPDATE clients SET firstName = ? WHERE clientID=" +rowInt;
PreparedStatement ps = null;
System.out.println("sql = " +sql);

try {
// Updating the database.
ps = conn.prepareStatement(sql);
ps.setString(1, rsClients.getString("firstName"));
ps.executeUpdate();
} catch (SQLException sqle) {
System.err.println("Error! Opening connection has failed: " + sqle);
sqle.printStackTrace();
}
}

I'm very new to jdbc and sql and I cannot see what is wrong with the sql statement above - it does not error and it does not update the mysql database.

Can anyone see anything wrong with sql.

Any help will be greatly appreciated.

Cheers Joe.

[stu derby]

First, I'd write it like this:

public static void doPreparedStatements(Connection conn, ResultSet rsClients, int rowInt){
 
  String sql = "UPDATE clients SET firstName = ? WHERE clientID= ?";
  PreparedStatement ps = null;
  System.out.println("sql = " +sql);
 
  try {
    // Updating the database.
    ps = conn.prepareStatement(sql);
    ps.setString(1, rsClients.getString("firstName"));
    ps.setInt(2, rowInt);
    ps.executeUpdate();
  } 
  catch (SQLException sqle) {
    System.err.println("Error! Opening connection has failed: " + sqle);
    sqle.printStackTrace();
  }
  finally {
    ps.close();
  }
}

Second, there of course needs to be a row that matches in your database.

Third, if you've set autocommit off (somewhere else), which you generally should do, you will need to commit your changes.
[ May 01, 2006: Message edited by: stu derby ]

[stu derby]

Oh.. Now that I look at it more closely, I see that you're passing in a ResultSet.

ResultSets are always positioned BEFORE the first row, you have to call ResultSet.next() to advance the next row.

Also, while I don't think this is a problem you have, keep in mind that re-executing the same statement will close a previous ResultSet.