Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

USE SERVLET TO UPDATE TWO TABLES

Posted on 2004-10-21
9
Medium Priority
?
331 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
Comment
Question by:rcmb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 37

Expert Comment

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

Accepted Solution

by:
zzynx earned 2000 total points
ID: 12368797
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
ID: 12368921
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Author Comment

by:rcmb
ID: 12368997
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
ID: 12369038
>>// 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
ID: 12369042
That line is a copy of the line above it
0
 
LVL 12

Author Comment

by:rcmb
ID: 12369048
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
ID: 12369061
>> 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
ID: 12369068
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

609 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