How to..?

How can a load be written that only uses a script instead of using a Macro or Forms Playback load?

Scripts can add functionality to Macro and Forms Playback loads but they are most powerful when the load is entirely implemented using a script. With this approach you do not use a Macro or Forms Playback load and instead the script performs all data manipulation and loading functionality. This makes Data Scripting very powerful because almost anything can be achieved.

To create an entirely script driven load follow these steps:

1. Create a new load in DataLoad (File->New or toolbar icon)
2. Open the DataLoad Options window (Tools->Options or toolbar icon)
3. Click on the Macro Loads tab and you will see the window shown on the right
4. The first check box on this tab is called "Enable Macro Loads" (circled in red in the screen shot). This controls whether DataLoad runs a Macro load when a load is started. Ensure this checkbox is not checked and then click OK. Having made this change nothing will happen when a user starts a load, so it is now time to create a script that will perform the load.
5. Having closed the Options form click on the Script tab, which is located just above the DataLoad spreadsheet
6. Assuming a load has not already been created, you will be asked by DataLoad if the template script should be loaded. Answer "Yes" to this question.
7. PHP code can now be added to the PHP event handlers. Most code will probably be added to the onStartLoad function because this will be run when the user starts a load. When ready to test the script the developer should select "Start Script" from the Script menu.

The DataLoad load will now not execute any of the Macro load functions and instead the load will be driven by the PHP script.

How do I add data validation to a Macro or Forms Playback load?

First, look at the built-in data validation available in DataLoad. If custom data validation is required then this can be implemented using a PHP script. A new script should be created and the data validation code added to the OnStartLoad event handler. The script will need to analyse the data in the DataLoad spreadsheets and that can be done using the DataLoad Scripting PHP functions. When the user is ready to run the load, whether it is a Macro or Forms Playback load, they should press the start load button. This is as normal for Macro loads but this button is not normally used for Forms Playback loads, however in this case we use this button to trigger the data validation. Alternatively, with Forms Playback loads it may be appropriate to add the validation code to the OnSave event handler. With that approach the validation code will run every time the file is saved and there is no need for the user to press any additional buttons. Where data issues are found they should be reported to user using the Console or MessageBox, as described below. If the load is a Macro load and the validation issues mean the load should not progress then the CancelMacro() function can be called and this will stop the load from continuing.

Is a log of PHP warnings & errors available to assist with debugging scripts?

All PHP errors and warnings are written to the phperrors.log file in the PHP directory.

How can I provide feedback to the user when the script is running?

Providing feedback to the user is very important otherwise they will not know what the script is doing. This is especially so when the script is loading the data, as described above. There are a number of ways to provide information to the user:

Hourglass Cursor - The script can set whether the mouse cursor is an hourglass. During long running operations the cursor should be set to an hourglass to show that the DataLoad script is busy. This can be done with the SetHourglass() function. This function takes a boolean parameter, i.e. it should either be true or false. For example, SetHourglass(true); will change the cursor to be the hourglass. It is important to remember to change the cursor back to normal when the long running operations have completed.

Pop-up Messages - DataLoad can display pop-up messages that provide information to the user. The messages and buttons displayed are both configurable and the user can also be prompted to supply some information. The details of the button pressed or data supplied by the user are returned to the script. The MessageBox(), InputQuery() and InputBox() functions should be used to provide this functionality.

Console - The Console can be shown at any time but is primarily used to show the user the progress of a script driven load or any other long running script activity. A screen shot of the console is shown on the right. The console has progress bars and text to show the progress of the load and two text boxes to shows information and error messages. The functions which control the console are ShowConsole(), HideConsole(), SetConsoleData(), ClearConsole() and SendMsg().

How do I connect to a database, either to load or retrieve date?

One of PHP's strengths is its massive function library and this includes support for all major database platforms, including Oracle, MS SQL Server, MySQL and IBM DB2. These functions can be used to perform all the required operations on these databases and thus data can be loaded in to these databases from DataLoad.

DataLoad Scripting comes fully configured ready to connect to Oracle databases. The PHP Oracle (OCI8) library and Oracle's Instant Client libraries are provided and pre-configured. Connecting to and using Oracle databases from within a DataLoad script just requires that these the OCI8 functions are used. Alternatively, included with DataLoad Scripting is a PHP class that provides a simplified interface to the OCI8 functions. Not all functionality is available in this class so the standard PHP OCI8 functions must be used in some loads but the DataLoad Oracle class is simple to use and covers common requirements. The DataLoad Oracle class is fully documented here.

If Oracle database connectivity is required in a load then at some stage the connection details must be collected. DataLoad includes a window to collect the connection details and this can be shown using the GetDBConn() function. A screen shot of this window is shown on the right. The connection details can be supplied as an Oracle TNS alias, assuming one or more tnsnames.ora files are available on the PC, or the "raw" details may be provided (database SID, server IP or hostname, and database port). The connection window also includes fields for the username and password. All these details are returned by the GetDBConn() function when the user clicks "OK".

Example loads that load data directly in to an Oracle database are included with DataLoad and it is recommended that these are reviewed to show how scripts can integrate with databases.