[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

JDBC, mySQL,  JSP: Cannot insert into database table

Posted on 2004-08-13
17
Medium Priority
?
928 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

834 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