Executing an Update correctly using a Prepared Statement

Hi I have the following code which includes the definitions,prepared stmt and its method
I'm guessing that I'm not setting the ? values of the prepared stmt correctly,
I get a return code of 0 which is incorrect.

please advise:



// at begining of my class I declare the string for the SQL

      final static String sQlstring9  = "UPDATE PRICE_TEST " +
      "SET CLEANMID = ?,  MODIFIEDBY = ?, MODIFIEDDATE = ?,   VERSION = VERSION    +1  " +
               "WHERE INSTID = ?";                                                    
 // I have method performUpdates  which is called by main()

in main() I have

ps6 = conn.prepareStatement(sQlstring9);

then I call performUpdates

retCode = performUpdates(instId,priceDate,cleanPrice,dbUser,ps6);


 //my performUpdates method

private static int  performUpdates(
                                                            long instID,
                                                            long priceDate,
                                                            double cleanPrice,                                                                                           String dbUser,                         
                                                            PreparedStatement pstmt6            
                                                            )  
      {
            int sqlResult = 0;
            
            try
            {      
                  // sqlResult > 0 implies a successful operation (signifies a rows affected rowcount )
                                       

                        // update price row
                        System.out.println("this instId");
                        System.out.println(instID);      
                        System.out.println("this cleanPrice");
                        System.out.println(cleanPrice);
                        System.out.println("this dbUser");
                        System.out.println(dbUser);
                        System.out.println("this date");
                        System.out.println(new java.sql.Date(System.currentTimeMillis()));
                        
                        pstmt6.clearParameters();
                        pstmt6.setDouble(1,cleanPrice);
                        pstmt6.setString(2,dbUser);
                        pstmt6.setDate(3, new java.sql.Date(System.currentTimeMillis()));            
                        pstmt6.setLong(4,instID);                        
                        sqlResult = pstmt6.executeUpdate();
                        System.out.println("this SQLRET CODE AFTER PRICE UPD: ");
                        System.out.println(sqlResult);
                        
                  
            }
            catch(SQLException e)
            {  
                  
                  System.err.println("SQLException caught during inserts : " + e.getMessage());
                  e.printStackTrace();
            }
            catch (Exception E)
            {
                  System.err.println(E.getMessage());
                  E.printStackTrace();
            }      

            return sqlResult;
      }

*************************************************
When I run the class In get:
***************************************************
 this instId
89666
this cleanPrice
991250.0
this dbUser
test_dbo
this date
2004-08-13
this SQLRET CODE AFTER PRICE UPD:
0

      

azsatAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pulupulConnect With a Mentor Commented:
Hi.
I have been looking at your code. executeUpdate returns (from Java API): either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for SQL statements that return nothing.
In your case returns 0, and being an UPDATE statement, it means it hasn't found any rows that match "WHERE INSTID = ?" for the instid you pass to performUpdates.
So the code is well, you need to check what the value of instid at that point, and check that there is actually rows in the database with that instid in the table PRICE_TEST.
Hope it helps.
0
 
azsatAuthor Commented:
Hi,

Thanks for that.

 "it means it hasn't found any rows that match "WHERE INSTID = ?" for the instid you pass to perform Updates."

Hmm , you helped me think about the output again. You are probably right, looking at the println stmts again I see

this instId
89666

I don't recall seeing a value this high in the DB!  so it's possible that the value passed is incorrect.  I won't be able to verify this until Monday, as I've left work for the weekend.

Will advise.
0
 
pulupulCommented:
Ok good :)
0
 
azsatAuthor Commented:
That was it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.