Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Executing an Update correctly using a Prepared Statement

Posted on 2004-08-13
4
Medium Priority
?
160 Views
Last Modified: 2010-03-31
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

      

0
Comment
Question by:azsat
  • 2
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
pulupul earned 1500 total points
ID: 11794997
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
 

Author Comment

by:azsat
ID: 11796127
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
 
LVL 3

Expert Comment

by:pulupul
ID: 11796909
Ok good :)
0
 

Author Comment

by:azsat
ID: 11813842
That was it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

963 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