[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

JDBC, mySQL,  JSP: Cannot insert into database table

Posted on 2004-08-13
17
Medium Priority
?
926 Views
Last Modified: 2011-09-20
// JDBC:mySQL, JSP: Cannot insert into database table.

// What could conceivably be wrong with this? I truly appreciate your help.

// ================================================
public class DBHandlerPatient extends HttpServlet {
 
    public void doPost (HttpServletRequest request,
      HttpServletResponse response) {
      try { // OBTAIN A DATABASE CONNECTION
         
          // Step 1: Load the JDBC driver          
          Class.forName("com.mysql.jdbc.Driver");
             
          // Step 2: Establish a connection to the database          
       
          Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost/rtpmKenyaDB","rmwijage", "NahumCh1v4");
         
          String sql = "INSERT INTO patient(patient_id, address_id, " +
          "diagnosis_id, sdp_id, firstname, middlename, surname, " +
          "date_birth, gender, occupation, marital_status, num_depandants, " +
          "patient_number) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave the bean properties to be inserted into table rtpm_user
          PatientFormBean f = (PatientFormBean) request.getAttribute("formHandler");
         
          String fname = f.getFirstName();
          String mname = f.getMiddleName();
          String lname = f.getLastName();
          String birthdate = f.getDateOfBirth();
          String theGender = f.getGender();
          String theOccupation = f.getOccupation();
          String theStatus = f.getMaritalStatus();
          String theDependants = f.getNumOfDependants();
         
          // Set the parameters in the prepared statement
          ps.setNull(1, Types.BIGINT); // For primary key, tell type of column
          ps.setInt(2, 0);     // (SQL BIGINT)  later, to be given actual value from another table
          ps.setInt(3, 0);    // (SQL BIGINT)                    - " -
          ps.setInt(4, 0);    // (SQL MEDIUMINT)                   - " -
          ps.setString(5, fname);
          ps.setString(6, mname);
          ps.setString(7, lname);
          ps.setString(8, birthdate);
          ps.setString(9, theGender);
          ps.setString(10, theOccupation);
          ps.setString(11, theStatus);
          ps.setString(12, theDependants);
          ps.setInt(13, 0); // below, will be given value derived from patient_id
         
          // Insert the row in the table
          ps.executeUpdate();
         
          /* Query the database for the last inserted id. In this case,
           * the value of patient_id allocated automatically by the database */
          String queryLastInsertedId = new String("SELECT LAST_INSERT_ID()");
         
          /* parse queryLastInsertedID into an integer and insert it into
           * table patient as patient_number.
           * Later, I might want to have patient_number different from
           * patient_id but a derivative of it.                             */            
          int patientID = Integer.parseInt(queryLastInsertedId);
          sql = "INSERT INTO patient(patient_number) VALUES(?)";
          ps = conn.prepareStatement(sql);
          ps.setInt(13, patientID);                
   
          // Close the connection
          ps.close();
          conn.close();    
      }
      catch (SQLException e) {
          // handle any errors
          System.out.println("SQLException: " + e.getMessage());
          System.out.println("SQLState: " + e.getSQLState());
          System.out.println("VendorError: " + e.getErrorCode());
          }
      catch (Exception e) {
      e.printStackTrace();
      }
     
      // Send the request to rtpm_address.html  
      try {
          getServletConfig().getServletContext().
        //  getRequestDispatcher("/success_patient.jsp").
            getRequestDispatcher("/rtpm_address.html").
             forward(request, response);
      }
      catch (Exception e){}
     
   }
}
0
Comment
Question by:Rrugemalira
[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
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 3

Expert Comment

by:pulupul
ID: 11795365
Why do you say it could be wrong? Errors? stack trace?
0
 
LVL 3

Expert Comment

by:haobaba1
ID: 11795535
         sql = "INSERT INTO patient(patient_number) VALUES(?)";
          ps = conn.prepareStatement(sql);
          ps.setInt(13, patientID);                
   
          // Close the connection
          ps.close();


Do you mean to ps.executeUpdate() before closing the connection?

0
 

Author Comment

by:Rrugemalira
ID: 11795631
I check the database and there is no data in it though program runs without showing any errors!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Rrugemalira
ID: 11795753
Surely, I'd forgot to include ps.executeUpdate(); before closing the connection.  
Now, I've it included, but still no data in database!!
0
 
LVL 3

Accepted Solution

by:
haobaba1 earned 2000 total points
ID: 11796511
after this:
  String queryLastInsertedId = new String("SELECT LAST_INSERT_ID()");

you need to actually run the query;
int last_id_created=  conn.prepareStatement(queryLastInsertedId).executeQuery().getInt(1);

then

         sql =new String( "INSERT INTO patient(patient_number) VALUES(?)");
          ps = conn.prepareStatement(sql);
          ps.setInt(13, last_id_created);    


warning to the wise though you need to check and make sure the previous query was successful before you go and add a record to the patient table. the executeupdate function returns an integer that represents how many rows were affected during the update during an insert that number should be 1 if a failure occurs then you will get a zero or less than zero so check for that before you go and add more records to other tables based on the last inserted id because this may be an invalid or erroneous value that ends up screwing up your entire database.
0
 
LVL 92

Expert Comment

by:objects
ID: 11798041
>  ps.setNull(1, Types.BIGINT); // For primary key, tell type of column

If this is an autoincrement column then you should be setting it to null.
0
 

Author Comment

by:Rrugemalira
ID: 11798272
/* I must admit I'm a month old in Java, JSP and mySQL and I'm having painful teething problems.
 * This is what I have so far and still no successful data entry! I'd appreciate your corrections and instructions. Thanks alot.
 */

package patient.patientServlets;

import java.io.*;
import java.net.*;

import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import patientBeans.PatientFormBean;

public class DBHandlerPatient extends HttpServlet {
 
    public void doPost (HttpServletRequest request,
      HttpServletResponse response) {
      try { // OBTAIN A DATABASE CONNECTION
         
          // Step 1: Load the JDBC driver          
          Class.forName("com.mysql.jdbc.Driver");
             
          // Step 2: Establish a connection to the database          
       
          Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost/rtpmKenyaDB","owakato", "HabakkukCh1");
         
          String sql = "INSERT INTO patient(patient_id, address_id, " +
          "diagnosis_id, sdp_id, firstname, middlename, surname, " +
          "date_birth, gender, occupation, marital_status, num_depandants, " +
          "patient_number) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave the bean properties to be inserted into table rtpm_user
          PatientFormBean f = (PatientFormBean) request.getAttribute("formHandler");
         
          String fname = f.getFirstName();
          String mname = f.getMiddleName();
          String lname = f.getLastName();
          String birthdate = f.getDateOfBirth();
          String theGender = f.getGender();
          String theOccupation = f.getOccupation();
          String theStatus = f.getMaritalStatus();
          String theDependants = f.getNumOfDependants();
         
          // Set the parameters in the prepared statement
          ps.setNull(1, Types.BIGINT);          // 1) I this OK for PRIMARY KEY with autoincrement?
          ps.setInt(2, 0);   // SQL BIGINT. Later to be given value from another table
          ps.setInt(3, 0);   // SQL BIGINT.                = " =
          ps.setInt(4, 0);   // SQL MEDIUMINT      = " =
          ps.setString(5, fname);
          ps.setString(6, mname);
          ps.setString(7, lname);
          ps.setString(8, birthdate);
          ps.setString(9, theGender);
          ps.setString(10, theOccupation);
          ps.setString(11, theStatus);
          ps.setString(12, theDependants);
          ps.setInt(13, 0);
         
          // Insert the row in the table
          int inserted = ps.executeUpdate();  // 2) Is this right? Does an update take place and return a value to int inserted?
          System.out.println(inserted);           // 3) Isn't this ok for checking for a successful insert? I do not get any output!
         
          /* Query the database for the last inserted id. In this case,
           * the value of patient_id allocated automatically by the database
           */
          String query_lastInsertedId = new String("SELECT LAST_INSERT_ID()");
         
          // Carry out the query
          int last_id_created = conn.prepareStatement(query_lastInsertedId).executeQuery().getInt(1);
         
          System.out.println(last_id_created); // 4) ?
         
          sql= new String( "INSERT INTO patient(patient_number) VALUES(?)");
          ps = conn.prepareStatement(sql);
          ps.setInt(13, last_id_created); // Inserting into database
          int success_insert = ps.executeUpdate();
          System.out.println(success_insert); // 5) ?
         
          /* Later, I might want to have patient_number different from
           * patient_id but derived from it.
           */
          // Now close the connection
          ps.close();
          conn.close();
 
      }
      catch (SQLException e) {
          // handle any errors
          System.out.println("SQLException: " + e.getMessage());
          System.out.println("SQLState: " + e.getSQLState());
          System.out.println("VendorError: " + e.getErrorCode());
          }
      catch (Exception e) {
      e.printStackTrace();
      }
     
      // Send the request to rtpm_address.html  
      try {
          getServletConfig().getServletContext().
        //  getRequestDispatcher("/success_patient.jsp").
            getRequestDispatcher("/rtpm_address.html").
             forward(request, response);
      }
      catch (Exception e){}
     
   }
}
0
 
LVL 92

Expert Comment

by:objects
ID: 11798281
If patient_id is an autoincrement field then don't include it in your insert:

         String sql = "INSERT INTO patient(address_id, " +
          "diagnosis_id, sdp_id, firstname, middlename, surname, " +
          "date_birth, gender, occupation, marital_status, num_depandants, " +
          "patient_number) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);

...

        // Set the parameters in the prepared statement
          ps.setInt(1, 0);   // SQL BIGINT. Later to be given value from another table
          ps.setInt(2, 0);   // SQL BIGINT.                = " =
          ps.setInt(3, 0);   // SQL MEDIUMINT      = " =
          ps.setString(4, fname);
          ps.setString(5, mname);
          ps.setString(6, lname);
          ps.setString(7, birthdate);
          ps.setString(8, theGender);
          ps.setString(9, theOccupation);
          ps.setString(10, theStatus);
          ps.setString(11, theDependants);
          ps.setInt(12, 0);
0
 
LVL 92

Expert Comment

by:objects
ID: 11798285
>       sql= new String( "INSERT INTO patient(patient_number) VALUES(?)");

is your intention here to insert a new record, with just a patient number?
0
 

Author Comment

by:Rrugemalira
ID: 11798346
Yes indeed.
Initially, patient_number is set to a zero value when I've used ps.setInt(13, 0);
After patient_id is automatically set (incrimented), I want to give a value to patient_number, that is derived from patient_id (PRIMARY KEY). In this test case, patient_number  = patient_id.
I have also tried using sql= new String( "UPDATE patient(patient_number) VALUES(?)"); to no effect!
I have also removed patient_id from the INSERT statement as you indicated to me, in the last comment, but with no effect!


0
 
LVL 92

Expert Comment

by:objects
ID: 11798364
> I want to give a value to patient_number

Thats not what that code will do, it will add a new row to the table and not update the existing one. You need to do an update

> UPDATE patient(patient_number) VALUES(?)

Thats not the correct sql for an update, should be:

UPDATE patient set patient_number=? where patient_id=?

And if you just want to set the patient_number to the value of patient_id then you can use:

UPDATE patient set patient_number=patient_id where patient_id=?
0
 
LVL 92

Expert Comment

by:objects
ID: 11798372
> I have also removed patient_id from the INSERT statement as you indicated to me, in the last
> comment, but with no effect!

Are you saying no row is being inserted? and that inserted is 0.


0
 

Author Comment

by:Rrugemalira
ID: 11798467
Do you mean:
          sql = new String("UPDATE patient SET patient_number=? WHERE patient_id=?");

          ps = conn.prepareStatement(sql);
          ps.setInt(13, last_id_created);
          int success_insert = ps.executeUpdate();
          System.out.println(success_insert);

Could you please recommend a good book or manual?

         
0
 
LVL 92

Expert Comment

by:objects
ID: 11798491
try:

         sql = new String("UPDATE patient SET patient_number=patient_id WHERE patient_id=?");

          ps = conn.prepareStatement(sql);
          ps.setInt(13, last_id_created);
          int success_insert = ps.executeUpdate();
          System.out.println(success_insert);
0
 
LVL 92

Expert Comment

by:objects
ID: 11798494
0
 

Author Comment

by:Rrugemalira
ID: 11798502
Yes, you meant
 sql = new String("UPDATE patient SET patient_number=? WHERE patient_id=?");

          ps = conn.prepareStatement(sql);
          ps.setInt(13, last_id_created);
          int success_insert = ps.executeUpdate();
          System.out.println(success_insert);
for it works!

Also,      
ps.setNull(1, Types.BIGINT);  
is OK for primary key.
0
 
LVL 92

Expert Comment

by:objects
ID: 11798520
WHy did you accept that comment?
That will insert a new row into your table which you said is *not* what you want to do.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Working from home is a dream for many people who aren’t happy about getting up early, going to the office, and spending long hours at work. There are lots of benefits of remote work for employees.
While there are many new features for iOS 11, these are the five that can improve your digital lifestyle.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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