Alternative to ODBC

Prior to this program, exporting files from within the D3 database to an SQL Table format required ODBC. Creating SQL Tables was command-line driven, using the verb SQL-CREATE-TABLE. We maintain these commands in the SQLCR file.  There is now a new program called SQL.EXPORT. This program is also command-line driven from TCL, but does not utilize ODBC for the exporting of data. The program is currently setup to work with MySQL only. Also, data will generally be exported once a day through batch processing so data will usually be a day behind.

The syntax is as follows:  SQL.EXPORT sqlcr-item... (options

Multiple files (SQLCR items) can be exported from one command line, simply separate each file with a space. To export all files listed in the SQL Setup, do not specify a filename. To export ALL items found in the SQLCR file, use "*" instead of the filename.

The options include:

L - Print an Export <L>og to the specified printer in SQL Setup

E - <E>mail an Export Log to the specified email address in SQL Setup

Y - Bypasses the "Are You Sure" prompt, with the exception of ORDER and POS.INVOICE. See important notes below regarding these files.

Examples:

SQL.EXPORT INVENTORY (L - Export INVENTORY data and print export log

SQL.EXPORT (YE - Export all files found in SQL Setup & email log, bypassing the "Are You Sure" prompt.

SQL.EXPORT * - Export everything in the SQLCR file. Most customers will NOT do this, but rather setup the key files to export in the SQL Setup Program.

Important Note:  The ORDER and POS.INVOICE files are special cases. These are incremental in nature by default. With any other file, the entire contents of the file are exported, but with ORDER and POS.INVOICE only the last 7 days is sent and the order must be finalized (an invoice date must be assigned). To send the entire ORDER file, the command would be:

SQL.EXPORT ORDER (Y - Warning: This could take a full day on large systems.

POS.INVOICE requires the same syntax.

THEY MUST be run individually as separate processes to achieve a complete export of both files.

Typically, there will be 3 Background Processes setup to export the data:

SQL.EXPORT (Y - Daily

SQL.EXPORT ORDER (Y   - Weekly

SQL.EXPORT POS.INVOICE (Y    - Weekly

Hot SQL Export

Hot SQL Export functions similarly to Hot Backup, but items are exported to SQL. To turn the feature on, a flag is available in the Hot Backup Setup (10.6.x.1), fields 9-11:

9.  Hot SQL Active:

10. Hot SQL Suspend Time:

11. Hot SQL Resume Time:

The Active flag must be set to <Y>. The Suspend and Resume Time are optional, allowing the user to set a time window when exporting data will stop. If the Suspend Time is set, but the Resume Time is left blank, the export process will set a flag for an external process when it is suspended. The export process will then wait for a signal back from the external process to indicate that it is clear to resume exporting.

If a group of items is in the process of being exported once the Suspend Time is reached, the export will complete before the process is suspended.

A one-time procedure is also necessary when starting up the process for the first time. The current settings in SQL Setup Entry (10.6.x.2) must be <S>aved, which updates all files found in field 7 with the necessary programming to capture changes to the data.

Any fatal errors encountered during the export process will send notification to the admin ports, along with an email to the admin email address. The process will stop on any such error. After determining and addressing the cause of the error, the user can restart the process by returning to Hot Backup Setup and re-saving the settings (with the Hot SQL Active set to <Y>). The new feature required enhancements to our export program, which allows for some new command line options. SQL.EXPORT now has the capability of exporting or deleting individual item ids, as well as truncating tables. The syntax for the new options is:

SQL.EXPORT fname (I "id1" "id2" .... (Export individual items)

SQL.EXPORT fname (X "id1" "id2" .... (Delete individual items)

SQL.EXPORT fname (C                  (Clear - truncate appropriate tables)

Item IDs must be enclosed in quotes <">

Abort on Consecutive Errors - If the Hot SQL process encounters 10 or more errors in a row without a successful send, the process will be stopped. This wil prevent a continuous loop of error emails sent until the error is resolved.

A separate email will be sent to the SQL Admin email address explaining that the process has been aborted. To restart the process once the cause of the abort has been dealt with, at TCL type CHECK.SQL.SEND.ACTIVE <Enter>

Update Logic - When deleting items from the table(s) prior to readding them back with updated data, sub-table items will be deleted first, prior to the main table (i.e. in reverse order).

Performance - Hot SQL sends items in groups, rather than one at a time. This increases theperformance of the export.