Home » Categories » Solutions by Business Process » SQL

Datafile SQL Driver

Datafile Software

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.

Custom Fields
  • Release ID: Standard
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Database Refresh - Empty Table and Re-Import Data
Viewed 2284 times since Mon, Jun 11, 2012
Connection to SQL Server via ODBC and SQL Log Housekeeping
Viewed 4208 times since Fri, Jul 21, 2017
Datafile SQL Configuration
Viewed 1875 times since Thu, Jul 5, 2012
SQLDET Database Structure
Viewed 4310 times since Mon, Jun 11, 2012
Installing the Datafile SQL Driver
Viewed 2601 times since Thu, Jul 5, 2012
Suspend SQL Mirroring During Night / Weekends
Viewed 1922 times since Fri, Oct 26, 2012
Configuring the Datafile System
Viewed 2066 times since Thu, Jul 5, 2012
Update Individual Records to SQL on Nominal Update
Viewed 2638 times since Fri, Oct 26, 2012
Running the SQL Manager
Viewed 2226 times since Thu, Jul 5, 2012
Additional SQLDET.DFD Maintenance Options
Viewed 8935 times since Mon, Jun 11, 2012