I decided to write this guide and post it here on my website as there is a severe lack in information out the on the web for the intermediate Windows user. Having very little idea about how ODBC connections work and how I would go about pulling data from a SAGE Line 50 database to my MySQL database served on a Linux platform.
This guide will be based on the assumption that you are aware of simple configuration tasks in Windows XP/Vista and that you have a good understanding and control over your own server where we are moving the data to whatever platform you are running it on.
In my case I was moving data from a Sage Line 50 database hosted on a Windows Vista machine to a MySQL 5 server running on Ubuntu Server Edition 9.04. I used a third machine to translate the data which was running XAMPP (Apache2 with PHP) on Windows Vista. You could use another XP/Vista machine like I did or you could use the machine that Sage Line 50 is on. I looked for a SAGE Line 50 ODBC driver for the Linux platform but did not find anything so it was nessessary for me to use a middle man machine of the Sage Machine itself. If anyone knows of a Sage Line 50 ODBC driver for Linux, please contact me as I would be very keen to hear about this. This was annoying as I had to set up another server. Even though I was only using PHP's CLI I used XAMPP for the set since there is no configuration required and it is easy to remove afterwards.
Now onto the how to:
The first thing that you will need to do is set up the Sage Line 50 ODBC driver on the computer that will be doing the 'translation'. You will find an install package for the Sage Line 50 ODBC driver inside the Sage Line 50 directory on the machine that Sage Line 50 is installed on.
Once the install package is complete you will need to set the up the data path for the Sage Line 50 ODBC driver from the Windows Control Panel. Go to the Windows XP/Vista Control Panel (You will have to select something like 'View Classic Mode' somewhere to see all the functions in the Control Panel). Open up the ODBC configuration function. You will see in the list something like 'SageLine50v13'. Edit this entry to set the data path. You need to point to the network location where the Sage Line 50 data is stored. Usually under the Sage Line 50 installation directory in a folder called ACCDATA. To access this over the network, Windows file sharing will have to be set up (READ ONLY) on that directory. So set your data path as something like '\\192.168.1.100\SAGE\ACCDATA' .
Once that has been done you can start writing your PHP script. Here's an example:
<?php
$odbc['user'] = ?username?;
$odbc['pass'] = ?password?;
$odbc['dsn'] = ?SageLine50v13?;
$odbc['table'] = ?SALES_LEDGER?;
$fields = array();
$results = array();
$conn = odbc_connect($odbc['dsn'],$odbc['user'],$odbc['pass']);
if (!$conn) die(?Error connecting to: ?.$odbc['dsn']);
$sql = ?SELECT * FROM ?.$odbc['table'];
$r = odbc_exec($conn,$sql);
if (!$r) die(?Error executing query: ?.$sql);
for ($i=1;$i<=odbc_num_fields($r),$i++)
$fields[odbc_field_name($r,$i)] = odbc_field_type($r,$i);
$x=0;
while (odbc_fetch_row($r)) {
for ($i=1;$i<=odbc_num_fields($r),$i++)
$results[$x][odbc_field_name($r,$i)] = odbc_result($r,$i); $x++;
}
odbc_close($conn);
?>
So now you have two arrays, $fields and $results, with all the information in to move the data over to your MySQL database.
It is just a case of structuring the table using the $fields array and then looping through all the data in results and adding it to your new table on your MySQL database. Pretty simple really.
I am currently fine tuning the script I am using for this, since it will eventually be set up on the Sage Line 50 machine to run as a Scheduled Task in windows to INSERT or UPDATE data on an hourly basis. Once I have completed this script I will share it here will all the nessesary information.
It is a shame that there is little to no information for us casual developers to access on Sage and it is a shame that the Developer Community Subscription is so expensive. I hope that my article will help someone trying to do something similar.
Clear applications for this that I can think of right away would be if you needed your Sage Line 50 data linked in with your osCommerce data.