3

I am using ZeosLib within Delphi to call a MySQL stored procedure through the TZQuery object. Immediately after MySQL finishes execution of the stored procedure I need to initiate my next block of code.

I added a SELECT 1; statement at the end of my MySQL stored procedure. What is the best way to find out that the results have been returned?

Johan
  • 74,508
  • 24
  • 191
  • 319
Mike Furlender
  • 3,869
  • 5
  • 47
  • 75

1 Answers1

2

Zeos lib does not play nice with stored procedures that return a result set.
It actively disables the returned resultset.
There is a workaround for that, you can activivate the resultset by altering the ZEOS source code.

However when you do that, you get lots of errors like MySQL has gone away.
At unpredictable times, which is probably why they disabled this option in the first place.

In the end the workaround I went for is to put any resultset that I need in a (temporary) table and query that after the stored procedure finishes. This solved the errors and works well for me.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • That sounds strange ... I will expect two main reasons for that: 1) In the time of MySQL 5.0 SP itself were not stable. 2) The result set with BLOB may lead to "gone away". In any case we (da-soft) do not see such reports for long time already. – da-soft Sep 04 '11 at 14:16
  • @da-soft, I hacked the source to force ZEOS lib to allow stored procedures to return a result set. That doesn't mean I did it correctly or that there is not other code that depends on stored procedures NOT returning a resultset. The problem as I remember is that stored procedures already return a resultset indicating success or failure of the stored proc. Any other resultsets are additional, and this confuses ZEOS with is build on the assumption that the returned resultset will **only ever refer to success or failure** – Johan Sep 04 '11 at 14:22
  • @da-soft what do you mean by completion status? How do you check that? – Mike Furlender Sep 04 '11 at 14:41
  • @Johan When you say "query that after the stored procedure finishes"... How do you know when its finished? – Mike Furlender Sep 04 '11 at 15:18
  • There's an event that fires after the stored proc is done. Not sure which one. AfterApplyUpdates I think. – Johan Sep 04 '11 at 21:35