Appending Strings and Parameters in Firebird.

One of the issues I recently faced while working with Firebird was how to use a string within a ‘Execute Statement’. For example,

execute statement 'ALTER TABLE TESTTABLE ADD COLNAME VARCHAR(20) DEFAULT 'HELLO'';

This throws an error. though the answer looked farely simple when done. You need to two Single Quotes.

execute statement 'ALTER TABLE TESTTABLE ADD COLNAME VARCHAR(20) DEFAULT ''HELLO''';

There might be another situation, when you need to append string based on a variable. Here a way out to do the same, using Pipe Character.

EXECUTE BLOCK AS
DECLARE PARAM VARCHAR(20);
BEGIN
PARAM = 'DYNAMICCOL';
if (NOT exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'TESTTABLE' and rf.RDB$FIELD_NAME = 'DYNAMICCOL'))
then
BEGIN
execute statement 'ALTER TABLE TESTTABLE ADD '||PARAM||' SMALLINT;';
END
END ^
SET TERM ; ^