File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes executeUpdate() returning unexpected results Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "executeUpdate() returning unexpected results" Watch "executeUpdate() returning unexpected results" New topic
Author

executeUpdate() returning unexpected results

M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 388
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?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

Odd. It should return 0. Are you using a standard driver?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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.
Jon Egan
Ranch Hand

Joined: Mar 24, 2004
Posts: 83
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

Joined: Jun 25, 2004
Posts: 388
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

Joined: Jun 25, 2004
Posts: 388

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

Joined: Jun 25, 2004
Posts: 388
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

Joined: Oct 11, 2000
Posts: 1121

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

Joined: Jun 25, 2004
Posts: 388
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

Joined: Oct 11, 2000
Posts: 1121

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

Joined: Jun 25, 2004
Posts: 388
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: executeUpdate() returning unexpected results
 
Similar Threads
Eclipselink problem with MySQL Stored Procedures
Checking if a record exists
update button
Constraint on table that prohibits record inserts using web based app(JAVA)
How does one insert a column value into an existing record?