TeleFlow Step 0810

From TeleFlow

SQL Statement Step
Executes a SQL statement or stored procedure.

Note: Please refer to the SQL Users Guide for more information about SQL programming in TeleFlow.

Properties

SQL handle (1..9): Every SQL statement must be assigned a number, or handle, which other database steps use to refer to or work with the statement or its results (E.g. To fetch a results row from a SQL Statement, you would use a SQL Fetch step, and supply the handle from the step that generated the results set.). This number must range from 1 to 9.

SQL Statement: The definition of the SQL statement. TeleFlow runs SQL statements through an ODBC driver, so the driver determines what you can and cannot do (I.e. The driver determines what SQL statement syntax and database functions work). The following example shows what a select statement might look like. TeleFlow variables are populated in the 'select' section, and used to locate information in the where clause. The following select statements was created for dBase tables.

Select
 accnt1 + accnt2 + accnt3 ACCTOT,
 shipto SHIPTO,
 shipdate SHIPDATE
FROM
 \DATA\Account.dbf
Where
 acctno = @ACCTNO

Time warning message threshold (ms): Whenever TeleFlow runs a SQL Statement step, it notes how long it takes - in milliseconds - for ODBC to execute it and return a result. A warning message is displayed in the log output if this threshold is crossed. The elapsed time, in seconds, is output to the TeleFlow log, following the output from the SQL Statement step itself, like so:
Elapsed Time: 0.015

If the time is greater than the threshold amount supplied, a warning message will follow the elapsed time, as shown:

Elapsed Time: 3.220 - TIME WARNING

As this warning message is consistent, it can be searched for in TeleFlow log files to easily locate SQL statements that are in need of tuning.

If no value is supplied, an internal default of 2000 (2 seconds) is used. Note that the value in this field does not change anything about the execution of the SQL statement.

Action Steps

Failure: The primary reason to take advantage of the Fail path on a SQL Statement step is to handle issues with database connectivity. Sometimes, for one reason or another, a database you connected to with the DB Connect step is unavailable when you attempt to run a SQL Statement on it. It is sometimes possible - depending on how long the database remains inaccessible - to recover from such a problem if you put a short pause - using the Wait step - after a failure, then try the SQL Statement again. Because the failure could also be incorrect syntax, and because a database might remain unavailable, it is a good idea to retry only a few times before giving up and generating a fatal error (using the Fatal Stop step). Many of the sample applications that come with TeleFlow Designer use flowchart's named "DBSet" and "DBRetry", which have examples of how to go about building such retries into a TeleFlow Application.

Related Steps

DB Connect
DB Disconnect
SQL End
SQL Fetch