Command line scripting
Scripting allows users to write special functions in a plain text files and pass the name of this file as a command line argument to WinSQL. WinSQL runs these functions in a sequential order.
The following rules apply when using a script file.
- The file extension of a script file must be one of the two values:
- .wxf- Example: MyScriptFile.wxf
- .winsqlscript- Example: MyScriptFile.winsqlscript
- Lines starting with two dashes are considered comments and are ignored at runtime
- Empty lines are ignored
- Function names are case-sensitive and must include open and close parenthesis even if parameters are not required.
- Parameters must be enclosed by a double quote character
To run the simply pass the file name containing a script as a command line argument to WinSQL, for example:
winsql MyScriptFile.wxf
Function reference
The following is a list of available functions you can write in the script. Every function starts with
wnsFunc_.
| Function name: |
wnsFunc_AutoLogin |
| Description: |
Establishes a connection to a database. This should be the first function in your script |
| Parameters: |
This function takes 3 parameters in the following order.
- DSN name - This is either a DSN name or connection string.
- User ID - Login ID for the user. Set this value to a -1 if the first parameter is a connection string,
- Password - Set this value to -1 if the first parameter is a connection string.
|
| |
| Function name: |
wnsFunc_AutoTerminate |
| Description: |
Terminates WinSQL's process. This should be the last function in your script. Any script appearing after this function is ignored. If you skip this function, WinSQL will run the script and will display an empty window at the end. |
| Parameters: |
There are no parameters for this function |
| |
| Function name: |
wnsFunc_DataDiff |
| Description: |
Runs the Data-Diff wizard for the given template file. You must use the Data-Diff Wizard in WinSQL and create a template before using this function.
|
| Parameters: |
Takes the name of the Data-Diff template file name as the parameter
|
| |
| Function name: |
wnsFunc_ExportData |
| Description: |
Runs an export template. You must create an export template using WinSQL's GUI.
|
| Parameters: |
Expects 1 parameter, which is the name of a template file.
|
| |
| Function name: |
wnsFunc_ImportText |
| Description: |
Runs a text import template. You must create an import template using WInSQL's GUI.
|
| Parameters: |
Expects 1 parameter, which is the name of a template file. |
| |
| Function name: |
wnsFunc_HtmlExport |
| Description: |
Exports to HTML file(s) based on a template. You must create an HTML export template using WinSQL's GUI
|
| Parameters: |
Expects 1 parameter, which is the name of a template file. |
| |
| Function name: |
wnsFunc_RunScript |
| Description: |
Runs any SQL script saved in a file. Multiple queries can be separated by a query separator.
|
| Parameters: |
Takes 2 parameters in the following order.
- SQL file name - Name of a file containing SQL queries
- Output file name - This file will contain results from a SELECT queries and/or record affected count for DML and DDL queries.
|
| |
| Function name: |
wnsFunc_SchemaDiff |
| Description: |
Runs schema-diff wizard for the given template. You must create a Schema-Diff template using WinSQL's GUI.
|
| Parameters: |
Expects 1 parameter, which is the name of a template file.
|
| |
| Function name: |
wnsFunc_SendStatusEmail |
| Description: |
Sends status email.
|
| Parameters: |
This function is overloaded - meaning it can take either 2 parameters or 5 parameters.
2 Parameter Version
- Email option - This can either be 1, 2, or 3. If this value is 1, an email is only sent when the status of every previous task is success. When this value is 2, an email is only sent when any of the previous task generate an error. When this value is 3, email is sent regardless of the error status.
- Attachments - Name of the file to be attached. Multiple file names can be separated by three pipe characters (|||). You can use $FILE_ATTACHMENTS string as the name of a file if the file name should be determined from the previous script. For example, when you run an Export template to a text file, the name of the output file is read at run-time. Refer to the example below.
Note: This function gets the recipient and sender email address from the WinSQL global configuration, which can be set by clicking Edit/Options in WinSQL's GUI.
5 Parameter Version- Email option - same as above
- Attachment - same as above
- Recipient - Email address of the recipient. Multiple addresses can be separated by a comma.
- Sender name - Name of the sender
- Sender email - Email address of the sender
Note: In either versions, the body of the email is generated by WinSQL and is based on the tasks that are run prior to calling this function.
|
| |
| Function name: |
wnsFunc_ExportDataBag |
| Description: |
Exports data stored in a Databag to a table
|
| Parameters: |
This method takes 5 parameters in the following order.
- Databag File name
- Target DSN
- User ID for connection
- Password
- Target table name
|
| |
| Function name: |
wnsFunc_SwitchDatabase |
| Description: |
Switches the connection context to a different database. This function is only meaningful when your back-end database supports this concept. For example, Microsoft SQL Server and Sybase support switching to a different database, but Oracle does not.
|
| Parameters: |
Takes one parameter containing the desired database name.
|
| |
Examples
Example 1
Runs SQL scripts saved in a file called BackupDB.sql.
-- Establish connection to a database. The DSN name is 'testData'
-- User id is scott and tiger is the password
wnsFunc_AutoLogin("testData", "scott", "tiger")
-- Switch the database to a different value
wnsFunc_SwitchDatabase("Northwind")
-- Following function will run the scripts in BackupDB.sql file
-- Output logs are saved to BackupDB.out file
wnsFunc_RunScript("C:\data\BackupDB.sql", "C:\data\BackupDB.out")
-- Send status email if the script fails or succeeds
wnsFunc_SendStatusEmail("3", "C:\data\BackupDB.out")
-- Finally terminate WinSQL
wnsFunc_AutoTerminate()
Assume the above script in saved in a file called TestScript.wxf. The following command will execute the script.
c:\Program Files\Synametrics Technologies\WinSQL\winsql "c:\data\TestScript.wxf"
Example 2
The following script runs an export template that exports data from a table to a text file. The name of the generated text file is automatically read from the export template and is attached to the email. This is done by specifying
$FILE_ATTACHMENTS as a file name in wnsFunc_SendStatusEmail function.
Another important information to see in this example is that you do not have to call the wnsFunc_AutoLogin function. This is because the connection parameters are specified in the export template file, which is 24Hour.wet in this case.
-- Execute export routine
wnsFunc_ExportData("C:\data\24Hour.wet")
-- Send status email
wnsFunc_SendStatusEmail("3", "$FILE_ATTACHMENTS")
-- Finally terminate WinSQL
wnsFunc_AutoTerminate()
