Get OUT Param with TMyCommand

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
davor.TCS
Posts: 29
Joined: Thu 05 Apr 2012 22:10
Contact:

Get OUT Param with TMyCommand

Post by davor.TCS » Wed 06 Jan 2021 21:29

I am using TMyComand with two IN parameters, but I can not get result into OUT parameter. How can I do this?

In TMyCommand as SQL I have this:
SELECT SUM(table_count.MyNumber) INTO @TblRecNo
FROM
(SELECT COUNT(*) AS MyNumber FROM :TblName1 UNION ALL SELECT COUNT(*) AS MyNumber FROM :TblName2) table_count;

-- this does not work
SELECT @TblRecNo INTO :TotalCount;
-- or
SET :TotalCount = @TblRecNo;
In Delphi I have something like this (DM.GetRecCount is TMyCommand):
begin
DM.GetRecCount.ParamByName('TblName1').AsString:='EventsTbl';
DM.GetRecCount.ParamByName('TblName2').AsString:='ChangedEventsTbl';
DM.GetRecCount.Execute;
ShowMessage(IntToStr(DM.GetRecCount.ParamByName('TotalCount').AsInteger));
end;

davidmarcus
Posts: 50
Joined: Tue 25 Jan 2005 11:22
Location: Somerville, MA
Contact:

Re: Get OUT Param with TMyCommand

Post by davidmarcus » Thu 07 Jan 2021 13:47

select sum(...) as Foo ...

DM.GetRecCount.FieldByName( 'Foo' ).AsInteger

davor.TCS
Posts: 29
Joined: Thu 05 Apr 2012 22:10
Contact:

Re: Get OUT Param with TMyCommand

Post by davor.TCS » Fri 08 Jan 2021 12:20

TMyCommand does not have FieldByName property...
Params are usually set with :ParamName ...

davidmarcus
Posts: 50
Joined: Tue 25 Jan 2005 11:22
Location: Somerville, MA
Contact:

Re: Get OUT Param with TMyCommand

Post by davidmarcus » Fri 08 Jan 2021 14:17

Use TMyQuery.

davor.TCS
Posts: 29
Joined: Thu 05 Apr 2012 22:10
Contact:

Re: Get OUT Param with TMyCommand

Post by davor.TCS » Sat 09 Jan 2021 15:57

Thank you for answer, but this does not solve my question on how to get OUT param with TMyCommand.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Get OUT Param with TMyCommand

Post by ViktorV » Mon 11 Jan 2021 19:18

1. You will not be able to execute a query like 'select * from: param' because MySQL server does not allow specifying a table name as a parameter. Therefore, such a request will generate an error both in the TMyCommand component and in TMyQuery.
To solve your task, you can use Macros. For example:

Code: Select all

var
  TableName: string;
...
  MyQuery.SQL.Text: = 'select * from (&TableName)';
  TableName: = 'Dept';
  MyQuery.MacroByName ('TableName'). Value: = TableName;
  MyQuery.Open;
You can learn more about Macros at: http://www.devart.com/mydac/docs/work_macros.htm
2. When executing the SELECT ... INTO variables statement, the MySQL server does not automatically return the values ​​of the variables. You can verify this by running the queries you have provided using the standard means, for example, mysql.exe Command-Line Client.
To get their values, you should run a query like SELECT @Var; and in this case the server will return a string. As written in the MyDAC documentation https://www.devart.com/mydac/docs/deva ... ommand.htm - A component for execution of SQL statements and stored procedures which do not return rowsets - therefore you cannot use this component for solving the problem. you should use the TMyQuery component.

davor.TCS
Posts: 29
Joined: Thu 05 Apr 2012 22:10
Contact:

Re: Get OUT Param with TMyCommand

Post by davor.TCS » Wed 13 Jan 2021 11:52

Thank you and @davidmarcus for responces, I finally used TMyQuery with defined field MyRecNo as Float.

In TMyQuery as SQL now I have this:
SELECT SUM(table_count.MyNumber) AS TblRecNo
FROM
(SELECT COUNT(*) AS MyNumber FROM &TblName1 UNION ALL SELECT COUNT(*) AS MyNumber FROM &TblName2 ) table_count;
In Delphi now I have (DM.GetRecCount is now TMyQuery):
DM.GetRecCount.MacroByName('TblName1').Value:='EventsTbl'; //<- if you do AsString it will be quoted!
DM.GetRecCount.MacroByName('TblName2').Value:='ChangedEventsTbl';
DM.GetRecCount.Execute;
ShowMessage(DM.GetRecCount.FieldByName('TblRecNo').AsString);

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Get OUT Param with TMyCommand

Post by ViktorV » Fri 15 Jan 2021 09:06

Thank you for the interest to our product.
We are glad to see you have found a solution.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.

Post Reply