CODEWITHSUNDEEP
delphifirebirdfiredacanydac
cjmarques
cjmarques

Reputation: 672

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

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.

Upvotes: 3

Views: 441

Answers (1)

Deltics
Deltics

Reputation: 23036

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.

Upvotes: 5

Related Questions