[Zope] Determining Success or Failure status on SQL update in ZSQL Metho d d

[Ben Glazer]

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 )
>
>