[solved] MyStoredProc: Returning rows from an SP

[sean]

How can one get return rows from a SP in Delphi?

For example with this SP:

Code: Select all

delimiter  //
DROP PROCEDURE IF EXISTS test3//
CREATE PROCEDURE `test3`(IN v1 INT, OUT v2 DOUBLE) READS SQL DATA
BEGIN
  SET v2= v1 + 10.02;
  select "hello", "there";
END;
//

When executed on the mysql command line, it will print "hello there"

Code: Select all

mysql> call test3(27901, @v2);
+-------+-------+
| hello | there |
+-------+-------+
| hello | there |
+-------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

How can this be done using MyStoredProc? ParamByName and FieldByName do not seem appropriate.

According to http://dev.mysql.com/doc/refman/5.0/en/ ... procs.html
mysql SPs can return result sets.

Thanks in advance.

[Dimon]

TMyStoredProc is a descendant of the TCustomDADataSet class, therefore you get access to resultset in the same way as if you use TMyQuery.

[sean]

TMyStoredProc is a descendant of the TCustomDADataSet class, therefore you get access to resultset in the same way as if you use TMyQuery.

My delphi code is:

Code: Select all

  with MyStoredProc1 do begin
    try
      Close;
      StoredProcName:='test3';
      ParamByName('v1').AsInteger:=10;
      Execute;
      meCostMisc.Lines.Add('v2=' +FieldByName('@v2').AsString);
    finally
      MyStoredProc1.Close;
    end;
  end;

Now, before even trying to iterate though results, If the SP test3 contains the line
select "hello", "there";
Then when the above delphi code is executed, it gives an error about @v2 not found.
If the select line is removed, @v2 is correctly return and printed as 20.02.

Hence is looks to me like rows return by an SP are not correctly handled?

[Dimon]

The point is that when stored procedure returns one or multiple result sets, then you should call the OpenNext method to get all datasets, and out parameters will be set after opening all of them.

[sean]

The code is now:

Code: Select all

  with MyStoredProc1 do begin
    try
      Close;
      StoredProcName:='test3';
      FetchAll := False;
      ParamByName('v1').AsInteger:=10;
      Execute;
      while Active do begin
        ShowMessage('row');
        OpenNext;
      end;
      ShowMessage('v2=' +FieldByName('@v2').AsString);
    finally
      MyStoredProc1.Close;
    end;
  end;

This prints out "row" once, then complains that it cannot find "v2".
Questions:
- What is wrong in the above logic so that first the row by row output from the procedure is printed, then the OUT parameter "v2" is also printed?
- How do I find out the column names/count (to replace the ShowMessage('row') above

Thanks in advance

[Dimon]

Try to use the following code:

Code: Select all

var
  i: Integer;
  s: string;
begin
  with MyStoredProc1 do begin
    try
      Close;
      StoredProcName:='test3';
      FetchAll := False;
      ParamByName('v1').AsInteger:=10;
      Execute;
      while not EOF do begin
        s := '';
        for i := 0 to Fields.Count - 1 do
          s := s + Fields[i].FieldName + '= ' + Fields[i].AsString + #13#10;
        ShowMessage(s);
        Next;
      end;

      OpenNext;
      ShowMessage('v2=' +FieldByName('@v2').AsString);
    finally
      MyStoredProc1.Close;
    end;
  end;
end;

[sean]

Your example is useful, thanks.
It prints out "Hello=hello there=there",
which corresponds to the output of "select hello there " in test3().

However, on clicking OK, it then says "@v2 not found", so how is one supposed to access the out parameter?

If in addition, test3() is modified to print two rows:

Code: Select all

CREATE PROCEDURE `test3`(IN v1 INT, OUT v2 DOUBLE) READS SQL DATA
BEGIN
  SET v2= v1 + 10.02;
  select "row1";
  select "row2";
END;

Then is shows row1=row2, and then @v2 not found.
Why does it not also print row=row2 ?

[Dimon]

To solve the problem use the following code:

Code: Select all

var
  i: Integer;
  s: string;
begin
  with MyStoredProc1 do begin
    try
      Close;
      StoredProcName:='test3';
      FetchAll := False;
      ParamByName('v1').AsInteger:=10;
      Execute;
      while Active do begin
        while not EOF do begin
          s := '';
          for i := 0 to Fields.Count - 1 do
            s := s + Fields[i].FieldName + '= ' + Fields[i].AsString + #13#10;
          ShowMessage(s);
          Next;
        end;

        OpenNext;
      end;

      ShowMessage('v2=' + ParamByName('v2').AsString);
    finally
      MyStoredProc1.Close;
    end;
  end;
end;

[sean]

Thanks for your patience, its perfect.
Hopefully the simple example will help others who search.

[jkuiper]

Maybe a tip from my site:

if you're using this

Code: Select all

SELECT 'this is a test';

Delphi shows the same name as field as the string.

When using

Code: Select all

SELECT 'this is a test' AS arow;

you also can use fieldbyname()

[sean]

Good point. Thanks.