There are four years, learned how to use a feature called DBLINK, an additional module of Postgres, which lets you connect to another database, including remote.
I also had the opportunity to use a very similar feature in Oracle Database Link.
So I was intrigued about having this feature in Firebird, because in some cases it is useful.
For example: I have a situation of a database with records of addresses of people, but I do not keep this basis the names of streets, or postal code and even neighborhood, keep only one code from the street.
In another database, have all records to street addresses, zip code, districts and municipalities of the country all.
I could do it on the same basis of my system, but this specific base addresses, this pattern in the post, suffers almost annual update and is referenced by, via DBLINK for several other systems.
So, imagine that without DBLINK, all systems would have to have its own copy of the data.
I wish I had this in Firebird, but not found, went on sites that have forums, articles, tips about it, but nothing of this feature, the only advice that can be done via application and a promise to version "2.5".
Then, via the application means that the system is using different data bases, need to connect at each base, the main base and the other in the case of addresses.
Would not be possible to use the feature in just a "select", but use the resources of the programming language, be it java, php and Delphi.
So, I decided it would be interesting and useful to add this feature.
I used it for the ability to accept Firebird External Functions (UDF) and be able to call functions written in other languages, such as C ++ or Delphi.
I'm used to Delphi, Windows environment, so I wrote a library for these characteristics.
I am offering a set of files, where one has the type DLL and SQL.
You must take the steps to copy the DLL file in the folder designed for it, the Firebird installation.
In my case, I copied to "C: \ Program Files \ Firebird \ Firebird_2_1 \ UDF"
Where even already have some files distributed.
Then open the SQL file and using a tool to manipulate the SQL (IBConsole or other) data, do run scripts.
Note the examples of "select", the own SQL file and make your tests.
This function library uses ODBC to make the connection, so it is possible to make a connection with many databases and even Excel without using features unique to each type of bank would like something generic.
I did several tests, connecting to Firebird own, and can also fetch data from other DBMSs.
Tested Postgres and Oracle, I believe that if you install an ODBC driver for mySQL, it is also possible to connect.
Tested with Excel and it worked.
So all this gives good possibilities.
Environmental requirements and tests:
- Firebird 2.1
- Windos 7 32bit
- ODBC drivers for specific and desired connections (for PG Drive, Drive for Oracle)
- Place the DLL file in the proper place at the Firebird.
- Run the contents of the SQL file, which contains the declaration of the UDF and a "Stored Procedure" that is ready for use.
Examples of queries:
Select * From dbquery('DBNAME','select * from table_name') ;
Select c.*, t.field2, t.field3
From dbquery('DBNAME','select * from table_name') as t
Join tbl_local c On c.local_key = trim(t.field1) ;
Select *
From dbquery('DBNAME; PASSWORD=12345; USER ID=dbadmin',
'
Select * From table_name
Where rownum <= 10000
') ;
Select 'p1' pla, p1.* From dbquery('XLS_FILE','select * from "SHEET NAME1$"') p1
Where recnum <= 2254
Union
Select 'p2' pla, p2.* From dbquery('XLS_FILE','select * from "SHEET NAME2$"') p2
Where recnum <= 314
Free Download:
dblink2firebird.rar in Google Drive
Nenhum comentário:
Postar um comentário