Sqltable

Submits an SQL (Structured Query Language) statement to a database server and returns a multi-row result. This action returns a time/date based on the location of the back-end server associated with your account (rather than UTC time zone). You can convert this time/date to another time zone using a Snippet action.

 

Supported Script Types

Generic Email Chat Phone Voicemail Work Item SMS

Properties

Property Details
SQLQuery

The SQL statement that will be submitted to the database server. This statement should return records.

RowSeparator

The delimiter character used to separate individual records.

FieldCountVar

The name of the variable that will return the number of fields returned by the SQL Query.

RowCountVar The name of the variable that will return the number of rows returned by the SQL Query. The Row Count Var that is created is very helpful with a Loop action to determine the number of times you want to repeat a sequence.
GlobalVariables Not used.
Publish Publishes a variable and value, with everything in the database associated with a contact. If you want to publish specific data, use the Assign action.
MaxRows

The maximum number of rows to retrieve. The default is 100.

TimeoutSec

The number of seconds until the query times out. The default is 10. The maximum number of seconds supported is 30 (though larger numbers can actually be entered in the field).

Branches

Branch Details

Default

Path taken unless the script meets a condition that requires it to take one of the other branches. It is also taken if the other branches are not defined.
NoRecords Path taken if there are no values for the specified properties. This happens when data is not returned from the database.
OnError Path taken when an action fails to execute properly. This happens when the SQL Query is invalid.
OnTimeout Path taken if there is no response for the number of seconds specified.

Tips & Tricks

  • This action will return all rows as an array.
  • Use Sqltable for the same purpose as Sqlselect when multiple rows are expected. Like the Sqlselect action, column values are stored in variables with the same names as the column names. In this action, however, more than one record may be stored in each column variable. Each record is separated by the character defined in the RowSeparator property. If the default pipe symbol (|) is used, each column can be referenced as an array.
  • SQL databases do not guarantee any kind of ordering when you do not explicitly sort the data.
  • Be careful not to select a large amount of data. This will ultimately cause the script to fail. If sequential access to a large table is required, see the Sqlcursor action.