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

Firebird 3.0 Issues

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 .

Search Tables with Known Column Name

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;

Batch Execution in Firebird

Executing a .sql file on Firebird Database can be quite a messy job. The lack of proper documentation and support would hurt you more when encountered issues and that is what I faced when I required to do execute contends of a .Sql File. Having understood that FbBatchExecution is the command I would required, I went to write following Function in C#.
public void ExecuteTransaction()
{
    try
    {
        if (m_cmd.Connection.State == System.Data. ConnectionState.Closed)
            m_cmd.Connection.Open();

        using ( FbTransaction fbTransaction = m_connnection.BeginTransaction())
        {
            FbScript script = new FbScript( this.ScriptFileContends);
            m_cmd.Transaction = fbTransaction;
            script.Parse();
            var BatchExecute = new FbBatchExecution(m_cmd.Connection, script);
            BatchExecute.Execute( true);
        }
    }
    catch ( Exception Ex)
    {
		throw Ex;
    }
}

This looked reasonable, I am reading out the Contends of the File using FbScript object and using the FbBatchExecution Object for bulk queries. I am managing the whole operation using FbTransaction. All seems well until I ran it and came up with following Exception.
“Execute requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized”
After hours of browsing, it turned out to be simple actually. All I needed for to add following lines.
 BatchExecute.CommandExecuting += delegate( object sender, CommandExecutingEventArgs args)
{
      args.SqlCommand.Transaction = fbTransaction;
};

The new Method would like following and you all set to go.
 public void ExecuteTransaction()
{
    try
    {
        if (m_cmd.Connection.State == System.Data. ConnectionState.Closed)
            m_cmd.Connection.Open();

        using ( FbTransaction fbTransaction = m_connnection.BeginTransaction())
        {
            FbScript script = new FbScript( this.ScriptFileContends);
            m_cmd.Transaction = fbTransaction;
            script.Parse();
            var BatchExecute = new FbBatchExecution(m_cmd.Connection, script);
			BatchExecute.CommandExecuting += delegate( object sender, CommandExecutingEventArgs args)
            {
                args.SqlCommand.Transaction = fbTransaction;
            };
            BatchExecute.Execute( true);
        }
    }
    catch ( Exception Ex)
    {
		throw Ex;
    }
}