Saturday, September 12, 2009

Create a Firebird database from script file with ActiveRecord

Once I needed to create a Firebird database from a SQL script file using the ActiveRecord component from Castle Project. First problem was to convince ActiveRecord to execute a non-query command and the second, to execute the script file using the Firebird embedded engine.

Execute non-query commands with ActiveRecord

The main idea is to grab the IDbConnection object from the current ActiveRecord session. After that, you can use this connection any way you want to.

C# .NET

ActiveRecordMediator.Execute(typeof(ActiveRecordBase),

delegate(ISession session, object data)

{

IDbConnection conn = session.Connection;

IDbCommand cmd = conn.CreateCommand();

session.Transaction.Enlist(cmd);

cmd.CommandText = sqlCommand;

returnnull;

},

null);

Create database by script file on Firebird embedded

Here the problem is that Firebird can't execute the entire sql script file as a sigle statement. Doing so, you will get an "Dynamic SQL error" right before the second "create table" command.

The solution is to split the script file content by ";" to get each SQL statement as a single chunk. Some code clean-up is also required.