SQL Query
Using SQL Query Databoxes in your scripts
Introduction
SQL Query Databoxes are powerful and can return a variety of information from a host system's database(s) which could prove useful in the course of a diagnostics script either by controlling the flow, minimising the number of questions asked or for displaying relevant information that assist the call handler or end user alike. For example, they are often used:
in Startup scripts to decide which set of scripts to use, e.g. General, Leasehold, Communal
to provide additional tenant/caller profile data, e.g. date of birth, vulnerability indicators, warnings. no. of bedrooms
to provide additional asset related information, e.g. heating type, in defects, void property, repair responsibilities
historical information e.g. previous repairs or enquiries
present rent account information in Messages
SQL Queries can be issued against any configured/compliant database in order to return a single row of data which internally is held as an XML packet and can be referenced by Expressions, e.g. 'Item' (see example below).
Select an existing SQL Databox or click Add to create afresh.
Properties
Group - when displayed in the Selection List, Databoxes are arranged into groups. Select a group from the drop down list to add the Databox to an existing group or type in a new group name to create a new group.
Database - the database drop-down list specifies which database connection the SQL Query Databox will access. By default the Keyfax Database is available. If you are wishing to query other SQL databases these will need to be added by Support, to do this read-only login credentials will be needed, along with database name and location. The necessary permissions will also need to be established.
Name – a descriptive name for the Databox. If you can, make this a meaningful description to enable easy identification in your scripts..
Empty bookmarks allowed – check this box if the Databox can be used in a Task or Message without being required to hold a value.
Description - a short description of the Databox. This is displayed in the Databox Selection list in and is searchable using the filter.
SQL Query – This is where the SQL query is added. The Query normally includes a reference to another Databox, for example, a Databox obtaining the AssetID of a property. The maximum length of the query is around 1900 characters; this varies depending on the selected database name and the possible references to other Databoxes. Longer SQL queries should be set up as Stored Procedures in the database with EXECUTE permissions granted to the role: OMFAXROLE. The Databox SQL can then simply execute the stored procedure with the appropriate parameters.
Test – test the SQL and Expressions setup against the databox.
Expressions – these are covered in-depth in the Databox Expressions section. Typically, the Expression Item will be used to access individual columns returned by the query. In the example above you'll see four Item Expressions which point at Name, Email, Telephone and TelephoneExt which match up exactly with the column names returned.
All Databoxes are re-evaluated every time they are referenced either directly or through an Expression; for SQL Databoxes this will re-run the SQL query and could be inefficient. If it is known the query will always return the same result and the query returns multiple values or is referenced several times in the script then it is better to use a Databox-Write to store the original result into a separate Script Databox and use the latter for all subsequent references.
In the above example...
The example retrieves four items, e.g. the user's Name, Email, Telephone and Extension No. The key piece of data (used in the WHERE clause) is '{Import.UserCode}' that has been supplied by the host system.
As you can see in the Expression list, each element of data can be referenced separately.
To ensure the SQL query and its Expressions are working, click Test.
Handling multiple rows of data
If an SQL Query returns more than a single row, this is considered an error and the SQL query should be amended to ensure this cannot happen. It is good practice to always test for this eventuality in which case you will see this message: 'Unable to process multiple rows returned'.
Depending on the context, there may be various techniques to restrict results to a single row; typically this may use the 'TOP n' clause. For example, to return a single row containing the current balance for the most recent tenancy record: SELECT TOP 1 CurrentBalance FROM Tenants WHERE TenantID = {Import.CallerId} ORDER BY TenancyStartDate DESC Having said that, there are exceptions where multiple rows can be returned but the results must be coerced into an XML packet; this will require more specialist knowledge - see the section Handling multiple rows from SQL.
It should also be taken into consideration that if an SQL Query doesn't return anything then any Expressions against it will not run.
Last updated