Introduction to Cross Platform Integration (Part 2 of 2)

Triggers: These are wonderful tools if you know how to use them. A trigger is a server-side function that is executed when an operation is performed on a table (select, insert, delete or update). For example, you can tell the database to add a new record in the customer-windows table every time a record is inserted in customer-linux. The following is a PostgreSQL declaration of a rule, which executes a statement when an insert is performed on the customer-linux table:

create rule update_windows_customer_table as

 on

   insert to customer-linux

do

   insert into customer-windows (cust-code, cust-name, cust-phone, cust-address)

      values (new.code, new.name, new.phone, new.address);  

(Again, please check the PL/SQL manual for your specific database engine SQL syntax)
As you can see, this is an extremely powerful technique if both applications you want to integrate use the same database engine. You can use different statements for different operations and maintain the tables consistent throughout the applications.

But sometimes -even when both applications to be integrated utilize the same database engine- the applications themselves contain specific logic or business rules that the data must respect. In those cases we'll just have to integrate them through an interface (Figure 3 -A-). Let's see what the most common cases are in the business field and how we can use existing tools to make them work.

3.d. Scenario I - Interfacing two existing applications:

Figure 1 described a scenario where we have a web server with e-commerce software and an application server administering the Sales and Inventory information. Let's assume that the web server is a Linux distribution using Apache and a Perl-based shopping cart, and the application server is Windows-based with a ODBC compliant database (MS-SQL Server, MS-Access, etc.).

We want a one-way integration from the Windows application to the Linux shopping cart, so new items/customers are entered or modified from just one side of the whole system, in this case from the Windows application.

The first approach is to make the shopping cart program read the inventory and customer information directly from the Windows application.

For example, if your Windows application uses a ODBC compliant database, you can make your shopping cart's Perl scripts read the tables stored in Windows using DBD::ODBC. DBD::ODBC exactly as any other DBI module, so your Perl script just pretends that is connecting to a Linux-based database engine.

 
Depending on the Windows database engine or relational table administrator that you're using (Access, SQL Server, Oracle, XBase) some SQL functionalities may not work: for example table creation and transactions. The exact syntax and semantics of some statements may vary as well, so be careful when constructing SQL queries or updates.
Here are some tips when installing and configuring DBD::ODBC:

-You will need Perl 5.004 or higher (type perl -V to find out your current version)
-You will need DBI 0.93 or higher. You can download the latest version (1.16) along with the installation instructions
-Download the latest version of DBD::ODBC (0.28) tar file from the CPAN
-As usual, unpack the distribution by typing:

-Install the ODBC driver manager (iodbc) that comes with DBD::ODBC. You can find source and instructions to install the binaries under the iodbcsrc directory

-Then, setup the environment variables:

# DBI_DSN=dbi:ODBC:Your_Windows_DSN

# DBI_USER=user

# DBI_PASS=password

# ODBCHOME=/usr/local/iodbc

# export DBI_DSN DBI_USER DBI_PASS ODBCHOME

* Notes:

• The DBI_DSN indicates the connection string to the DBI data source. You should have a DSN (Data Service Name) created in the Windows machine that has the connection to the database (from Control Panel -> ODBC Data Sources). Your_Windows_DSN is the name of the DSN that you see in the 'ODBC Data Sources' option
• DBI_USER and DBI_PASS are username and password to access the Windows database
• ODBCHOME tells Linux the path where you installed the ODBC driver manager, in this case iodbc (you can use a different ODBC driver manager, as long as it is supported by the DBD::ODBC)

-Prepare and install the binaries as any other Perl module:

# perl Makefile.PL

# make

# make test

# make install

-This version of DBD::ODBC works well with MS-SQL Server and Oracle ODBC drivers. If you experiment problems you can post a help message to the DBI mailing list, but please make sure you deliver all the proper information (log of all steps during the build and full detail of environment and software versions, including Linux, Perl, DBI, DBD and ODBC driver manager version). Make sure you really tried before asking for help. Check out documentation at the Perl official site or browse the perldoc pages (perldoc DBI and perldoc DBD::ODBC)

-If you have a special network connectivity layout -like a firewall between servers- you may want to use DBD::Proxy. DBD::Proxy is part of the DBI 1.16 distribution.

-You may also want to look for bridges. A bridge is a piece of software that makes transparent the passage of data between different sources. You can take a look and download the bridge offered by Easysoft.