Originally Published: Friday, 8 June 2001 Author: Marcelo Pham
Published to: develop_articles/Development Articles Page: 1/1 - [Std View]

Introduction to Cross Platform Integration (Part 2 of 2)

In part two of this detailed look at platform integration, software architecture and networking issues with Linux, consultant Marcelo Pham concludes his exclusive Linux.com article series with a complete overview and code walk-through of application and database integration strategies for cross-platform data integration in a business environment.

In the last issue of this series we talked about the roles Linux can play when networking in a mixed environment. In this second part of our look at Linux Cross-platform integration issues we will cover some basics you need to know in order to integrate applications from different platforms and databases with your Linux box.

3. Integrating Applications

3.a. Introduction
Most companies with five or more years in business have a computer system with a software package to run the business. This software system can be Accounting (General Ledger, Accounts Receivable, Accounts Payable) Distribution and Sales (Inventory Management, Sales Order, Purchase Order, CRM) or Groupware (Payroll, Mail, Collaboration tools, Project Management) related.

Considering all the factors we talked about in the last installment (Internet and e-commerce expansion, acquisitions and mergers) it's easy to see that the bigger the company the more of software and platforms it has to integrate. The ideal solution would be to merge all the applications into one single platform, one single database engine and one single environment, whatever that is. Correct? Yes, that is true -in Utopia. In the real world, that would cost a lot of money, would take a lot of time, and in today's frenetic business climate would not be even close to be feasible. We have to learn how to make software and platforms interact with a realistic and solid solution.

You will find out that there are thousands of different applications, and most of the time you won't have time to rewrite and migrate either application to the 'opposite' platform: The only solution will be to make them interact. We already know how to make them 'see each other' on a network, let's see how we can make them work together.

3.b. Types of integration
There are two general types of integration: one-way and two-way.

One-way integration is when you choose one application to be the 'master' and the other the 'slave'. The slave retrieves and sends data from and to the master application, but the main and updated data is always stored in the master. A classic example of one-way integration is between the Distribution application and the E-commerce software (Figure 1). In this case we assume that the Web Server is a Linux box and the Application Server is a Windows based server.


Figure 1.

Two-way integration is when two applications need to keep the same data updated up-to-the-second. This means -in database terminology- the data is replicated between two different databases for two different applications. A good example is the interaction between the Accounting module and the Sales module (Figure 2).


Figure 2.

3.c. Integration Levels
You would be very lucky if the applications you have to interface together use the same database engine. If that is the case we can integrate both applications at the 'database level' (Figure 3 -B-).


Figure 3.

Depending on the database engine, you can use database tools to update the data from both applications, ensuring integrity and consistency:

Server-side functions: Also called stored procedures, these are functions or procedures that are executed from the database server instead of from the user application. A server-side function can be written in the native PL/SQL itself (ideally) or through Perl, C, C++, Tcl/Tk, Python or PHP as well.

For example, let's say you have two different applications (one Linux-based and the other Windows-based) and each run with an Oracle database but using different customer tables. If your data does not need to be up-to-the-minute fresh, you can execute a stored procedure at the end of the day to update the Windows-based tables. This can be easily achieved using a cron job with a small Perl or C script that executes the server-side function.

Syntax and semantics may vary from database to database. For example in PostgreSQL you must declare CREATE FUNCTION, while in Oracle is CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION. As far as I know (the version I was working with was 3.23) mySQL does not support stored procedures. I believe it is in the To-Do list, however. But, as always, you can emulate a stored procedure by executing the command from a script (C, Perl or whatever you choose). For instance:

The Perl script:

#!/usr/bin/perl -w

use strict;
use DBI;

# End of day procedure: update customer table in Windows-based program (upd.cgi)
# Linux-based customer table layout
# customer-linux:
# create table customer-linux (code char(5) primary key, name char(50),
# address char(100), phone char(25));
# Windows-based customer table layout
# customer-windows:
# create table customer-windows (cust-code char(5) primary key, cust-name char(100),
# cust-phone char(25), cust-address char(100));

print "Executing end of day customer update. Please wait....\n";

# Connect to the database:
my $dbh=DBI->connect("your_connection_string";"your_user";"your_password");
# SQL command:
my $cmd=
     "insert into customer-windows (cust-code, cust-name, cust-phone, cust-address)
        (select code, name, phone, address from customer-linux except
         select * from customer-windows)";

# Execute command:
my $sth=$dbh->do($cmd) || die "I had problems updating the table!!!!\n";

# Disconnect from database:

print "Windows-Customer table updated successfully...\n";
# End of script

(This SQL statement is customized for PostgreSQL. Please refer to your preferred database enginePL/SQL manual to find out more about advanced queries)

The scheduled job in your /usr/spool/cron/tabs/root file -or /usr/spool/cron/crontabs/root in Unix- (every weekday at 11:00 PM) using:

00 23 * * 1-5 ./home/taz/upd.cgi

Assuming that upd.cgi is located under /home/taz and has correct permissions to read and execute, this line must be added to your root crontab file, please refer to your Unix or Linux man pages for more information (man crontab).

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
   insert to customer-linux
   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:

# gunzip DBD-ODBC-0.28.tar.gz
# tar xvf DBD-ODBC-0.28.tar

-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

* Notes:

-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.

The second approach if you're using a third party e-commerce package and it's too complicated to customize, is to import and 'refresh' the data every once a while.

First, you'll have to find out how the files are saved in the shopping cart software (CSV file, mySQL, etc.)
ii. Then, from your Windows application, you should generate a file (usually a raw text file) containing all the necessary fields of the new item or customer to be updated in the shopping cart (both in separate text files). For example, a CSV file containing customer number, name, address, city, state, zip, country and contact name would look like:

ALA010,Alamo Rent-A-Car (Miami),8780 NW 18 Terr,Miami,FL,33130,USA,Marcelo Pham
ALA020,Alamo Rent-A-Car (New York),10210 Forest Hills Av.,Long Island,NY,30210,USA,
Louis Falco

iii. You are (supposedly) using Samba (please refer to Part I), so you can share a Windows directory and mount it from Linux through smbmount. You should create a mount for the directory that contains the text files created by the Windows application.
iv. The other way around would be to share a Linux directory and map it from Windows through the 'Windows Neighborhood', and save the text files there (either ways will work fine)
v. Create a Perl script that reads the text file to create or modify entries to your shopping cart database file (you may use the following sample as a template). Don't forget to rename/delete the text file after importing it!

#!/usr/bin/perl -w

# This is an example of how to make a little script to update a database
# from a text file generated by a Windows (or any other different platform) prg so they
# can synchronize common data. You can use this as a template and adapt it to your specific
# needs. Notice that I'm using a text file (/home/taz/file.csv) with these fields:
# Customer, Name, Address, City, State, Zip, Country and Contact name
# (all separated by commas).
# This is just a sample, you might want to use something more sophisticated
# if your fields contain also commas inside each field (like the Perl Text
# manipulation module) or simply use a different separator such as the pipe (|) or
# a semicolon (;).

# If this process will be automated, you may choose to write to a log file instead of
# printing the results on screen, along with the date and time that the update
# occured.

# If you have question or comments, please email me at marcelo@linuxmail.org
# Enjoy!!! (Taz)

use DBI;
use strict;

# Open database connection:
my $dbh=DBI->connect("your_connection_string";"your_user";"your_password");

# Open text file:
open (TXTFILE,"/home/taz/file.csv") || die "Couldn't open interface file !!\n";

# Let user know that it's processing:
print "Please wait... I'm updating the Postgres database...\n";

# Loop to read text file
my $newcus=0;
my $updcus=0;

while (TXTFILE)
   my ($cus_code,$name,$address,$city,$state,$zip,$country,$contact)=split(/,/);
   # or use the field separator you chose, for example ...=split(/|/); to use the pipe

   # Verify if exists
   my $sql="select code from customer where code='$cus_code'";
   my $sth=$dbh->prepare($sql) || die "Couldn't prepare statement!!\n";  
   $sth->execute() || die "Couldn't execute statement!!\n";
   # Store query result
   my @rows=$sth->fetchrow_array;

   # Verify if code already exists
   if ($rows[0] eq '')
        # Row does not exist, I have to insert a new record:
        my $sql="insert into customer values ('$cus_code','$name','$address','$city',
        my $sth=$dbh->do($sql) || die "Couldn't add new customer!!\n";    
        # Inform user what I am doing:
        print "Patience! I'm adding the new customer $cus_code...\n";
        # Row already exists, I have to update the existing information:
       my $sql="update customer set name='$name',address='$address',city='$city',
                 where code='$cus_code'";
        my $sth=$dbh->do($sql) || die "Couldn't update customer info!!\n";    
        # Inform user what I am doing:
        print "I'll be done in a minute! I'm updating info for customer $cus_code...\n";
      }  #endif

 } #endwhile

# Close text file

# /********************************************************/
# /* You can add here updates for other tables as well !! */
# /********************************************************/

# Disconnect from database

# Delete file (you can just rename it with the date and time as part of the name)
unlink "/home/taz/file.csv";

# Inform user that process was completed:
print "I'm back! I added $newcus new customer(s) and updated $updcus customer(s). Happy?\n\n";

# End of script
vi. Here again you may also use a crontab job to execute the Perl script and update the shopping cart files, for example, perhaps every two hours

These are just ideas and you can work around or combine them to synchronize your e-commerce application data. Always try to make it automatic or 'hands-free' so the customer (or you as the administrator) do not have to trigger or pay attention to it every day.

3.e. Scenario II - Writing a new Linux module to interact with an existing application:

If you have to write your own Linux-based piece of software that has to interface with a Windows application (for example, what we've seen in Figure 2), here are some tips:

Try to use a single database engine. For example, if your Windows application uses MS SQL Server, you should write your Linux application using the same tables and access them remotely. If you use Perl, for example, you can use DBD::ODBC to connect and operate the MS SQL Server tables from your Linux application.

If your Windows application uses Oracle, try to move the Oracle server from Windows to Linux or Unix/Solaris along with the data. From your Windows application, change the DSN or system configuration so it connects to the Linux box instead of to Windows. From your Linux application use DBI::Oracle or the provided connection tools from your preferred programming environment.

ii. If you can't use the same database, choose a stable GPL database engine (Postgres or mySQL) and write a Perl script to synchronize data between both databases. You can use ODBC as the 'data hub' as well.

iii. If the Windows application uses a database that is not ODBC compliant, utilize the 'update' or 'import' scheme seen above, but from both sides -or two-way integration-. This is, write a Perl script that reads a text file generated from the Windows application to update the tables on the Linux side, and write a Windows applications that reads a text file generated by the Linux application and refresh the information stored in the Windows side (Figure 4). You might want to encapsulate both jobs in the same script. For instance, the Perl script could read the text file and at the same time generate the text file that the Windows application needs (same thing with the Windows program), just make sure you synchronize the automatic execution of both import programs in a sensible way. Remember to name the text files in a different way or in different directories, and to rename or delete them after they are processed

iv. One last thing, try to maintain a log file of all the import results so you will know what happened if something went wrong. Also, try to make your update programs fault-tolerant: These should be automated processes and should only need your supervision, not your intervention. For example, if one of the import jobs fails to execute or cannot open the text file your programs should know how to handle that.


Figure 4.


3.f. Scenario III - Simultaneous environments
Sometimes there are cases where you will have no other choice but to live with one operating system and make everything else work around it. Let me create a picture of this for you using a situation with a customer of mine. Dialtone Internet is the number one Linux dedicated hosting company in Florida, still growing and working hard to be number one Linux ISP in the U.S. They currently have around 2500 Linux servers and they had separate software applications to manage their Invoices, Help Desk requests and Inventory. They wanted to merge all of these applications into a single package that included other Accounting modules like General Ledger and Accounts Payable. After some research they decided to go with a Windows-based Accounting package that would include all the functionalities of the applications they were using. This Windows-based application uses a client-server architecture with a fat-client installed on every Windows-based workstation.

Now, here was the problem: 50% of the users are Linux help desk technicians, they maintain all the Linux servers through -of course- their Linux workstations (RedHat 6.2). Since the Windows-based application only accepts connections from Windows-based fat-clients, I recommended that they install a Windows emulator on those Linux workstations and install Windows and the application fat-client on top of Linux using the virtual machine software. In this way help desk users could use their Linux tools as usual, and also inquire or update information from the Windows-based application. VMWare is a commercial Windows emulator and it can load a Windows virtual machine in a X-Window session. You may want to download the free project of VMWare called FreeMWare. Another choice is to download Win4Lin, another Windows virtual machine, but like VMWare, this is a commercial package and you will have to pay the license if you want to use it in a business environment.

Another choice you have, if you wish to develop an application from the ground up, is to use an implementation of the Windows API (Application Programming Interface) through your preferred X-Window. This project is called WineHQ and you can download it for free. This is not an emulator so you won't need to install Windows, although you can use native Windows Dynamic Link Libraries to enhance your programs.

(On a side note, I think it's is fair to say that if you ever want to get a dedicated Linux server I highly recommend this company I was doing consulting for: (Dialtone Internet). I've been working with them closely and I can tell how professional they are and how much they care about the Linux community.)


4. Final thought:

As I said before, the ideal thing to do when faced with making different applications and operating systems work together would be to remake all of them under one single programming language using a single database and running on a common operating system. But the reality is: businesses often do not have the time, money or expertise to coordinate the technicalities, programming languages, networks and other challenges. Business is about time and money, the quicker and cheaper the solution, the better. It is your responsibility as a consultant or programmer to tell your customer what would be the best and cheapest alternative. Never get carried away by the wonders of technology, sometimes a fast solution is better and than a complex, sophisticated, elaborate or fancy piece of software. But not always. Remember that you're playing in the business ground -where every minute and penny counts-, they're not playing in yours.

Try to be creative, brainstorm and evaluate several solutions and then decide which one will fit best your customer's needs, be a true non-conformist and think that there will always be a better way to do it. Use your imagination, combine alternatives and, as usual, document every single step of what you did, what you think and what you plan to do. Executives like that. Good luck with your projects!


Marcelo "Taz" writes develop-related articles for Linux.com. He does enterprise consulting and programming from sunny Miami. When not hacking, you may see him riding his Harley Davidson on the Florida's Turnpike (heading nowhere).