Working with parameters Delphi XE7 Firedac

Question

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 1

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