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);
PARAM = 'DYNAMICCOL';
if (NOT exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'TESTTABLE' and rf.RDB$FIELD_NAME = 'DYNAMICCOL'))
execute statement 'ALTER TABLE TESTTABLE ADD '||PARAM||' SMALLINT;';
SET TERM ; ^
Migrating to Firebird 3.0 has been routine that caused more headache than expected. One of most common, yet pestering error was “Your user name and password are not defined“. Interestingly, it worked perfectly fine when we were using the Firebird 4.10 Ado.net Nuget, however, the moment we update our Nuget package to 5.7, we are doomed.
The fix was easy though, all you needed to do was hit your Firebird.Config file in your server and look for following line.
UserManager = Legacy_UserManager, Srp
It loooks to be some new security feature added to Firebird 3.0, however, I was quite happy without it. Commenting out the line cleared off my issues .
Have you ended up in a situation wherein, you know the Column Name but you have no clue about the Table Name ? I recently came across the situation and following is one query which help me out of the situation.The Query is for Firebird Database.
select f.rdb$relation_name, f.rdb$field_name
from rdb$relation_fields f
join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
and r.rdb$view_blr is null
and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
where f.rdb$field_name like '%ColName%'
order by 1, f.rdb$field_position;