RE: [Zope] Determining Success or Failure status on SQL update in ZSQL Metho d d
Thanks for the response. It sounds right to me, but the sql method seems to return an object. My skill with Zope or Python is not quite at the level where I know how to interrogate this object for a record count. When I use the following test code (where upd is the id of the SQLMethod) <dtml-let foo="upd()"> foo is <dtml-var foo> </dtml-let> I get back foo is <Shared.DC.ZRDB.RDB.DatabaseResults instance at e7fef0> Right now I am trying out a SELECT with a <dtml-raise> prior to issuing the update. It's non-atomic and thus not ideal, but is an acceptable workaround until I can find a production worthy solution. I am still open to any and all suggestions. BTW, once I figure this out, I am definitely going to write it up. Would this be a tip or a howto? --ira -----Original Message----- From: Matthew Parslow [mailto:matt@umd.com.au] Sent: Wednesday, February 16, 2000 11:33 PM To: Ira Hochman; zope@zope.org Subject: Re: [Zope] Determining Success or Failure status on SQL update in ZSQL Metho d d what about <dtml-let "rows_affected=sql_query(params=param)">? and then check if rows_affected is >0 Regards, Matthew Parslow On Thu, 17 Feb 2000, you wrote:
Hello,
A search of prior discussion threads shows this question occasionally appears without resolution. Perhaps someone has found an answer to it since then, so here it is...
Using a ZSQL method, how do I detect if any rows have been affected when performing a SQL UPDATE. I do not need to know how many rows, just whether the statement affected 0 rows or more than 0 rows.
My environment is Oracle (7 & 8) and with other programmatic interfaces to Oracle, one can capture a (non-error) return status that indicates if no rows were affected. This is supported in the ODBC API for Oracle and other databases as well. Is there a way to capture this return status even though it is not flagged as an error?
No dtml-try since it's only a return code, not an error code
As a workaround I've tried to do a select count, but to do this I need to 1) execute the select and update in the same transaction and 2) run the select before the update, store the value, then return it at the end (the update may or will change at least one of the fields referenced in the where clause, so I cannot run it after)
So, inside of a SQL method, I can do #1, but cannot see a way to do #2.
Is there a secret trick to this or is it a case of adding code to the existing DAs? Please please tell me it's the former.
Thanks in advance, Ira
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
After a quick scan of the Zope source, I don't think it's possible using raw DTML. After a quick scan of the Python code implementing the MySQL API, I don't think it's possible using the current Python interface. However, the Python extension code (written in C) DOES seem to return the number of rows affected whenever the query doesn't return a result. In reality, I don't know enough about writing extensions to Python to be certain about this, but it sure looks like I'm right. ;) Perhaps Anthony Baxter can comment further with some accurate information. One related note: if you're actually returning rows from a SELECT, you should be able to return the number of rows with something like: <dtml-let query_results="sqlSelect()"> foo returned <dtml-var "_.len(query_results)> rows </dtml-let> Regards, Ben
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Ira Hochman Sent: Thursday, February 17, 2000 12:25 AM To: 'Matthew Parslow'; zope@zope.org Subject: RE: [Zope] Determining Success or Failure status on SQL update in ZSQL Metho d d
Thanks for the response. It sounds right to me, but the sql method seems to return an object. My skill with Zope or Python is not quite at the level where I know how to interrogate this object for a record count.
When I use the following test code (where upd is the id of the SQLMethod)
<dtml-let foo="upd()"> foo is <dtml-var foo> </dtml-let>
I get back
foo is <Shared.DC.ZRDB.RDB.DatabaseResults instance at e7fef0>
Right now I am trying out a SELECT with a <dtml-raise> prior to issuing the update. It's non-atomic and thus not ideal, but is an acceptable workaround until I can find a production worthy solution.
I am still open to any and all suggestions.
BTW, once I figure this out, I am definitely going to write it up. Would this be a tip or a howto?
--ira
-----Original Message----- From: Matthew Parslow [mailto:matt@umd.com.au] Sent: Wednesday, February 16, 2000 11:33 PM To: Ira Hochman; zope@zope.org Subject: Re: [Zope] Determining Success or Failure status on SQL update in ZSQL Metho d d
what about <dtml-let "rows_affected=sql_query(params=param)">? and then check if rows_affected is >0
Regards, Matthew Parslow
On Thu, 17 Feb 2000, you wrote:
Hello,
A search of prior discussion threads shows this question occasionally appears without resolution. Perhaps someone has found an answer to it since then, so here it is...
Using a ZSQL method, how do I detect if any rows have been affected when performing a SQL UPDATE. I do not need to know how many rows, just whether the statement affected 0 rows or more than 0 rows.
My environment is Oracle (7 & 8) and with other programmatic interfaces to Oracle, one can capture a (non-error) return status that indicates if no rows were affected. This is supported in the ODBC API for Oracle and other databases as well. Is there a way to capture this return status even though it is not flagged as an error?
No dtml-try since it's only a return code, not an error code
As a workaround I've tried to do a select count, but to do this I need to 1) execute the select and update in the same transaction and 2) run the select before the update, store the value, then return it at the end (the update may or will change at least one of the fields referenced in the where clause, so I cannot run it after)
So, inside of a SQL method, I can do #1, but cannot see a way to do #2.
Is there a secret trick to this or is it a case of adding code to the existing DAs? Please please tell me it's the former.
Thanks in advance, Ira
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
participants (2)
-
Ben Glazer -
Ira Hochman