Monday, July 21, 2008

Another great new feature of Firebird 2.5

Other than CREATE USER something really valuable has been added, the ability to query other databases.
Using a table structure like my previous post what follows is an example of a query running on an external database:

SET TERM ^ ;
CREATE PROCEDURE GET_MASTER_PROD_ALL_EXT
RETURNS (
P_CODE Char(5),
I_ENABLED Char(1),
P_DESCR Varchar(50) )
AS
declare variable qry varchar(5000);
BEGIN
qry = 'SELECT pm.PRODUCT_CODE, pm.IS_ENABLED, pm.PRODUCT_DESCR
FROM product_master pm';
EXECUTE STATEMENT qry ON EXTERNAL DATA SOURCE 'localhost:c:\pippo2.fdb'
AS USER 'sysdba' PASSWORD 'masterkey'
INTO :p_code, :i_enabled, :p_descr;
SUSPEND;
END^
SET TERM ; ^

The trick is the ON EXTERNAL DATA SOURCE clause, which allows you to specify a target database ("localhost:c:\pippo2.fdb" in my case, generally that's time to start using aliases) and the user/password to be used
As you can see , no "dblinks" yet, but of course you can embed it into a view and join it (the stored proc or the view) with other local or remote objects.
The querystring can also be built dynamically passing parameters.
Isn't it great?

4 comments:

Jhonny Arley said...

That interesting!, Knew that this features are implemented but only so far as I understand is used, thank you very much pabloj. :)

OlDer said...

"DATA SOURCE" - should be two words.

pabloj said...

@older:
you are right, thanks

Bernard said...

It's really great to see how Firebird is moving forward. I wish that commercial/proprietary products were anywhere near as effective at identifying what the users see as problems/limitations, and then the developers behind the product putting those things into effect. Postgresql has come along too in the last 5 years, but not, in my opinion, as much as Firebird has.