TIBScript and local variables

Question

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.

Variables in the `into` clause don't need to be prefixed with a `:`, but it should be supported (though possibly that was introduced after Firebird 2.0, which is really old by now). Remove the colon, or try upgrading to a more recent (and supported) version of Firebird. Prefixing variables with a colon is only required in parts of a query where otherwise there would be ambiguity with colums etc in the statement. Such ambiguity does not exist in the `into` clause as it can only contain variables. — Mark Rotteveel, Jun 23 '20 at 13:29
Yes, I know that the colon is not obligatory after `into` but the variable is used in several places of the script such as insert statements where it is strictly required and I can't do without the colon. The error message doesn't appear and everything works smoothly only when I find a workaround to write the script without using local variables but this solution is not the best because it implies code duplication and slower performance due to executing the same query several times. — Rick77, Jun 23 '20 at 14:06
What happens if you execute this through ISQL or another query tool? Are you sure you are using the 'normal' colon (U+003A) and not one of the other colon codepoints: https://unicode-search.net/unicode-namesearch.pl?term=COLON . In any case, can you provide a [mre]. — Mark Rotteveel, Jun 23 '20 at 14:09
The script executes successfully in FlameRobin too. I am using the right character for the colons, I checked that. — Rick77, Jun 24 '20 at 06:48
Normally the token unknown error includes the problematic token, as you haven't shown it in the error message, I'm wondering if maybe this component itself is parametrized (I don't use Delphi myself, so I'm just guessing here), and replacing it in some way. — Mark Rotteveel, Jun 24 '20 at 07:47
I edited my post. The error message includes a question mark at the end. There are no question marks in the script. — Rick77, Jun 24 '20 at 09:10
I guess that means that TIBScript thinks the parameter is a parameter from Delphi to Firebird, and has replaced it with a positional parameter (`?`). Check if there is a property to disable this, or if you can escape the parameter in some way (try, for example, `\:test_variable` or `::test_variable` or whatever a reasonable escape strategy would be in Delphi). — Mark Rotteveel, Jun 24 '20 at 09:21
@MarkRotteveel You are right for the positional parameters, but unfortunately it is hardcoded. But worked without prefix `:`. — Val Marinov, Jun 24 '20 at 10:26

Val Marinov · Answer 1 · 2020-06-24T13:11:23.337

3

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.

edited Jun 24 '20 at 13:11

answered Jun 24 '20 at 10:06

Val Marinov

2,705
17
22

I edited the script in my post to include insert statements. Yes, you are right that I can remove the colon in the select statement but I can't do that in the insert statements. – Rick77 Jun 24 '20 at 11:11
1

Unfortunately removing the colon is only an option in the `into` clause in not with other occurrences of local variables or parameters. – Mark Rotteveel Jun 24 '20 at 12:24
1

@Rick77 I'm afraid that you can't do this with TIBScript component. A colon (:) always is replaced with ('?'). This is hardcoded. – Val Marinov Jun 24 '20 at 12:41

TIBScript and local variables

1 Answers1