Datafile SQL Driver

The Datafile Database is a proprietary database – this means that the data within can be read and updated within the Datafile Software suite of programs but not by other programs. To access the data with other programs requires an interface.

Datafile Software, as standard, supplies output routines to CSV or Excel Spreadsheets but these are one-shot outputs and, if the data is updated within Datafile, requires the output to be re-run.

This new option allows for the creation of a ‘mirror’ SQL table of a Datafile Database – the mirror is updated as the Datafile Database is with no impact on standard Datafile processing. This mirror SQL table is then in a format that can be interrogated and reported on in other programs such as Microsoft Access etc.The mirror procedures can link to a Microsoft SQL (SQL Server 2008, SQL Server 2005, SQL Server 2000 and SQL Server 7) or a MySQL table (Server 5.1, 5.0, 4,1, 4,0 and 3.23).

Within the Datafile system each database can be set to be mirror to SQL. If a database is set then whenever a record is created, amended or deleted in that database an entry is written to an interface database called SQLDET.DFD.This contains the SQL table name (the DFD filename without the .DFD part), the file location, and the record number that has changed.

The Datafile SQL Driver, installed separately on a terminal with access to both the Datafile system and the SQL database, is a process which is ‘continually’ interrogating the SQLDET.DFD for new entries. When an entry is added to the SQLDET.DFD it reads the instruction and reads the referenced record in the appropriate Datafile database (there are no file or record lock implications with this in the main Datafile system) and then accesses the SQL database and creates, updates or deletes the equivalent entry in the appropriate SQL table.

The above processes cover the output of data from Datafile Databases to SQL but the system can also be used to import data into Datafile provided the SQL Import tool has been purchased.

When operating (say) a webshop then the shop outputs the orders in a specified format – this is often a CSV or XML file – that are then processed via Datafile tools into a Datafile Database for the generation of sales orders. Alternatively the Datafile SQL Driver can interrogate a SQL table for new sales to populate a work file that can be used for the likes of sales order batch update.

Within the webshops Datafile have developed using JShop, orders are added to the jss_order_header and lines tables. Once the order is marked as paid these are mirrored into df_order_header and lines tables and these tables are processed by the Datafile SQL Driver pulling down the detail and then deleting the entries once processed.