The SQL Statement operation allows the
user to define any valid SQL statement that SLIK will execute during batch
processing. Typically these SQL
statements are used to execute pre and/or post processing steps before and/or
after performing a transfer.
Generally most SQL statements in SLIK are “UPDATE” and “DELETE” types that do not return any rows. However, SLIK also has a simple built-in ODBC reporting feature that executes any SQL “SELECT” statement and emails the resulting data rows as either a “CSV”, “HTML” or “XML” formatted file as an email attachment. This functionality can even be made conditional on the availability of data by using the “Conditional comparison operator” option available in the SQL Statement properties form.
For non-conditional SQL Statements
SLIK uses the system reported SQL error status to determine the success of the
job.
A database procedure can be called as
part of either an SQL Statement or Polled SQL Statement
operation.
This may contain constant values and/or SLIK run-time variables as
arguments to the database procedure. When
SLIK calls a database procedure it is possible to have a single integer return
value passed back to the SLIK server
. This is
accomplished by using the following ODBC syntax in the SQL Text
column;
{? = call db_procedure_name(argument1,argument2)}
This syntax is specific to ODBC and
ensures the ODBC driver will convert it to the correct native SQL syntax to suit
the particular target database.
For example: the call above would be
converted to;
EXEC ? = db_procedure_name argument1, argument2
if invoked for a MS SQL Server
database.
The return value is implied by the
“? =” notation. If specified,
SLIK reads the return value and uses it to determine the success of the
statement. If the procedure is
defined as a Polled SQL Statement then the return result has to equal the
Condition
for the
statement to be considered successful. Otherwise,
if the procedure is defined as a normal SQL statement then the standard is to
treat negative values as an error condition, zero as successful and a positive
value as success with warnings. So
depending on how the “Processing Options” have been configured a negative
value could be used to terminate further processing of jobs in a batch.
Note: if no Condition is specified SLIK will assume the default value
“>=0” as successful.
The SLIK server will only catch any errors at runtime. No attempt is made by the SLIK Interface Designer to validate the statement entered. The ODBC View utility included with SLIK is useful to verify SQL statements run correctly against the DataSource.
SQL Statement operations are created
using the following form:
|
The Statement ID field holds the
system generated unique identifier. The
Statement ID field automatically populates on creation of the Statement
record. The Statement record
is created when you click on
OK to save and close the new record. Field type: system generated |
|
|
This field is where the database
the SQL Statement being created will act on.
This field is where the
datasource the SQL statement being created will act on is recorded.
Field type: look-up |
|
|
This field holds the actual SQL
Statement
string.
Note: some databases have non-standard syntax or proprietary
extensions for a number of SQL statements. Care should be taken to ensure
the SQL entered will be interpreted correctly for the specified database. Field type: - free-text |
|
|
The condition field is used to
set the parameters that the SQL Statement result must meet. Each time the SQL Statement is run SLIK will check to see
if the result returned is equal to the value in Condition
fields. If the SQL result returned
equals the values specified in the Condition
fields then the SLIK server continues processing any subsequent jobs. If the process times-out then by
default further jobs are not processed.
This is dependent on how the “Processing options” have been
configured for the Job. |
|
|
Polled SQL Statements are
defined so that only a single integer value results from the query or
database procedure call. Polled
statements are executed periodically until either the desired result is
obtained or the request times-out. Poll
Count
– The
SLIK server executes the command up to Poll
Count times sleeping the specified Wait
Time seconds between each
poll. Wait
Time
–
the Wait Time
field
determine how long the SLIK server
waits
before executing another poll. For example: given a Condition
of 1 on an SQL statement
like: SELECT COUNT(*) FROM
UBE_RPT_STATUS WHERE
JOBID =
123 AND STATUS = ‘Complete’ ensures the balance of the jobs
in a batch do not commence until the UBE report has completed
successfully. When the SLIK server performs a
shutdown request, it interrupts any pending polled SQL statements. |
|
|
None
– the None option indicates that a report based on the SQL Statement is
not required. CSV
(Comma Separated Values) – the CSV (Comma
Separated Values) option indicates that a report based on the SQL
Statement will be generated in CSV format. HTML
(Web Page) - – the HTML (Web Page) option
indicates that a report based on the SQL Statement will be generated in
html format. XML
(Web Data) - – the XML (Web Data) option
indicates that a report based on the SQL Statement will be generated in
xml format. If either CSV, HTML or XML
formats are selected the following two fields become active. |
|
|
An entry in this field indicates
to whom (group or person) a report generated as a result of the SQL
Statement is to be sent to. The
report will be sent as an email attachment.
This entry may also include message text specific to the SQL
Statement. |
|
|
An entry in this field indicates
that the result of the SQL Statement is saved to a predefined file.
The file can be changed by selecting an alternative file using the
Windows browsing facility. If not explicitly specified then SLIK saves the
report to the Configuration Output directory as sql-$task_id.[format]. |