Solved

USE SERVLET TO UPDATE TWO TABLES

Posted on 2004-10-21
301 Views
Last Modified: 2013-11-24
I have a servlet I use to update a database. Basically what happens is the user will reset a certain field to 'No' for all users when required to do so. My problem is this update only includes one table and I need it to update two tables. Below is the code I use to update the first table. How can I modify this code to include my second table ("pers_riders") so I can also perform the query:


UPDATE pers_riders SET underway='No'

RCMB


Code of current page:


      public boolean ResetUW() {

            Connection conn = null;
            PreparedStatement pstmt = null;

            // perform database update
            try {
                  conn = ConnectionManager.getConnection();

                  String sql =
                        "UPDATE pers_members SET underway=? WHERE prospective_gain = 'No'";

                  PreparedStatement statement = conn.prepareStatement(sql);
                  statement.setString(1, Underway);
                  
                  statement.executeUpdate();
                  System.out.println("#rows= "+statement.executeUpdate());

                  if (statement != null)
                        statement.close();
                  if (conn != null)
                        conn.close();

            } catch (Exception e) {
                     e.printStackTrace();
            }

            return true;
      }
0
Question by:rcmb
    9 Comments
     
    LVL 37

    Expert Comment

    by:zzynx
    What's the problem?
    Just create another prepared statement and executeUpdate() that also?
    Or am I missing something?
    0
     
    LVL 37

    Accepted Solution

    by:
    I mean:

                   String sql =
                        "UPDATE pers_members SET underway=? WHERE prospective_gain = 'No'";

                   PreparedStatement statement = conn.prepareStatement(sql);
                   statement.setString(1, Underway);
                   
                   int n = statement.executeUpdate();
    //               System.out.println("#rows= "+statement.executeUpdate());        // Don't call executeUpdate twice!!!  <<<<<<<<<
                    System.out.println("#rows= "+n);

                   if (statement != null)
                        statement.close();

                   // New one
                   sql = ................ ; // your update statement for the 2nd table
                   statement = conn.prepareStatement(sql)
                   statement.setString(1, Underway); // or whatever

                   n = statement.executeUpdate();
                   System.out.println("#rows= "+n);

                   if (statement != null)
                        statement.close();
                   if (conn != null)
                        conn.close();
    0
     
    LVL 35

    Expert Comment

    by:TimYates
    And just to be sure, move your final closes into a finally block:

    so:

                  if (statement != null)
                        statement.close();
                   if (conn != null)
                        conn.close();

              } catch (Exception e) {
                      e.printStackTrace();
              }

    becomes

              } catch (Exception e) {
                      e.printStackTrace();
              } finally {
                  try { if (statement != null) statement.close(); } catch( SQLException ex ) {}
                   try { if (conn != null) conn.close(); } catch( SQLException ex ) {}
              }
    0
     
    LVL 12

    Author Comment

    by:rcmb
    Okay -- Here is what I have done:

    Look at my notes:

          public boolean ResetUW() {

                Connection conn = null;
                PreparedStatement pstmt = null;

                // perform database update
                try {
                      conn = ConnectionManager.getConnection();


                      String sql =
                            "UPDATE pers_members SET underway=? WHERE prospective_gain = 'No'";

                      PreparedStatement statement = conn.prepareStatement(sql);
                      statement.setString(1, Underway);
                      
                      int n = statement.executeUpdate();
                      System.out.println("# crewmembers rows udpated = "+n);

                      if (statement != null)
                            statement.close();

                      sql =
                            "UPDATE pers_riders SET underway = ? WHERE underway = 'Yes'";

                      statement = conn.prepareStatement(sql);

    // On this line I get an error that states Duplicate local variable
                      PreparedStatement statement = conn.prepareStatement(sql);
                      statement.setString(1, Underway);
                      
                      n = statement.executeUpdate();
                      System.out.println("# rider rows udpated = "+n);

                      if (statement != null)
                            statement.close();
                      if (conn != null)
                            conn.close();
                
                } catch (Exception e) {
                         e.printStackTrace();
                }

                return true;
          }

    Tim -- When I try to implement your code at the end I get "statement cannot be defined"

    RCMB
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    >>// On this line I get an error that states Duplicate local variable
    >>               PreparedStatement statement = conn.prepareStatement(sql);

    Just remove that line it's obsolete
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    That line is a copy of the line above it
    0
     
    LVL 12

    Author Comment

    by:rcmb
    Okay -- I have it figured out.

    I renamed the second statement to statement2 and added a close for statement2. This worked like a champ.

    Thanks for your help.

    RCMB
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    >> Tim -- When I try to implement your code at the end I get "statement cannot be defined"
    Move
            PreparedStatement statement

    outside the try - catch block
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    Thanks for accepting.

    Make sure you don't have

               statement = conn.prepareStatement(sql);

               PreparedStatement statement2 = conn.prepareStatement(sql);

    Don't do the update twice!!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    Suggested Solutions

    Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
    This video teaches viewers about errors in exception handling.

    845 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now