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