Solved

Executing an Update correctly using a Prepared Statement

Posted on 2004-08-13
4
152 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 500 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

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

23 Experts available now in Live!

Get 1:1 Help Now