• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

executeUpdate() returning unexpected results

 
Ranch Hand
Posts: 434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a stored procedure in Oracle 9 to delete a specific record. If the record is there, (CallableStatement)executeUpdate() deletes the record and returns the number of rows affected (I think?), which is alwasy 1 in this case.

But if the record is not found, no delete occurs. But executeUpdate() still returns 1. So how can I tell if the record was not found, but no SQLException was thrown?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Odd. It should return 0. Are you using a standard driver?
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
M,
It is unclear to me, what you are doing. Perhaps you could post the entire, relevant section of your code. Are you doing something like this:

Good Luck,
Avi.
 
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there maybe a value eplicitly returned by the stored procedure, maybe to indicate Success (no SQL error) or Failure, rather than the number of rows affected by the statement? If you had a normal Statement or PreparedStatement, I think you can count on that return value's meaning. I'm not sure, with a CallableStatement referencing a stored procedure...
 
M Burke
Ranch Hand
Posts: 434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jeanne Boyarsky:
Odd. It should return 0. Are you using a standard driver?



Yes, it's a standerd Oracle driver, Jeanne
 
M Burke
Ranch Hand
Posts: 434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Good Luck,
Avi.[/qb]<hr></blockquote>

The calls are a bit spread out among methods. But here they are

Context ctx = new InitialContext();
ds = (DataSource)ctx.lookup(Constants.JDBCCONN);

Connection conn = ds.getConnection();

callStmt = conn.prepareCall("begin pkg_stop_maint_details.pp_delete_comment_line(?, ?, ?); end;");

callStmt.registerOutParameter (1, OracleTypes.CURSOR);
callStmt.setString(2, tranID);
callStmt.setInt(3, tranNum);

rc = callStmt.executeUpdate();

this is where rc alwasy returns 1.


Stored Proc...

PROCEDURE delete_comment(results_o out refcursor,
transfer_number_i in varchar2,
transfer_line_number_i in number)
IS
BEGIN

DELETE FROM stock_transfer_line_comment
WHERE transfer_number = transfer_number_i
AND transfer_line_number = transfer_line_number_i;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END delete_comment;

[ April 14, 2005: Message edited by: M Burke ]
[ April 14, 2005: Message edited by: M Burke ]
 
M Burke
Ranch Hand
Posts: 434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jon Egan:
Is there maybe a value eplicitly returned by the stored procedure, maybe to indicate Success (no SQL error) or Failure, rather than the number of rows affected by the statement? If you had a normal Statement or PreparedStatement, I think you can count on that return value's meaning. I'm not sure, with a CallableStatement referencing a stored procedure...




That is what I am thinking. I have no idea what Oracle is doing under the covers.
 
Avi Abrami
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
M,
If you want to know how many rows were deleted by the stored procedure, then you have to get the stored procedure to return that number. Calling the stored procedure via the "executeUpdate()" method will not get you that information. When invoking stored procedures you need to use the "execute()" method (and not "executeUpdate()" -- even though it successfully executes the stored procedure).

From what you have posted, the only thing your stored procedure does is a DELETE operation. You do not use the OUT parameter at all. So why not just invoke the DELETE operation directly? What would be wrong with:

Good Luck,
Avi.
 
M Burke
Ranch Hand
Posts: 434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I would not use the stored proc. But the Architects and dba's demand it. The sp's make it hard to handle support.

So how do I get the sp to return the rows count using OUT?
[ April 15, 2005: Message edited by: M Burke ]
 
Avi Abrami
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
M,


So how do I get the sp to return the rows count using OUT?


Well, RTFM will answer your question. I suggest the "PL/SQL User's Guide and Reference", which is available from:

http://tahiti.oracle.com

If you only want to return a single value, I recommend a stored function -- and not a stored procedure. For example:
[Note: Uncompiled and untested. Please verify via documentation.]

I guess it's none of my business, but if the DBA's demand it, then why can't they show you how to do it? (Don't they know how to write PL/SQL?)

Good Luck,
Avi.
[ April 16, 2005: Message edited by: Avi Abrami ]
 
M Burke
Ranch Hand
Posts: 434
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, the dba's we have are not very competent. We have to do many things on our own.
Thanks for the tip using functions. I will have to see if I can get that approach approved.
 
reply
    Bookmark Topic Watch Topic
  • New Topic