Solved

JDBC; JSP; mySQL; UPDATE: Failure to update record

Posted on 2004-08-17
11
1,413 Views
Last Modified: 2008-01-09
The primary key is auto-incremented. I use result = ps.getGeneratedKeys(); to
obtain it and fail to update a record with the obtained primary key.

Here is the error message:
SQLException: Column not found,  message from server: "Unknown column 'thePK' in 'where clause'"
SQLState: S0022
VendorError: 1054

Here is the table:
mysql> DESCRIBE patient;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| patient_id     | bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| address_id     | bigint(20) unsigned |      | PRI | 0       |                |
| diagnosis_id   | bigint(20) unsigned |      | PRI | 0       |                |
| sdp_id         | bigint(20) unsigned |      | PRI | 0       |                |
| firstname      | varchar(20)         | YES  |     | NULL    |                |
| middlename     | char(1)             | YES  |     | NULL    |                |
| surname        | varchar(20)         | YES  |     | NULL    |                |
| date_birth     | date                | YES  |     | NULL    |                |
| gender         | varchar(10)         | YES  |     | NULL    |                |
| occupation     | varchar(15)         | YES  |     | NULL    |                |
| marital_status | varchar(10)         | YES  |     | NULL    |                |
| num_dependants | int(11)             | YES  |     | NULL    |                |
| patient_number | int(11)             | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

Here is the database handler: I'd appreciate answers also to comments marked Q: in the code

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","rmwijage", "Psa23v1");
          String sql = null;
          sql = "INSERT INTO patient( patient_id, address_id, " +
          "diagnosis_id, sdp_id, firstname, middlename, surname, " +
          "date_birth, gender, occupation, marital_status, " +
          "num_dependants, patient_number)" +
          "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
         
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave bean properties to be inserted into table patient
          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 generation with auto increment!
          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); // This value is to be updated shortly.
       
          // Insert the row (record) into the table
          int inserted = ps.executeUpdate(); // WORKS OK
          System.out.println(inserted); // Q: Can't see result in o/p window of NetBeans IDE. Why?

          /* Query the database for the last inserted id. In this case,
           * the value of patient_id hich was allocated automatically by
           * the database
           */
        // Q: Are the following lines OK for obtaining the generated Primary Key?
          ResultSet result = null;
          int thePK = -1;
          result = ps.getGeneratedKeys();
          if (result.next()) {
          thePK = result.getInt(1);
          }
          else {
            // throw an exception from here
          }
          System.out.println(thePK);
          result.close();

        // UPDATING row in table patient. I want to have patient_number = patient_id
        // Q: According to error message, here is the culprit. But isn't patient_id = thePK?
          sql = new String("UPDATE patient SET patient_number = thePK " +
                                  "WHERE patient_id = thePK");
         
          ps = conn.prepareStatement(sql);
          int update_count = ps.executeUpdate();
          System.out.println(update_count);
         
           /* 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("/success_patient.jsp"). // later dispatch
             forward(request, response);             // to rtpm_address.html
      }
      catch (Exception e){}
   }
}



// I do appreciate your expert advise. Thank you.
0
Comment
Question by:Rrugemalira
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:pat5star
ID: 11824363
I have been unable to get that working myself and suspect the driver I use does not support it. What I do to get the generated key is execute a second query to the database for the last inserted id.

I'd change your code:

// Q: Are the following lines OK for obtaining the generated Primary Key?
ResultSet result = null;
int thePK = -1;
result = ps.getGeneratedKeys();
if (result.next()) {
  thePK = result.getInt(1);
}
else {
// throw an exception from here
}
System.out.println(thePK);
result.close();

to this:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID() AS id FROM patient");
thePK = rs.getInt("id");

That guarantees you will get the last inserted key of your primary key that was created with this connection. As long as you use the same connection to insert the data and then query for the LAST_INSERT_ID it is safe no matter how many concurrent inserts are being performed.

Hope that helps,

-Pat



0
 
LVL 6

Expert Comment

by:manuel_m
ID: 11824843
you have an error in this line:

thePK has to be an int...
if you set "thePK" as an string it doesn't work, because then the variable thePK has no value

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

this should solve your problem!


original:
       // UPDATING row in table patient. I want to have patient_number = patient_id
       // Q: According to error message, here is the culprit. But isn't patient_id = thePK?
          sql = new String("UPDATE patient SET patient_number = thePK " +
                                  "WHERE patient_id = thePK");
         
0
 
LVL 3

Expert Comment

by:haobaba1
ID: 11824898
Q: Can't see result in o/p window of NetBeans IDE. Why?
I can't see why not, but try String.valueOf(inserted) and see what comes out.

// Q: Are the following lines OK for obtaining the generated Primary Key?
It looks  ok. Are you getting the results you expected? is it returning the key? I had problems with this precise function not returning correct results just a month ago or so. I used the mysql function mentioned by Pat to get the keys.

// Q: According to error message, here is the culprit. But isn't patient_id = thePK?
Are you able to verify that the record is being writen? have you tried to execute the query from the phpadmin console or from the mysql interface directly? What is the exact error you get?
0
 
LVL 3

Expert Comment

by:pat5star
ID: 11825084
I didn't notice the second question.

This code:

      // UPDATING row in table patient. I want to have patient_number = patient_id
       // Q: According to error message, here is the culprit. But isn't patient_id = thePK?
          sql = new String("UPDATE patient SET patient_number = thePK " +
                                  "WHERE patient_id = thePK");
         
          ps = conn.prepareStatement(sql);
          int update_count = ps.executeUpdate();
          System.out.println(update_count);

should be changed too:

sql = new String("UPDATE patient SET patient_number = ? WHERE patient_id = ?");
ps = conn.prepareStatement(sql);
ps.setInt(1, thePK);
ps.setInt(2, thePK);
int update_count = ps.executeUpdate();

-Pat

0
 
LVL 6

Expert Comment

by:manuel_m
ID: 11825140
         // Insert the row (record) into the table
          int inserted = ps.executeUpdate(); // WORKS OK
          System.out.println(inserted); // Q: Can't see result in o/p window of NetBeans IDE. Why?

I tried it on my eclipse console and i've got inserted = 1 as result.
but why you can't see it on your NetBeans IDE i don't know.


       // Q: Are the following lines OK for obtaining the generated Primary Key?
          ResultSet result = null;
          int thePK = -1;

should work if you change the thePK as i mentioned :)

       // Q: According to error message, here is the culprit. But isn't patient_id = thePK?
          sql = new String("UPDATE patient SET patient_number = thePK " +
                                  "WHERE patient_id = thePK");

as i mentioned above: error with the sql String!
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Rrugemalira
ID: 11825439
Yes, the problem was with:
sql = new String("UPDATE patient SET patient_number = thePK " +
                                  "WHERE patient_id = thePK");
 
Indeed, (and now I can say,  obviously!) with:
sql = new String("UPDATE patient SET patient_number = " + thePK+
                       "WHERE patient_id = " + thePK);

the previous SQLException is removed. I thank you all.

From the o/p window of the IDE I see thePK = 22 (wasn't looking well enough!)

BUT THERE IS A NEW SQLException:

1
22
SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax near 'patient_id = 22' at line 1"
SQLState: 42000
VendorError: 1064


Here is my latest code segment:

          int thePK = -1;
          ResultSet result = null;
          result = ps.getGeneratedKeys();
          if (result.next()) {
          thePK = result.getInt(1);
          }
          else {
            // throw an exception from here
          }
          System.out.println(thePK);
          result.close();
       
          sql = new String("UPDATE patient SET patient_number = " + thePK+
                       "WHERE patient_id = " + thePK);
          ps = conn.prepareStatement(sql);
          int update_count = ps.executeUpdate();
          System.out.println(update_count); // to check there was an update
         
           
          // Now close the connection    
          ps.close();
          conn.close();

Yes, I do check to see that the table is populated:
mysql> SELECT patient_id, patient_number FROM patient WHERE firstname="Johanna";
+------------+----------------+
| patient_id | patient_number |
+------------+----------------+
|         22    |              0       |
+------------+----------------+
1rows in set (0.00 sec)

patient_id != patient_number
0
 

Author Comment

by:Rrugemalira
ID: 11825702
I've also followed Pat's advice using code segment below:
BUT I get the SQLException:

1
SQLException: Before start of result set
SQLState: S1000
VendorError: 0

Here is the code segment:

        Statement stmt = conn.createStatement();
          ResultSet result = stmt.executeQuery("SELECT LAST_INSERT_ID() " +
                                                 "AS id FROM patient");
          int thePK = -1;
          thePK = result.getInt("id");
          System.out.println(thePK);
          result.close();

          sql = new String("UPDATE patient SET patient_number = ? WHERE patient_id = ?");
          ps = conn.prepareStatement(sql);
          ps.setInt(1, thePK);
          ps.setInt(2, thePK);
          int update_count = ps.executeUpdate();

          ps.close();
          conn.close();
 
0
 
LVL 6

Expert Comment

by:manuel_m
ID: 11825763
look at pat5star answer.

> sql = new String("UPDATE patient SET patient_number = ? WHERE patient_id = ?");
> ps = conn.prepareStatement(sql);
> ps.setInt(1, thePK);
> ps.setInt(2, thePK);
> int update_count = ps.executeUpdate();
>
> -Pat

he uses a prepared Statement again.

try it and it should solve the problem.

greets, Manuel
0
 
LVL 6

Assisted Solution

by:manuel_m
manuel_m earned 100 total points
ID: 11825808
take your latest code segment and change only the sql statement:

          int thePK = -1;
          ResultSet result = null;
          result = ps.getGeneratedKeys();
          if (result.next()) {
          thePK = result.getInt(1);
          }
          else {
            // throw an exception from here
          }
          System.out.println(thePK);
          result.close();
       
          // just change thePK with ?
          sql = new String("UPDATE patient SET patient_number = ? WHERE patient_id = ?");
          ps = conn.prepareStatement(sql);

          // add the prepared Statement...
          ps.setInt(1, thePK);
          ps.setInt(2, thePK);

          int update_count = ps.executeUpdate();
          System.out.println(update_count); // to check there was an update
         
           
          // Now close the connection    
          ps.close();
          conn.close();
0
 
LVL 3

Expert Comment

by:pat5star
ID: 11826146
Oh, I'm sorry. I forgot you have to call rs.next() to move the cursor to the start of the resultSet. Change this code:

          ResultSet result = stmt.executeQuery("SELECT LAST_INSERT_ID() " +
                                                 "AS id FROM patient");
          int thePK = -1;
          thePK = result.getInt("id");

to:

          ResultSet result = stmt.executeQuery("SELECT LAST_INSERT_ID() " +
                                                 "AS id FROM patient");
          int thePK = -1;
          if (rs.next()) {
            thePK = result.getInt("id");
          } else {
            // There was no result, which is a problem. Handle it somehow...
          }

-Pat
0
 
LVL 3

Accepted Solution

by:
pat5star earned 400 total points
ID: 11826181
Correction, the code should be:

          ResultSet result = stmt.executeQuery("SELECT LAST_INSERT_ID() " +
                                                 "AS id FROM patient");
          int thePK = -1;
          if (result.next()) {
            thePK = result.getInt("id");
          } else {
            // There was no result, which is a problem. Handle it somehow...
          }

I'm so used to calling my ResultSet rs, not result. That's why I originally called it rs :P

-Pat
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

A Short Story about the Best File Recovery Software – Acronis True Image 2017
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

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

15 Experts available now in Live!

Get 1:1 Help Now