Problem with mysql and result sets in stored procedures

cokotech · Post by **cokotech** » 23.10.2007, 21:27

Hello!

Now i installed the latest svn Version of zeos, but i can't open stored procedures with result sets. In many topics is written, that it works with the latest svn and there was a code example, too.... but i only get the message "can't return a result set in given context"

My Code:

Procedure test2: (sorry i created it in navicat... so i haven't the hole code)

BEGIN
SELECT * FROM Gilden;
END

Delphi:
ZConnection1.Properties.Add('CLIENT_MULTI_STATEMENTS=1');
conn := ZConnection1.DbcConnection;
stmt := conn.CreateStatement;
ZConnection1.Connect;
if Stmt.Execute('call test2()') then
begin
Rs := Stmt.GetResultSet;
while (Rs <> nil) and (Rs.Next)
do begin
S := S + Rs.GetStringByName('PRIMARY')+'; '+#13+#10;
end;
messageBox(Self.Handle, PChar(S), 'result 1', MB_OK);
end;

Any idea???

Bye Sven!

cipto_kh · Post by **cipto_kh** » 25.10.2007, 07:49

What database you're working?
mmm.. I never do like that, usually I use TZStroredProcedure or TZQuery component

cokotech · Post by **cokotech** » 25.10.2007, 14:05

cipto_kh wrote:What database you're working?
mmm.. I never do like that, usually I use TZStroredProcedure or TZQuery component

I am using mysql, so i cannot use TSoredProcedures!

Bye Sven!

Post by **mdaems** » 25.10.2007, 19:16

Maybe you could have a look here... http://zeos.firmos.at/viewtopic.php?t=1453

Mark

cokotech · Post by **cokotech** » 25.10.2007, 19:28

mdaems wrote:Maybe you could have a look here... http://zeos.firmos.at/viewtopic.php?t=1453

Mark

No, i need a resultset as result (nice sentence).....
And i think it should works after the changes in the latest beta (test, svn or however) version!

Bye SVen!

Post by **mdaems** » 25.10.2007, 19:34

What changes do you mean?

I suppose you read the other threads on this topic? Including the one where I explain why we can't add support for this in the ZQuery component.

Mark

cokotech · Post by **cokotech** » 25.10.2007, 20:07

20070314 : Added Multiple resultset support on Dbc level - Code by Asis - See http://zeos.firmos.at/viewtopic.php?t=459 and http://zeos.firmos.at/viewtopic.php?t=912

did i understand something wrong?!?!?!?

Bye Sven!

Post by **mdaems** » 25.10.2007, 20:59

Well... That's the last patch indeed.
To make it easy :
Can you attach a small (command line?) project.
Also, include the statements to create the necessary data and procedure...
That way I can start debugging without the hassle of setting it all up myself.

Mark

cokotech · Post by **cokotech** » 26.10.2007, 08:03

Hello!

I don't know what you mean, i sent you the code a few topics upper....

CREATE DEFINER=`admin`@`%` PROCEDURE `test2`()
BEGIN
SELECT * FROM Gilden;
END

ZConnection1.Properties.Add('CLIENT_MULTI_STATEMENTS=1');
ZConnection1.Connect;
ZQuery1.SQL.Clear;
ZQuery1.SQL.add('set names ''utf8'';');
ZQuery1.ExecSQL;
ZQuery1.SQL.Clear;

conn := ZConnection1.DbcConnection;
stmt := conn.CreateStatement;
Connection1.Connect;
ZQuery1.SQL.add('CALL test2();');
Query1.open;

After this i used the code from the Threads above but i get the error message after the open command.
I can't use variables because i need more then one result.... this example is not the code i need but the right code will give me more then one result, too.

Bye Sven

cokotech · Post by **cokotech** » 26.10.2007, 08:04

Hmmmmm now it works but i dont't know why......

Bye Sven!

Post by **mdaems** » 26.10.2007, 10:44

I meant : attach a zip file of your project files (Delphi or Lazarus) and a script to install everything on the database side.
I know : code above should be enough to reproduce this kind of problems, but setting up everything for every problem is very tedious.
The more : sometimes it's just a setting in the object inspector that's wrong.
Don't forget : at this side of the web I can't see what's on your side (and in your head).

Fortunately : it works know...

Mark