This article was originally created for
Databazovy Svet (in Czech) and covers a little bit more about this topic. This shortened version is focused only on solutions for Firebird, without any other stuff.
Sometimes I see question(s) in forums, groups, lists about ability to run program from trigger or better to say perform some action done by external program. Well you can create many of possible solutions, working best for your case. But I’ll cover two “special” features available in Firebird that can help you with this.
First, very elegant, option is to use events sent to clients subscribed to these. Every good connecting layer supports events – at least I can say, that Delphi, .NET, PHP, C/C++ works. Event is an identifier sent to subscribed clients from database server using some negotiated channel (i.e. similar to channel for sending commands). On the database side we’ll use
POST_EVENT <identifier>
, where identifier is any string (maximal length 78 characters). Application should subscribe for consuming event with same identifier. For example in .NET/C# the code will look like this:
FbRemoteEvent revent = new FbRemoteEvent(connection);
revent.AddEvents(new string[] { "new_order" });
// Add callback to the Firebird events
revent.RemoteEventCounts += new FbRemoteEventEventHandler(EventCounts);
// Queue events
revent.QueueEvents();
static void EventCounts(object sender, FbRemoteEventEventArgs args)
{
Console.WriteLine("Event {0} has {1} counts.", args.Name, args.Counts);
}
This solution is very neat and event are made for this kind of tasks. The handling application can run on another machine. Disadvantage is, that events are sent on commit. About rolled back transactions – if you’re interested in – you’ll not be notified. Same story with some payload, you cannot add to event some data. So if you need to send some data you have to use some kind of timestamps, flags etc.
The other solution is coming with, kind of, opposite (dis)advantages. The main idea is to use program modules (*.dll/*.so) compiled into User Defined Functions – UDFs. This module may contain any code, written in any language with
stdcall
calling convention support – C/C++, Delphi, FreePascal. Taking into account, that this code is running in server context and is blocking any subsequent execution, it’s good to keep this code really simple – KISS = Keep It Simple Stupid. For our example is suitable to make the UDF only sending some signal to another application and this application will do the action(s). The advantage of this solution is, that you can hand over the data too. Function calling is done immediately. So it’s obvious, that also data from transaction, that can be rolled back later will be processed.
Hey, that’s all. 
No more tricky stuff, just these two simple solutions. My advice is to first use events and then try to hack UDFs. Events are much better for this.
Sometimes you may need to look at the command, that’s created from your i.e. LINQ query and sent to database. Let’s say you have query like this:
var q = from m in e.master
select m.t.Length;
You can cast the q into ObjectQuery and use the ToTraceString method to see the query:
Console.WriteLine(((ObjectQuery)q).ToTraceString());
This will show you the query, that’s sent do store you’re using (mainly relational database). Neat and easy.
Mám rád všelijaká omezení. Jako správně postižený databázista mám constrainty všude. Čistá a konzistentní data mám prostě rád (ano, při upgradech vyměknu a něco někde povolím/odstraním ) Na druhou stranu nesnáším validace a omezení. [8-|] Člověk musí zobrazovat pěkné chybové hlášky, nejlépe ještě před odesláním dat na server. To znamená mít validace a omezení nejen pěkně v databázi, ale i někde poblíž ksichtu aplikace. A to mě nebaví, musíte psát ty samé validace a případně obalovat chyby/vyjímky z DB do konkrétních vyjímek, které je možné patřičně zpracovat. Poté co člověk navrhne hromadu foreign key a check constraintů a čert ví čeho ještě, musí to podat i nějak userovi, tedy user-friendly – překvapivě většině nestačí chybová hláška z DB nebo nějaká obecná věta 
. Ach jo. Škoda že není nějaký generátor, který by uměl tohle všechno vyřešit podle databáze sám.
You may wonder, how the Entity Framework will map some basic functions (concatenation, average, etc.) from i.e. LINQ or from EntitySQL to functions supported by database and what functions will be “the selected”. Well the answer is easy. Every provider will support (should support) so-called canonical functions. For Beta 3 version (current latest) of Entity Framework you can find these on http://msdn.microsoft.com/en-us/library/bb738626.aspx. As you can see, there’s a really good selection of functions and I think most of databases supports them in some way.
I’ve done some work on canonical functions few says ago in FirebirdClient (ADO.NET provider for Firebird) and 99% of it is build-in FB 2.1. If you’re interested in testing, checkout the weekly builds (except aggregates) all the canonical functions should be working.
From the list I’ve linked above, you can see, that LINQ is not cripled so for most of task you can used strongly typed way and EntitySQL use for special fine tuned stuff.
Hmmm, you may think, “And what about some database specific functions – my database has a set of really ‘must have’ function – will be these available?”. And the answer is “yes”. Every provider can provide DB specific functions, that will be also available in EntitySQL. Nice, isn’t it?
Today I get error “The operation could not be completed” when I was trying to open *.dbml LINQ to SQL file. I have this developement environment in virtual machine and I did no installs or updates. Event Log also wasn’t saying anything that could help. First classic shoots like restart, cleaning all temps, caches etc. – nothing. Still same problem.
I’ve tried to add new dbml to project (in worst case, I was ready to copy to content of files, because build was OK). Heureka, designer opened like a charm. But, when I dropped some table on designer’s surface I got error “Exception has been thrown by the target of an invocation.”. Hmm, damn.
Thanks my paranoia I have backups of a lot of stuff, virtuals too. 1-2 hours of installing and I was back. When configuring my new-old machine everything worked fine. So I’ve installed some experimental stuff like Entity Framework beta, some VS updates required and added DDEX for Firebird. Just quick click-click-next-next. I opened the project and wanted to open dbml file, oh, the error was back again.
After this, I incidentally realised, that I’ve removed FirebirdClient assembly from GAC after installing DDEX. To make DDEX work I’ve installed 2.1 version, ’cause I have it built. But for testing Entity Framework support I’m using 2.5.x version and I was planning to add it later, ’cause I had no built assembly near my hands. Exactly this (removing FirebirdClient from GAC) I did few days ago, when I was debugging some reported issue. And that was the problem! How innocent and how devastating. 
Adding assembly back to GAC solved the problem. Hope this helps somebody. Wish I’ve found it sooner and saved time with copying and installing stuff.
When you need to hook up two results in LINQ to SQL you will probably first try the method Union. It generates “standard” union (i.e. select a, b, c from x union select e, f, g from y), which is OK. But if you know the difference between union and union all (union (without all) is filtering duplicates in result, so it can be slower) you may want to use union all (for example you know that results are distinct or you want duplicates). So, you will need another method. It’s called little bit “non-SQL” Concat (at least it took me few minutes to find it, but the name makes sense). Using this method you get result with union all, so you can save some processing and speedup returning result.
Summary: UNION ALL ==> Concat
UNION ==> Union
