Multiple result set support

[pimentel_flores]

Hi, I'm working with stored procedures and some of them will retreive more than 1 result set for example'

Code: Select all

drop procedure if exists spMensaje;;
create procedure spMensaje(
Numero varchar(10),
Msg      varchar(50)
)

begin
   select  Cast(Numero as signed) as n, concat(msg,': 1') as m; -- >First result set 
   select cast(Numero as signed)+1 as n, concat(msg,': 2') as m; -- >Second result set
end;;

to run this we use

Code: Select all

call spMensaje ('1000','Hi this is te message'); 

if I run this with a mycommand or a myscript there is no problem because this components do not retreive any data
but I need this two results

if I run this with a myquery or mystoredprocedure or mytable I get "commands out of sync, you cannot run this command now"

if I run the same in the mysql console we will have

Code: Select all

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sistema
Database changed
mysql> call spMensaje ('1000','Hi this is te message');
+------+--------------------------+
| n    | m                        |
+------+--------------------------+
| 1000 | Hi this is te message: 1 |
+------+--------------------------+
1 row in set (0.00 sec)

+------+--------------------------+
| n    | m                        |
+------+--------------------------+
| 1001 | Hi this is te message: 2 |
+------+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

as you can see the console retreives both result set,

so which component shall I use to do this

I appreciate your prompt response

[Antaeus]

Most like you prepare your stored procedure before call it. This is one of known bugs of the prepared MySQL protocol. It is described in our FAQ.

[pimentel_flores]

But, i need to have the posibility of accessing all the result sets that the stored procedure retreives, i mean what can you suggest me to have access for (in this case) the two result sets and manipulate its data or at least can read them,

is it possible to make something like this

Code: Select all

for x:= 0 to mystoredproc.resultsets.count-1 do 
begin
   assign(mystoredproc.resultset[x],dbgrid1);
   or for y:=0 to Mystoredproc.columncount -1 do
            showmessage(MyStoredproc.column[x].as Text);


end;

please advice if there is any way to do this, or please suggest something to make this possible.

[Antaeus]

Try something like this:

Code: Select all

  MyStoredProc1.FetchAll := False;
  MyStoredProc1.Open;
  while MyStoredProc1.Active do begin
    
    MyStoredProc1.OpenNext;
  end;

Do not prepare the stored procedure.