Error handling in zope/ ext method calling a sproc to insert data into sql db?
I'm working on adding detailed error handling to an existing zope web application. The web application (a series of web pages served by zope) includes an html form that inserts inputted data (and data in hidden fields) into a sql database. The form currently submits the data to a dtml document that uses a Z SQL method to do the insert <dtml-in expr="zsql_nameofmethod(param1=1,param2=2, etc...). The Z SQL method, zsql_nameofmethod, is actually a simple stored procedure call (exec storedprocedurename ). Currently there is no error handling and all submissions seem to "go through," even when the sproc insert fails. Thus, I need to add custom error_handling to display an appropriate error message to the front-end user when the sproc call fails to insert. I've searched the web for any kind of help but have not found much to go on. A simple <dtml-try><dtml-except> block doesn't catch any error from the sproc. In the past I've taken the sproc call out of zope and put it into an external method (python). I've gotten more detailed error handling to work successfully from the external method by raising a RuntimeError and formatting what I get from sys.exc_info(). However, is there a better way to handle errors with zope/python/sql databases from within zope rather than in external methods? running Zope 2.5.1 (Python 2.1.3) on Win2k connecting to SQL database. External methods connected to database using win32com.client.Dispatch('ADODB.Connection')... Alisa __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
Alisa K wrote at 2003-1-15 12:13 -0800:
.... data entered in SQL database through stored procedure ... Currently there is no error handling and all submissions seem to "go through," even when the sproc insert fails. Then "sproc" does something wrong. It should raise an exception when it fails.
Otherwise, you have little change to handle the failure. Your "sproc" must somehow tell whether or not it failed. Dieter
I'm glad to get a response on this- I still have a question... --- Dieter Maurer <dieter@handshake.de> wrote:
Alisa K wrote at 2003-1-15 12:13 -0800:
.... data entered in SQL database through stored procedure ... Currently there is no error handling and all submissions seem to "go through," even when the sproc insert fails.
Then "sproc" does something wrong. It should raise an exception when it fails.
The sql stored procedure ("sproc") I am using to insert data apparently sends a return code (a numerical code) no matter if it successfully inserts data into the database or fails due to a conflict of business rules. I think this return code (ex: return ireturncode = 115 ) is seen/returned when calling the sproc from sql Query Analyzer. However, when I call it from zope (zsql method) or from a python external method I don't seem to trap this return code. I see nothing-
Your "sproc" must somehow tell whether or not it failed.
Is this return code enough of an indication that the sql stored procedure failed?? Am I simply not trapping it properly from zope? I hope to test your suggestion of raising an exception from inside the sproc instead of sending a numerical return code. Thanks, Alisa __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
Alisa K wrote at 2003-1-17 16:00 -0800:
... The sql stored procedure ("sproc") I am using to insert data apparently sends a return code (a numerical code) When you call the stored procedure via a Z SQL Method, the return code is lost.
Some DAs (Database Adapter) provide a special method to call stored procedures. They may give back the return code. However, I do not have much experience with this. You may wrap the call to your stored procedure in a "select" and then get the return code back: select <call_to_your_stored_procedure> as RETVAL When you call this in a Z SQL Method, the result will behave like a sequence with one element. The "RETVAL" attribute of this element is the return code of your stored procedure. Dieter
participants (2)
-
Alisa K -
Dieter Maurer