How does one insert a column value into an existing record?

[Steve Howerton]

Hello
I was trying to insert a value into column of my table for the first time.

There is no exception caught or thrown.
but clearly the rowcount on the update is not a value.
Using Apache derby embedded database.

my DAO () just ignores prepared statement because the syntax must be wrong.

How does one insert a column value into an existing record?

prepared statement in question:

CLASS 
 public class ConnectBookingDAO extends MasterForm implements BookingDAO {: 
deleteBooking() : INSERT INTO booking (book_status) VALUES(?): insertNewStatus: 
DONE: if (SwingWorker.StateValue.DONE == evt.getNewValue()) { : 

Example of the columns of the table. Please find the book_status as the value I would like to update.

public class ConnectBookingDAO extends MasterForm implements BookingDAO {: 
 -->  public static boolean load(2) var: (int num)<-- 
 : SELECT
     book_num,
    instr_uid,
    stu_uid,
instructor_password,
    student_password,
     payee_password,
    book_location,
    payee_uid,
    book_start_date,
    book_end_date,
    book_start_time,
    book_end_time,
    book_instrument_type,
    book_cancel_date,
    book_init_date,
    book_refund,
    instr_pay_rate,
    instr_rate,
    initiated,
    finalized,
    book_status,
    payment,
    pymt
 FROM booking WHERE book_num=?

DAO () called:

 public boolean insertNewStatus(String status) throws UnknownUserNameException, SQLException, IncorrectPasswordException, LoginException,
                                                      FileNotFoundException, IOException, ProfileException, InterruptedException {
       
         boolean bInserted=true;   
    
        setTableName(tableName);   
     
        
            conn = connect();
           
            ps = (PreparedStatement) conn.prepareStatement(
                    ModelUtils.getXMLResource("insertNewStatus"));
            ps.setString(1, status);            

           int rowCount = ps.executeUpdate();<-----------[b]stops here[/b]
        if (rowCount != 1) {
            bInserted = false;
            
        }
    
         return bInserted;
    }

[Rob Spoor]

Check out how the UPDATE statement works. You need that one to modify any column of existing records. INSERT is only used to create new records.

[Steve Howerton]

Thanks that was the answer.

public class ConnectBookingDAO extends MasterForm implements BookingDAO {: 
 --> in  public boolean insertNewStatus(EMINENT) var: (String status)<-- 
 : UPDATE booking SET book_status =?: insertNewStatus: 
error message is:  : Mapping Records Adjusted! :  AT// CLASS 

[Rob Spoor]

You're welcome.

[Steve Howerton]

Is there way to set a param and a proper sql statement to change the value as shown above if I send in the PK ? I noticed my statement now is changing all the records.
I would like to change only one using the primary key.

<entry key="insertNewStatus">UPDATE booking SET book_status=? WHERE record_num=?</entry>

I tried to bring in the PK and set it but I have a few bugs to work out before I can confirm it works or not.

 public boolean insertNewStatus(String status,int bookNum) throws UnknownUserNameException, SQLException, IncorrectPasswordException, LoginException,   
                                                      FileNotFoundException, IOException, ProfileException, InterruptedException {   
          
         boolean bInserted=true;      
       
        setTableName(tableName);      
        
           
            conn = connect();   
              
            ps = (PreparedStatement) conn.prepareStatement(   
                    ModelUtils.getXMLResource("insertNewStatus"));   
            ps.setString(1, status);          
ps.setInt(2,bookNum);     
  
           ps.executeUpdate();
              
         return bInserted;   
    }  

 

Is there some code to confirm that something was updated that can be used here?

[Rob Spoor]

executeUpdate returns the number of records that were updated (or inserted / deleted with INSERT / DELETE statements). If that's >0 then you have an updated record.

[Steve Howerton]

I was able to update records and change book_status.

 
<entry key="insertNewStatus">UPDATE booking SET book_status=? </entry>  

Is my code setting the

WHERE record_num=?

I do not understand how to set the additional param 'bookNum' .

 
<entry key="insertNewStatus">UPDATE booking SET book_status=? WHERE record_num=?</entry>  

thanks

[Paul Clapham]

Have you considered the possibility that setting the second parameter would be very much like setting the first parameter? Which you have code for.

[Steve Howerton]

I was hoping this to be true. I wanted to eliminate this last step as being the error.
I am in the middle of adding functionality and devising a test and on top of that
I have realized a global var problem concerning MVC2 model design. I will
Add this back in and try to finish debugging this mess.
Thanks