JDBC, mySQL, JSP: Cannot insert into database table

// 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){}
     
   }
}
RrugemaliraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
haobaba1Connect With a Mentor Commented:
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
 
pulupulCommented:
Why do you say it could be wrong? Errors? stack trace?
0
 
haobaba1Commented:
         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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
RrugemaliraAuthor Commented:
I check the database and there is no data in it though program runs without showing any errors!
0
 
RrugemaliraAuthor Commented:
Surely, I'd forgot to include ps.executeUpdate(); before closing the connection.  
Now, I've it included, but still no data in database!!
0
 
objectsCommented:
>  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
 
RrugemaliraAuthor Commented:
/* 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
 
objectsCommented:
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
 
objectsCommented:
>       sql= new String( "INSERT INTO patient(patient_number) VALUES(?)");

is your intention here to insert a new record, with just a patient number?
0
 
RrugemaliraAuthor Commented:
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
 
objectsCommented:
> 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
 
objectsCommented:
> 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
 
RrugemaliraAuthor Commented:
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
 
objectsCommented:
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
 
RrugemaliraAuthor Commented:
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
 
objectsCommented:
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
All Courses

From novice to tech pro — start learning today.