Re: Need Help w/ Stored Procedures Error Handling

> Question regarding error handling when using stored procedures. If I
> have a procedure usp_delete_database_data which takes 3 parameters
> @dbID int, @error_no int output and @error_desc varchar(255) output,
> and I call this procedure using something as follows:

> try:
>     varTest =
> c.callproc('usp_delete_database_data',{'@dbID':varDBID,'@error_no':varErrorNo,'@error_desc':varErrorDesc})
> except Sybase.DatabaseError:
>     print "Unable to delete database data for database_id"
> else:
>     print "Success"
> 
> Is there any way to capture the error_no and error_desc return
> variables? If I simply call the procedure using c.callproc outside
> of a try block and an SQL error occurs within the procedure, Python
> raises the Sybase.DatabaseError and the script blows up. When inside
> the try block the varErrorNo and varErrorDesc are not populated. I'm
> assuming this is because the c.callproc() doesn't really run
> successfully because of the Sybase.DatabaseError that is occurring?
> The procedure error that I am testing is a simple 2601 duplicate key
> insert error. I need the c.callproc() to run successfully and then
> be able to capture the error_no and error_desc outputs. Perhaps I'm
> missing something? Thanks

It is possible to retrieve the named arguments in the lower level
extension module.  Look at the rpc.py/rpc.sql in the examples
subdirectory.

Problem is that you need to set the status of the return CS_DATAFMT
(via DataBuf) to CS_RETURN otherwise the CT library does not know you
want to receive output parameters in the buffer.  If there was a nice
way to work out which of the parameters you passed in your Python are
for output then it should be possible to handle the above code.

A while ago I tried setting CS_RETURN in the status of all parameter
values in the Cursor.callproc() method.  I seem to remember it didn't
work.  I should five it another try.

Note that the rpc.py only seems to work for Sybase client libraries.
FreeTDS still seems to have problems.

- Dave

-- 
http://www.object-craft.com.au