Solved

Executing an Update correctly using a Prepared Statement

Posted on 2004-08-13
4
157 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Suggested Courses

630 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