Introduction to Cross Platform Integration (Part 2 of 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:

$dbh->disconnect;

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:

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