MySQL Stored Procedures And Zeos Lib

[Ðerek wildstar]

Hello Everybody!

I like MySQL and I Like ZEOS as well. They were working to me very good, but now i need a little bit mor sofistication


i have some questions about Zeos and MySQL stored Procs and Funcs

I'm setting CLIENT_MULTI_STATEMENTS=1 and i can run my stored procs on the ZQuery or ZReadOnlyQuery but after some consecutive calls the connections to server is lost...

I'm using the last Zeos LIB

Scenery:

i have a DataModule with a ZConnection. This component is initialized on the OnDataModuleCreate event.

On a form i have two ZReadOnlyQueries on a master detail relationship. The detail dataset is a Procedure like "call detail(:masterkey)". This procedure is executing an ordinary SQL command like

Code: Select all

select * from detail where masterkey = procparam

Oh the same form i have two DBGrids one with the master and another with the detail dataset

I run the program. After some navigation into the master DBGrid i get a error "MySQL lost the connection" or something like it... and after that i cant do anything else...

This is why after some browse on the Master DBGrid, the Detail procedure is called several times... i guess!

what i can do?

PS.: stored functions is working perfectly.. WITHOUT the CLIENT_MULTI_STATEMENTS=1 option setted

[mdaems]

Can you force this message? I think it has something to do with the multiple resultset processing we can't get right.

PS.: stored functions is working perfectly.. WITHOUT the CLIENT_MULTI_STATEMENTS=1 option setted

How do you use it ? Select functionname(...) from table?
That should work indeed as it returns only one resultset.

If you can replace the stored proc approach by a stored function that would be the best solution... As an oracle developer by profession the whole concept of procedures returning resultsets is unbelievable madness to me anyway. Or you select data or you execute an action. Combining the two is asking for trouble in my opinion.

Mark

[v-maguryan]

This way is not correctly.

The BUG in source code ZEOS library in unit ZDbcMySqlResultSet.

Try to change this source code and then try to test STORED PROCEDURES:

Code: Select all

procedure TZMySQLResultSet.Close;
begin
  if FQueryHandle <> nil then begin
    FPlainDriver.FreeResult(FQueryHandle);
    while FPlainDriver.RetrieveNextRowset( FHandle) = 0 do begin
      FQueryHandle := FPlainDriver.StoreResult(FHandle);
      if Assigned(FQueryHandle) then
        FPlainDriver.FreeResult(FQueryHandle);
    end;
  end;
  FQueryHandle := nil;
  FRowHandle := nil;
  inherited Close;
end;