I was recently interviewed for InfoQ article – Q&A with Jiri Cincura of the Firebird Database Project. We touched Firebird, ADO.NET, O/RMs, Entity Framework etc. If you have any questions, feel free to ask.
A question came to me last week. It was simple. Given the column I’d like to query all tables in database for this column (with some condition) and get values back. It was on Firebird so I jumped into system tables and generated query on the fly in execute block (aka anonymous stored procedure).
The idea is simple. First get all table names (views and system tables excluded, but you can also exclude i.e. temporary tables) with this column (to be able to later run the query successfully), then concatenate some strings to build the query (with condition for the column) and finally use execute statement to run the query. The into clause will fill the variable and suspend will send the result (row) to client.
execute block
returns (table_name varchar(100), column_value varchar(100))
as
declare variable column_name varchar(100);
begin
column_name = upper('id'); /* put here your column name */
for select rdb$relation_name from rdb$relations r
where rdb$system_flag = 0 /* no system tables */ and
rdb$view_blr is null /* no views */ and
exists(select 1 from rdb$relation_fields rf where rf.rdb$relation_name = r.rdb$relation_name and rf.rdb$field_name = :column_name)
into :table_name do
begin
execute statement 'select cast(' || column_name || ' as varchar(100)) from ' || table_name || ' where /* put your condition here */' into :column_value;
suspend;
end
end
As a modification you can also instead of running n queries create one big string with union all-ing all queries and run just this one. You should compare execution plans and speed to see which one performs better. Then you would use for execute statement ... do do process results.
Previous version of ADO.NET provider for Firebird brought us a support for command tracing. Although it was good, it could be done better. Few interesting scenarios came back to as a valuable feedback and with the old implementation it was hard to do it.
The new one builds on top of TraceSource class allowing you to handle different sources from trace messages easily and independently. The what’s logged is same, but now every message is traced through FirebirdSql.Data.FirebirdClient source, hence you can easily i.e. turn it of (and just these messages, without affecting other messages) or send it to i.e. console window. Just a few lines in app.config and you’re done.
<system.diagnostics> <sources> <source name="FirebirdSql.Data.FirebirdClient"> <listeners> <clear /> <add name="console" type="System.Diagnostics.ConsoleTraceListener"/> </listeners> </source> </sources> </system.diagnostics>
The code above sends all messages (commands) from provider to (only) console window. You can use your own listener, of course or turn it off completely, using just the <clear />.
I’m happy to bring you early Christmas gift packed as ADO.NET provider for Firebird version 2.7. This version brings important bug fixes (tracker.firebirdsql.org) and logging improvements.
This release wouldn’t be possible without support of people/companies using provider actively. Big thanks to them.
You can download it at www.firebirdsql.org or use NuGet package.
Enjoy!
Imagine you have a blob column and you want to add sorting clause to your query based on that column. Crazy? Might be. On the other hand, why not?
Firebird allows you to use blob column for sorting. No problem. But the behavior might surprise you. I’m not going to deeply describe how the blobs are stored in Firebird database. Simply speaking, it’s stored in separate data pages and inside row only blob id is stored. If you use blob column for sorting, Firebird isn’t fetching the complete blob (though looks straightforward, it would be very slow), but rather uses blob id for sorting. You probably see the problem already – the blob id has nothing to do with content. Hence the sorting will be very likely broken.
But there’s a solution. I’m assuming that you want to mainly sort on text blobs (though you can use it on binary blobs too). Simply cast the blob to i.e. varchar(20) (choose length that fits your needs) and sort using this. Yes, it’s going to be slow, but if you need to do it often, you can precompute this column (using trigger etc.).
Yep, it’s done. Now you can download FirebirdClient from NuGet. From nuget.org/List/Packages/FirebirdSql.Data.FirebirdClient to be precise.
It took me a while to find some time to create the package and publish it. But recently I started using NuGet quite often, so assigned this task higher priority.
The build there is same as the default one (targets .NET 4 CLR) you can download from site. Later I’d like to incorporate into package other versions (different CLRs, Mono builds, …) too. Maybe the other pieces like WebProviders, DDEX (?) and unstable builds could be there too. I’ll think about it more.
Hope you’re excited as I’m and you’ll enjoy it. 
I’m proud to announce new version of ADO.NET provider for Firebird – 2.6.5. It’s half maintenance release, half new features.
You can find all bug fixes in tracker.
The new features include and improvements:
* Support for Trace API in Firebird 2.5.
* Improvements in SQL generation for Entity Framework.
* Support for commands logging .
* Slightly faster command execution of big queries.
* And a lot of small code improvement making it more stable…
You can download it at http://sourceforge.net/projects/firebird/files/firebird-net-provider/2.6.5/ or http://www.firebirdsql.org/en/net-provider/.
Hope you’ll enjoy the release.
I recently improved the command logging in ADO.NET provider for Firebird. Let me start with little bit of history and then you’ll see the motivation and current improvements.
Before I added simple Debug.WriteLine to the FbCommand class. This was mainly driven with need to easily see commands I was generating for Entity Framework. Then the Visual Studio 2010 came with the IntelliTrace (only Ultimate version) and while working with MS SQL Server I was happy to see commands without any additional effort. I wanted the same for Firebird too. Sadly the IntelliTrace right now isn’t publicly pluggable. I was still using the debugging code, but often when doing development on customer’s applications using released version of provider I lost this ability. No debug outputs, no IntelliTrace.
Because I believe it’s important to have easy way to see (and not only for Entity Framework’s generated) the command, to spot performance problems early, I added simple logging facility. This logging is enabled for all builds (not only debug) and uses Trace class from .NET Framework. Everytime you Prepare (the Prepare method is called also automatically before execute if not called manually) command you’ll see the command text and current parameter names and values (if any). If you’re inside Visual Studio you see by default the output in Output window. You can also configure it to log i.e. to file, probably usable in staging and/or production.
Hope you find this useful as I do.
Few days ago I wrote “Some thoughts on denormalization” post. Though concatenating data to one string is easy with there introduced function, splitting it back could be harder. I left it as exercise, but I feel, to make it complete, it’s good to show one possible implementation.
Here’s one I came with today (using Firebird syntax, however it’s almost pure SQL), quickly. It’s something I created from start to finish in one row and sure for some cases it could be optimized.
recreate procedure Tokenize(input varchar(1024), token char(1))
returns (result varchar(255))
as
declare newpos int;
declare oldpos int;
begin
oldpos = 1;
newpos = 1;
while (1 = 1) do
begin
newpos = position(token, input, oldpos);
if (newpos > 0) then
begin
result = substring(input from oldpos for newpos - oldpos);
suspend;
oldpos = newpos + 1;
end
else if (oldpos - 1 < char_length(input)) then
begin
result = substring(input from oldpos);
suspend;
break;
end
else
begin
break;
end
end
end
The procedure splits the input string using the specified token. The string can (or not) end with the token itself, the procedure will handle it.
select * from Tokenize('ab,cd,e', ',')
union all
select * from Tokenize('ab,cd,e,', ',');
union all
select * from Tokenize('ab,cd,e,,', ',');
What it is not handling is some form of quoting in case there's a token inside the element. Mainly because its intended purpose is to tokenize strings you can control (see the previous post) and because it will slow down the execution.
Feel free to improve it etc. (either post link to your solution or post the code in comments).
Few months ago I was playing with different VPS providers and I was also considering deploying the application to some “small instance” cloud. I came to Micro Instance of Amazon EC2. Part of the solution I was doing research for is Firebird database and because the pricing looks good, I tried to install Firebird (2.5 SuperClassic) on this instance.
The good news is, that it’s possible. As I did manual install, it was fast. The bad news is, that it’s really slow. If you read the description of Micro Instance you can expect it will not be blazing fast. But even for really low-load database server it’s almost unusable.
I’m not saying you can’t use it. Maybe some slow/late backup/mirror or something like that should be doable. But for normal applications using database server you’ll do better job with your own server or VPS.
Recently I was playing with Amazon EC2 trying what I could use it for. I was playing with Firebird there as well. But in fact you have virtual machine you can use. So using Firebird there wasn’t that hard. But I got and idea about Azure, because it’s more platform for applications than computers.
Then the VM role was introduced and the challenge was somehow not so challenging. But …
Yes, it was still in my mind. My rough idea was abuse Firebird Embedded and load it inside Web or Worker role. Only problem was where to store the database. Sure, the scaling will be compromised, but it’s just “try it and see what could be done and let others (not) use it”. 
The CloudDrive solved my problems about where to store the database.
So it was no-brainer to try it.
First some helper for CloudDrive use.
public class CloudDriveHelper : IDisposable
{
CloudDrive _drive;
public string DriveLetter { get; private set; }
public CloudDriveHelper(CloudStorageAccount storageAccount, string name, int cacheSize = 0, int driveSize = 1024)
{
CloudBlobClient client = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = client.GetContainerReference("drives");
container.CreateIfNotExist();
string diskName = string.Format("{0}.vhd", name);
_drive = storageAccount.CreateCloudDrive(container.GetPageBlobReference(diskName).Uri.ToString());
try
{
_drive.Create(driveSize);
}
catch (CloudDriveException)
{ }
DriveLetter = _drive.Mount(cacheSize, DriveMountOptions.None);
}
public void Dispose()
{
Dispose(true);
}
~CloudDriveHelper()
{
Dispose(false);
}
void Dispose(bool disposing)
{
if (disposing)
{
_drive.Unmount();
DriveLetter = null;
_drive = null;
}
}
}
I added reference to FirebirdSql.Data.FirebirdClient assembly. I played with Firebird Embedded in package, but putting it to blob storage via CloudDrive would be easier, maybe next time. The environment in Azure is x64 (for Web and Worker roles, run by WaWebHost and WaWorkerHost respectively), so don’t forget to use proper version. Anyway, then I abused Web role to see some results.
Simple controller action.
public ActionResult Index()
{
using (CloudDriveHelper drive = new CloudDriveHelper(Global.Account /* could be CloudStorageAccount.DevelopmentStorageAccount as well */, "firebird", driveSize: 1024))
{
string database = Path.Combine(drive.DriveLetter, "SomeDatabase.fdb");
TestClass.CreateDatabase(database);
ViewData["FirebirdVersion"] = TestClass.GetServerVersion(database);
ViewData["SomeData"] = TestClass.SomeQuery(database).ToArray();
return View();
}
}
And some methods to do actual work.
public static void CreateDatabase(string databasePath)
{
FbConnection.CreateDatabase(CreateConnectionString(databasePath), true);
}
public static string GetServerVersion(string databasePath)
{
using (FbConnection conn = new FbConnection(CreateConnectionString(databasePath)))
{
conn.Open();
return conn.ServerVersion;
}
}
public static IEnumerable<Tuple<string, object>> SomeQuery(string databasePath)
{
using (FbConnection conn = new FbConnection(CreateConnectionString(databasePath)))
{
conn.Open();
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from mon$database";
using (FbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
yield return Tuple.Create(reader.GetName(i), reader[i]);
}
}
}
}
}
}
Well, it did worked OK. Before you think how cool is that I have some bad news. The cloud computing is mainly about scaling and elasticity. With this you have one drive and (have to have) one instance of Firebird working with it – you’re not scaling. You can’t scale with this solution. So it’s more about being concept. However I came with two possible options, that are more realistic.
First is having one special Worker role processing some data and storing it in Firebird database (for whatever reason). The Azure machines are quite powerful and if you have everything there why to setup your own server… And I think this can be worth in some scenarios (apart not being fault tolerant etc.).
Other one builds on top of previous solution and abuses Firebird’s external tables. You can load or store data via external tables to blob storage via CloudDrive and (re-)use already written logic in stored procedures (or triggers). Little crazy, I know hence I’ve not tried it (so maybe it’s not doable). But technologies are here to use these on the edge.
As I said, the real world usage of all this isn’t big, but as a exploration project it was fun.
From time to time I have an idea for helpful project I could do to help myself or people around me to finish some task faster/easier. And, of course, it often involves database. And though I’m a strong believer in normalization (3NF is a must), at least during initial design, sometimes I see this project being partly simple and partly I want it done in no time (because I’m doing it in my own spare time) I think about storing data denormalized and doing the work in application.
Classical variation is user and his/her permissions. It’s a standard 1:N case (also might be M:N). The proper way is to create two tables and use foreign key to ensure data integrity. But often you feel it’s not so crucial function and maybe you’ll have less than five permissions, you think, storing it in column comma separated is good idea and will cut the time significantly.
You might be right. But you feel it’s not correct. It’s bad. It’s not for future extending of application (and we all know it’ll happen 8-)). Today I realized I can have all from both worlds – good design and quick development.
Design it normalized, because it’s what you should do. But then you can create a simple view where you use some kind of “LIST” function (i.e. Firebird has exactly that named one). This view will create you denormalized form of data. With a small help from triggers you can also update that view hence underlying data (left as an exercise for reader).
select list(r, ',') from ( select 'r' as r from rdb$database union all select 'r' from rdb$database union all select 'r' from rdb$database );
Because you write this only once (yes, probably you have to rename some table next time), for next “fun” project you’re designing tables as it should be and because you have it already ready in tool-belt you have the ease of development too.
My head-split is solved.
I was recently migrating my server to PHP 5.3. To be honest I don’t know why. I’m not using PHP often, only few applications and some simple web pages (really simple logic). And this blog. To make things worse, I’m using as my main database Firebird, not MySQL. And PHP driver for Firebird, say, it’s very average. But to keep myself in touch with “current” world I decided to go to PHP 5.3 (hoping also some bugs (mainly Firebird related 
) to be fixed there). I faced two issues.
First was problem with loading php_interbase.dll. After some research I found, the gds32.dll isn’t part of PHP package as it was in 5.2. Using fbclient.dll from Firebird’s installation and renaming it to gds32.dll didn’t solve the issue. Surprisingly (thanks Process Monitor), the extension is looking for fbclient.dll (in PHP’s directory, not in ext). InterBase & Firebird mix.
Other problem was MySQL. I don’t like the database and also I’m not expert in PHP+MySQL stuff. I feel more strong around Firebird stuff. The incompatibility is known and it’s about MySQL’s new (longer) password hashes being mandatory to be able to connect from 5.3. And you get error saying it: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication.. I’m not admin (logically or from privileges) of MySQL server I’m using, so some tips were not working. The final commands that solved my issue were:
set session old_passwords = 0;
set password = password('<password>');
I was kind of jumping around it, with similar commands or commands doing same thing only requiring higher privileges.
Although simple to solve, I spent some time doing wrong steps (and trying). Hope this will save yours.
I do love Entity Framework. And you probably know it from the amount of weird ways to do some things published on this blog. And so I do love Code First. It’s like having all the power absolutely under control (or at least there’s a possibility to have it). And I also do love Firebird. It’s a great database engine. And I admit, it has strong as well weak points. But every engine does.
So there’s no wonder I’m using Entity Framework’s 4.1 (currently in CTP stage) Code First with Firebird. If you’re a bit lazy and you’re specifying only required minimum on information to run the mapping, you might quickly face one problem.
Long story. If you generated your model from database, the SSDL contained a lot of information about database structure. Especially lengths of (var)char fields. And these information were then used by provider for Firebird to create proper queries. But with Code First it’s bit boring to specify lengths for string fields moreover, when it works without it.
But the Firebird’s API has some limitations (especially around sizes of stuff) and by default (right now – CTP) the (var)char fields are 4000 characters long. The amount of (var)char parameters in code is limited (depends on few variables like charset, hence I’m not putting here exact number) and with UTF8 (multi-byte charset) it’s even more limited. And UTF8 is option #1 in .NET world.
All this together, couple of (var)char fields without length specified and you could start seeing Implementation limit exceeded and block size exceeds implementation restriction etc. The first place where you’ll see it is inserting, because there you have all the fields.
The solution is easy. Specify lengths explicitly and hope for the best. If the table is very wide (aka it doesn’t work), you could always split the table in more (yes, not great, but …).
When I came from Firebird Conference in Bremen I was so excited about all the people being excited about Trace and Audit API I decided I’ll implement this in .NET provider as soon as possible. And here it is.
Right now there’s a new class (and few supporting it) on FirebirdSql.Data.Services namespace called FbTrace, that you can use to start trace session and see what’s going on on server. I’ll not describe the Trace API itself, you can find info in documentation, I’ll rather focus on how you can use it from the provider.
Right now, as I have no sharp idea how the people will use it, I implemented it simply and later, if I find some common cases, I’ll add some “shortcuts” to help with these scenarios.
If you have ever used any FbService derived class, you’ll be able to use FbTrace in under a minute.
FbTrace trace = new FbTrace();
trace.ConnectionString = "database=localhost:rrr.fdb;user=sysdba;password=masterkey";
trace.ServiceOutput += (object sender, ServiceOutputEventArgs e) =>
{
Console.WriteLine(e.Message);
};
trace.DatabasesConfigurations.Add(
new FbDatabaseTraceConfiguration()
{
DatabaseName = string.Empty,
Enabled = true,
Events = FbDatabaseTraceEvents.Connections | FbDatabaseTraceEvents.Transactions
});
trace.Start("test");
The important stuff is in FbDatabaseTraceConfiguration class, where you specify what should be traced, exactly the same way as in fbtrace.conf (you can find this file in Firebird‘s installation). Mainly the database name to match for trace (if you want only some), whether it’s enabled (probably useful if you want to keep the configuration somewhere, but selectively turn it off and on). And finally the events you’re interested in – FbDatabaseTraceEvents enum. In the example above I’m tracing only connections and transactions (not for example stored procedures prepares or executions). All this configuration is added into DatabasesConfigurations property. You can have for sure different configurations for different databases. For Services API tracing there’s property, because it’s only one “services instance” in Firebird server, called ServiceConfiguration. At the last line I’m starting the trace session named test using method Start.
And that’s it. When something happens in the database the ServiceOutput event handler will be invoked and you’ll get the trace message.
Of course, you can also call Suspend, Stop, Resume and List methods to do accordant actions in API.
This is the initial implementation and some methods may be added (I’m already thinking about a few). Hope you enjoy it. Feel free to play with it and comment here or in list.
If you’re expecting some sensation, I’ll not make you happy. Shortly, yes, Firebird is free, completely. You can use it wherever you want and you don’t have to pay anything nor release sources of your application nor …
On the other hand, the whole truth, considering all the edge cases and consequences, is different. To keep high quality of final product, keep adding new features, provide bug fixes – simply moving forward – the creator needs some resources. If these resources will not be available, it will be effectively dead. The resources I’m here talking about, in case of Firebird, are people doing full time or regular development. These people have families, houses, hobbies, … And for all of these items you need money (in our society ). They’re not doing it for fun (only), but also for living as well.
So Firebird actually needs some money to keep moving. It doesn’t have licenses to buy or something like that. We’re simply relying on the fact, that people using it, similar to people working on it, do love it. And are educated enough to realize all this and provide, even small, support. Thus next time you’ll be deploying your application with Firebird, think about sending $10 or even $1. I bet it’s nothing for you (compared to price of the application or money you’re paying for toilet paper in your office). And ten thousand people (not much) donating $10 makes a huge difference. It’s not only about few donating $10000.
And by the way, Firebird is not “just” engine, but tools around too: .NET driver , Java driver, documentation, QA, …, you name it.
Firebird .NET provider team is proud to announce next version of ADO.NET provider for Firebird – 2.6.0.
This version contains various bug fixes as well as new features and improvements. And of course support for some of new Firebird server features. Highlights:
All changes can be found at tracker.firebirdsql.org.
You can download it at http://sourceforge.net/projects/firebird/files/firebird-net-provider/2.6.0/ or http://www.firebirdsql.org/index.php?op=files&id=netprovider.
Thanks to all who helped improving and hunting issues.
The conference is starting today. I’ll write here during the days about what’s going on. Also check Twitter with hashtag #FBCon10 for most recent quick messages.
Day 1:
Well, I was not so active posting new content during the day, mainly because I had no connection, so I was only tweeting. Right now we’re slowly approaching the end of first day. All the sessions we’re interesting so far.
Vlad’s about new features in SQL was a good summary of all the pieces of information you collect throughout the year. The next session from Holger about character set was good as well. Though as person from .NET world my databases are alway UTF8, so I don’t care too much. But I understand, that understanding how Firebird works with different characters set is crucial to know, and not only for migrating i.e. legacy database to UTF8 or something like that. Than was my session. Most interesting, of course. Well, not a lot of .NET people, only 4 attended. Frank’s session (actually two) about trees in database was a great, if you’re not familiar about some basic concepts of storing trees and querying these. Especially if you are (were) not familiar with recursive CTEs (which I’m using a lot).
Right now the last session of day, Holger’s about UDFs and 32bit & 64bit and Lazarus and FreePascal, is slowly starting. As I’m not using UDFs, because I’m relying strongly on built-in functions (and you can’t, right now, write these in managed code ), I’ll just check what are the challenges and problems today with it.
Day 2:
Connection is today little bit better, so I can post something. We’re now in the middle (slowly approaching) of the second day. So far I’ve seen the Vlad’s presentation about ODS improvements in Firebird 3. Although it’s probably not going affect my databases so much, it’s nice to hear somebody explaining the internals as it brings you more complete view on the topic. Then we have seen some nice features of IBExpert tools from Holger. Some are really advanced. And right now I’m sitting on session about Firebird performance comparison and it’s a nice summary of different stuff you can buy and/or do to make (or not) make your database(s) running faster. In the other room Roman is doing session, but as there was some swapping, I don’t know the name .
Dammit. My session is now done, but my computer froze while switching virtual machines. :-\ At that time I really appreciated my effort to keep my system clean and of course my SSD. In under two minutes I was back. At least there was lot more people visiting my session compared to yesterday.
We’re slowly approaching the end of the end of the day 2, only one session comparing Firebird 2.5 architectures is left. Because I’m using 2.5 version for more than a year, I’m confident I know all the important differences. Again, I’m looking to the after-dinner talks, because you’ll learn a lot of stuff (not only Firebird or databases related).
Day 3:
The final day, day 3, is now over. That means also the conference is over. Surprisingly this day was most interesting (related to session topics) for me. This first session was about the Firebird future features from Dmitry (presented by Vlad). Then Thomas’ about audit and trace. The trace is in fact, from using it perspective simple, but a lot of people might not know about it. Roman has a similar session about RIA and Java+Firebird (mine was OData+Firebird), so I was able to see how it’s done in Java world. The IBEBlock language session, well I was surprised how much functions you have available there, some of these really advanced and you can still use it as DLL and scripting. My, already mentioned, OData+Firebird session was, as I was kind of expecting after previous days, based at least on the peoples elaboration at the Q&A, interesting for them as well (and everything was working smoothly ). And that’s pretty much it, last day of conference.
This year, IBExpert team did again great job with organization. Not only conference, but also all the stuff around it (except the internet connection, which was really bad). As a speaker I was in particular pleased with the recording of session. No software hassle, just some frictionless hardware box recording directly from VGA between notebook and projector.
During evening events we did some Mercedes Benz cars driving, as the conference was in their customer center and of course exchanged a lot words about Firebird, topics realted to Firebird, but also absolutely unrelated topics. Not a lot of .NET people, only few, but the others were little bit interested about the .NET world.
The next one is already planned for the beginning of 2012 (of course if somebody will not do one sooner). Looking forward to it already.
I almost forgot to announce the topics touching ADO.NET provider for Firebird. This year I have three sessions. You can find more info, and register, of course, at firebird-conference.com. Here’s the list.
So if you’re interested in any of these topic, come, ask, participate. Especially hearing scenarios in what you’re using the .NET provider is very valuable for me. I also like talking between sessions, I always learn something new and I’m eager to to my best to answer all questions people are asking.
Remember the challenge I did some time ago with .NET provider for Firebird and MonoTouch? Well because I’ve got access to previews of MonoDroid, why not to try the same here?
Again it’s a pretty challenge for the whole MonoDroid stack, as the provider uses a lot of various pieces from .NET Framework. And taking into account, the MonoDroid is still in previews phase I wasn’t sure I’ll be able to succeed. However I did. With some tweaking, and I kind of remembered the important places from last attempt, so it was faster, I was able to make it work easily.
Application connected to Firebird server and showing server version and data from MON$DABATASE
Cool, isn’t it? Taking into account, that the Windows Phone 7 (because everything there is based on Silverlight) doesn’t contain pieces from ADO.NET, it’s nice that Mono isn’t crippling the objects available.
Still using i.e. OData is probably better idea, but who knows what somebody might wanna create.