[Zope-DB]found reason: sql query works in database connector but not in ZSQL method

[Charlie Clark]

Am 19.01.2007, 17:50 Uhr, schrieb robert rottermann <robert at redcor.ch>:

> it in deed seems not to work.
> however I do not understand why.

If you use callproc() we could probably help you.

> This is now academical as I resolved to not to use stored procedures
> that return a record set (tough its a pain in the back).
> what I do (did):

> 1. write data (in a ZSQL connction), did commit it, did flush the table.
> (reading from plain mysql shows the data)
> 2. in a "python modoul" I
>  - flush the tables (to force a reread)
>  - read the data
>  - send it to plone to display
> unfortunately I get stale data.
> As we need to go online next week, I resolved to not to use Stored
> Procedure to retrieve the data.
> However I really like to know why I cant.

Don't forget that Zope will also cache results from the database as indeed  
the ODBC driver may. You should really try this using a single connection  
object.
call the procedure + get the results if possible
if not call the results with an additional query
run the query that uses the results of stored procedure.

You can do all this in a single ExternalMethod.

Charlie