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 ; ^

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s