Solved

JDBC, mySQL,  JSP: Cannot insert into database table

Posted on 2004-08-13
17
908 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
  • 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
 

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 500 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
maven project in eclipse 11 57
stateless EJB with glassfish 2 70
parse example 13 86
JSONSerializer.toJSON in JSP generating error 8 53
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

22 Experts available now in Live!

Get 1:1 Help Now