help with an update query

[gearypa]

Hi
I am doing a fantasy football game and i am having trouble with an update statement. I have a results database which has 3 fields:- winning_teams,losing_teams, and drawing_teams. The winning_teams column contains the names of the teams which have won. All team in this column must have 5 points added to their "point_scored" total. I have my code running but it only updates the first name in the "winning_teams" field. I want it to go through the whole resultset, but it seems to stop after the first update. I have included the code that i have used. As you can see from the code I have included print statements to show that "Driver loaded", "Connection established" and "statement created". These statements appear when i run the code but "ResultSet is closed" also appears when i run it. I would be very grateful if someone could tell me what i am doing wrong. Thanks

public class Team
{
  public static void main(String args[])
    {
    Vector win = new Vector();
    Vector lose = new Vector();
    Vector draw = new Vector();

    try{//start of try
          //connect to database
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          System.out.println("Drivers loaded");
          String url = "jdbc:odbc:fantasyfootball";
          Connection con = DriverManager.getConnection(url,"root","");
          System.out.println("Connection established");

          Statement stmt = con.createStatement();
          System.out.println("Statement created");
          ResultSet res = stmt.executeQuery("SELECT winning_teams FROM results");


          //list the returned rows
          while(res.next())
          {
            stmt.executeUpdate("UPDATE teams SET points_scored=(points_scored+5) WHERE team_name='"+res.getString("winning_teams")+"'");


          }


          //clean up
          stmt.close();
          con.close();
        }//end of try

        catch(Exception e){//start of catch
          System.out.println(e.getMessage());
          e.printStackTrace();
        }//end of catch
    }

 

[Kafenated]

Try using a mysql Driver for Java
it is called mysql-connector-java-3.0.6-stable-bin.jar
I think you can find it at the mysql site.

I had the same issue when I switched to the mysql driver it resolved the issue

Connection String would look like this
Class.forName("com.mysql.jdbc.Driver"
System.out.println("Drivers loaded"
String url = "jdbc:mysql://localhost/fantasyfootball";

Hope that helps