Introduction to Cross Platform Integration (Part 2 of 2)

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',

                 '$state','$zip','$country','$contact')";

        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";

        $newcus++;

      }

   else

      {

        # Row already exists, I have to update the existing information:

       my $sql="update customer set name='$name',address='$address',city='$city',

                 state='$state',zip='$zip',country='$country',contact='$contact'

                 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";

        $updcus++;

      }  #endif

 } #endwhile

# Close text file

close(TXTFILE); 

# /********************************************************/

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

# /********************************************************/

# Disconnect from database

$dbh->disconnect;

# 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

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.