CODEWITHSUNDEEP
sqldelphifirebirddelphi-7firebird-psql
Rick77
Rick77

Reputation: 241

TIBScript and local variables

I am working with Delphi 7 and Firebird 2.0. In my application I am using TIBScript components. The problem arises when I use local variables in the script. Firebird requires the names of local variables to be preceded by a colon in some cases. That’s where the problem lies in. The application stops showing the error message:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, column 66
?

The token in question is the colon. Here is how my script looks like:

SET TERM ^ ;
    
EXECUTE BLOCK AS
  DECLARE test_variable INT;
BEGIN
  SELECT tt.id FROM test_table tt WHERE tt.name LIKE 'abc%' INTO :test_variable;

  INSERT INTO test_table2(id, test_column)
  VALUES(1, :test_variable);

  INSERT INTO test_table3(id, test_column)
  VALUES(1, :test_variable); 
    
  ...
    
END^
    
SET TERM ; ^

The same script executes without any errors when run from IBExpert.

How can I use local variables in a TIBScript? Any help would be appreciated!

I want to add that this problem occurs only with variables inside an EXECUTE BLOCK construct. There is no problem with local variables in stored procedure and trigger definitions.

Upvotes: 1

Views: 557

Answers (1)

Val Marinov
Val Marinov

Reputation: 2755

After executing the method TIBSQL.PreprocessSQL (Unit IBX.IBSQL line 2362), parameters marked with ":" on the front are replaced by "?". So you should use parameters without ":". Also I think it should be removed SET TERM. Instead, to set terminator value use the IBScript.Terminator property.

P.S. I watched unit IBX.IBSQL in Delphi 10.3 Rio.

this

EXECUTE BLOCK AS
  DECLARE test_variable INT;
BEGIN
  SELECT tt.id FROM USERS tt WHERE (tt.fname LIKE 'abc%') INTO test_variable;
END;

is executed properly when

IBScript.Terminator = ^;

Edit:

You can't execute INSERT with parameters in EXECUTE BLOCK using TIBScript component.

As Mark Rotteveel comented:

Unfortunately removing the colon is only an option in the into clause in not with other occurrences of local variables or parameters.

Upvotes: 3

Related Questions