Getting error using a parameter with anydac (now firedac) script for firebird

Question

I'm unable to get the following script to execute without error.. It's running in delphi against a firebird database.

var
  vScript: TADScript;

begin
  vScript := TADScript.Create(nil);
  vScript.Connection := xConnection;
  vScript.Transaction := xTransaction;

  with vScript.Params.Add do
    begin
      Name := 'DEFVAL'; // also tried ':DEFVAL'
      AsInteger  := 1;
    end;

  with vScript.SQLScripts.Add do
    SQL.Text := 'ALTER TABLE "PERSON" add "AGE" INTEGER DEFAULT :DEFVAL';

  vScript.ValidateAll;
  vScript.ExecuteAll;
end

It gives an error of 'Token unknown - line 1, column 48', the parameter (:DEFVAL) location

I've tried assigning the sql text first then calling Params.FindParam but it's not in the list. This works for queries.

Not using parameters and just including the default value in the SQL string works but this code is used as part of a bigger frame work and that option is a last resort.

Just as suggestion, try to not use the with statement. It is just to make the code more readable in general — Alberto Miola, Nov 01 '16 at 21:38
I complete agree about with statements.. for some reason when I found sample code for using any dac components I just kept them :P — cjmarques, Nov 02 '16 at 13:45

score 5 · Accepted Answer · answered Nov 01 '16 at 22:05

5

The reason you get a token unknown error is because ALTER TABLE statements do not allow the use of parameters in this way.

You will need to concat the stringified default value to the ALTER TABLE statement. Since your code is unconditionally applying a default value of 1 in this case then this can be simply included in the statement literal:

with vScript.SQLScripts.Add do
  SQL.Text := 'ALTER TABLE "PERSON" add "AGE" INTEGER DEFAULT 1';

If you did need to accommodate potentially variable default values then obviously this would need to change to something similar to:

with vScript.SQLScripts.Add do
  SQL.Text := Format('ALTER TABLE "PERSON" add "AGE" INTEGER DEFAULT %i', [iDefaultValue]);

Where iDefaultValue is some integer variable holding the default value required.

answered Nov 01 '16 at 22:05

Deltics

20,843
2
38
67

1

As an alternative use AnyDACs macro feature instead of parameters. – Uwe Raabe Nov 01 '16 at 23:04
Thank you. That explains it. I'll have to modify the string instead of using parameters. Thanks Uwe Raabe, I'll look into AnyDAC's macro feature. – cjmarques Nov 02 '16 at 13:47
After looking at TADMacro, I think that's the way to go for me. Thanks again. – cjmarques Nov 02 '16 at 13:53

Getting error using a parameter with anydac (now firedac) script for firebird

1 Answers1