Page 1 of 1
[solved] MyStoredProc: Returning rows from an SP
Posted: Tue 17 Nov 2009 11:36
by 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.
Posted: Wed 18 Nov 2009 13:18
by Dimon
TMyStoredProc is a descendant of the TCustomDADataSet class, therefore you get access to resultset in the same way as if you use TMyQuery.
Posted: Thu 19 Nov 2009 06:59
by sean
Dimon wrote: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?
Posted: Thu 19 Nov 2009 07:55
by 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.
Posted: Fri 20 Nov 2009 07:37
by 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
Posted: Fri 20 Nov 2009 12:49
by 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;
Posted: Fri 20 Nov 2009 15:12
by 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 ?
Posted: Fri 20 Nov 2009 15:29
by 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;
Posted: Fri 20 Nov 2009 15:53
by sean
Thanks for your patience, its perfect.
Hopefully the simple example will help others who search.
Posted: Wed 02 Dec 2009 08:46
by jkuiper
Maybe a tip from my site:
if you're using this
Delphi shows the same name as field as the string.
When using
you also can use fieldbyname()
Posted: Wed 02 Dec 2009 21:43
by sean
Good point. Thanks.