TUniQuery: After update no result for RET_ Parameters

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
G.Scholz
Posts: 1
Joined: Wed 21 Mar 2018 09:13

TUniQuery: After update no result for RET_ Parameters

Post by G.Scholz » Wed 21 Mar 2018 09:54

I use your component TUniQuery for update my mysql-table

Code: Select all

UPDATE lager 
SET bestnr = :bestnr , montage = 1, kennung = :kennung 
WHERE ( (gang >= :gang) AND (platz >= :platz) AND ( BestNr='0') ) 
ORDER BY lager.kosten desc, lager.Gang,  lager.Platz 
LIMIT 1 
I need the value from the field bestnr after update. If i send the sql code with

Code: Select all

UPDATE lager 
SET bestnr = :bestnr , montage = 1, kennung = :kennung 
WHERE ( (gang >= :gang) AND (platz >= :platz) AND ( BestNr='0') ) 
ORDER BY lager.kosten desc, lager.Gang,  lager.Platz 
LIMIT 1 
RETURNING gang
and execute i get an error from mysql
How the right way to use the RET_funktion?
Have you one example for your RET_ funktion?

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

Re: TUniQuery: After update no result for RET_ Parameters

Post by ViktorV » Wed 21 Mar 2018 11:54

MySQL does not support RETURNING clause, so you cannot execute the specified query in MySQL.
To solve your task, you can use the following code to get the value of the bestnr field:
var
FieldValue: Variant;
...
MyQuery.SQL.Text := 'UPDATE lager SET bestnr = @bestnr := :bestnr , montage = 1, kennung = :kennung ' +
'WHERE ( (gang >= :gang) AND (platz >= :platz) AND ( BestNr='0') ) ' +
'ORDER BY lager.kosten desc, lager.Gang, lager.Platz ' +
'LIMIT 1; ' +
'SELECT @bestnr as FieldValue;';
{
the code for setting the parameter values
}
MyQuery.Execute;
FieldValue: = MyQuery.FieldByName ('FieldValue'). Value;

Post Reply