executeUpdate() returning unexpected results

[M Burke]

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]

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

[Avi Abrami]

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:

java.sql.Connection c = // However you get it.
java.sql.CallableStatement cs = c.prepareCall("{call [I]your_delete_procedure[/I]()}");
int result = cs.executeUpdate();

Good Luck,
Avi.

[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...

[M Burke]

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]

java.sql.Connection c = // However you get it.
java.sql.CallableStatement cs = c.prepareCall("{call [I]your_delete_procedure[/I]()}");
int result = cs.executeUpdate();

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]

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]

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:

String sql = "DELETE stock_transfer_line_comment " +
"WHERE transfer_number = ? AND transfer_line_number = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(2, tranID);
ps.setInt(3, tranNum);
int rowsDeleted = ps.executeUpdate();

Good Luck,
Avi.

[M Burke]

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]

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.]

create or replace function DELETE_COMMENT (
 transfer_number_i in varchar2,
transfer_line_number_i in number)
return number
IS
BEGIN
 
DELETE FROM stock_transfer_line_comment
WHERE transfer_number = transfer_number_i
AND transfer_line_number = transfer_line_number_i;
return sql%rowcount;
end;

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]

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.