Devart Forums

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;

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

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

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

Use TMyQuery.

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

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: 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.

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);

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.