Working with parameters Delphi XE7 Firedac

Eishman picture Eishman · Oct 15, 2014 · Viewed 8.7k times · Source

Any assistance here would be great.

I am trying to use parameters to dynamically change 'ORDER BY'

Below is the code I have tried but despite following the documentation I still get an error '[FIREDAC][PHYS][MYSQL] You have an error in your SQL syntax ... near "ORDER BY some_field" at line 4'

I have set ParamCreate to True

My database is MySQL

FDQuery1.Close;
FDQuery1.SQL.Clear;
FDQuery1.SQL.Add('SELECT *');
FDQuery1.SQL.Add('FROM my_table');
FDQuery1.SQL.Add('LIMIT 1000');
FDQuery1.SQL.Add(':id');
FDQuery1.ParamByName('id').AsString := 'ORDER BY some_field';
FDQuery1.Open;

Answer

Sir Rufo picture Sir Rufo · Oct 15, 2014

You did not cite the exception message as it shows up. Here is the original message

[FireDAC][Phys][MySQL] You have an error in your SQL syntax ... near ''ORDER BY some_field'' at line 4.

compare to your cite

[FIREDAC][PHYS][MYSQL] You have an error in your SQL syntax ... near "ORDER BY some_field" at line 4


To avoid this for the future just press CTRL+C on the focused exception window and the complete message is inside your clipboard and can be pasted wherever you like


Now reading this, the error is now very clear.

You expect to get a statement like this

SELECT *
FROM my_table
LIMIT 1000
ORDER BY some_field

But using the parameter you will get the following statement

SELECT *
FROM my_table
LIMIT 1000
'ORDER BY some_field'

and that is exactly what the exception message is telling you.

Just check the exception message with the previous statement

... near 'ORDER BY some_field' at line 4.

and

... near ''ORDER BY some_field'' at line 4.

As a conclusion it is not possible to change the statement itself using parameters. You can only pass values as parameters for the statement.

And the correct statement should be anyway

SELECT *
FROM my_table
ORDER BY some_field
LIMIT 1000