TUniQuery: After update no result for RET_ Parameters

[G.Scholz]

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]

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;