DirectSQL

The DirectSQL function runs an SQL query or statement against the specified database.

You can use the DirectSQL function only with SQL database data types.

You can use this function to perform SELECT queries with JOIN clauses and to perform other operations that cannot be carried out using GetByKey, GetByFilter, or GetByLinks. Resulting rows from SELECT queries are returned to the policy as data items. This function also supports SQL statements such as INSERT, UPDATE and DELETE.

Syntax

The DirectSQL function has the following syntax:

[Array =] DirectSQL(DataSource, Query, CountOnly, ForceSelect )

Parameters

The DirectSQL function has the following parameters.

Table 1. DirectSQL function parameters
Parameter	Type	Description
`DataSource`	String	Name of the data source associated with the SQL database.
`Query`	String	SQL operation to run against the database.
`CountOnly`	Boolean	Pass a `false` value for this parameter. Provided only for backwards compatibility.
`ForceSelect`	Boolean	Force the `DirectSQL` function to run the query as a `SELECT` statement even though it does not start with a `SELECT` keyword.

Return value

For SELECT queries, the DirectSQL function returns an array of data items where each data item represents a row returned by the SQL query. Fields in the data items have a one-to-one correspondence with fields in the returned rows.

For other statements such as INSERT, UPDATE or DELETE, the DirectSQL function returns the affected row count or 0 if the SQL statement returned nothing.

Note: If the UPDATE statement is retrieving SQL row data at the same time (using keywords such as the PostgreSQL RETURNING clause), you can force the DirectSQL function to return the row data by setting the ForceSelect parameter to true. The expression must return zero or more rows otherwise the function may time out waiting for data.

Examples

The following example shows how to run an SQL SELECT operation with a JOIN clause against a database.

// Call DirectSQL and pass the name of the data source and the
// SQL SELECT statement as input parameters
					
DataSource = "MYSQL_01";
Query = "SELECT * FROM Customer LEFT JOIN Server ON " + \
    "Customer.Location = Server.Location";
CountOnly = false;
					
MyCustomers = DirectSQL(DataSource, Query, CountOnly);

The following example shows how to run an SQL UPDATE operation against a database.

// Call DirectSQL and pass the name of the data source and the
// SQL statement as input parameters

DataSource = "MYSQL_02";
Query = "UPDATE Customer SET Affected = true WHERE Location = 'New York'";
CountOnly = false;

DirectSQL(DataSource, Query, CountOnly);

The following example shows how to run an SQL DELETE operation against a database.

// Call DirectSQL and pass the name of the data source and the
// SQL statement as input parameters

DataSource = "MYSQL_03";
Query = "DELETE FROM Customer WHERE Location = 'New York'";
CountOnly = false;
DirectSQL(DataSource, Query, CountOnly);
num_rows_deleted = Num;
Log("Number of deleted rows: " + num_rows_deleted);

This query deletes the specified records and returns the number of rows deleted.

Caching on SELECT Statement

The DirectSQL function supports caching when used to run a SELECT statement that returns a result set from a database. Caching is configured separately for each data source.

To enable caching, you must create a DirectSQL properties file. This file should be named servername_directsql.props, where servername is the name of the Impact Server. The file must be located in the $IMPACT_HOME/etc directory. The following table shows the properties in this file.

You must replace the string in each property with the data source number as represented in the data source list. The data source list is a file named servername_datasourcelist and is located in the $IMPACT_HOME/etc directory.

Table 2. DirectSQL Caching Properties
Property	Description
`impact.datasource.n.enablecaching`	Specifies whether caching is enabled for this data source. Value can be true or false.
`impact.datasource.n.cachesize`	Maximum number of rows per query to be cached.
`impact.datasource.n.querycachesize`	Maximum number of queries to be cached.
`impact.datasource.n.cacheinvalidation`	Amount of time in seconds before data items in the cache are considered stale and must be refreshed from the data source.
`impact.datasource.n.querycacheinvalidation`	Amount of time in seconds before queries in the cache are considered stale and must be refreshed from the data source.

Note: The values set for impact.datasource.n.querycacheinvalidation and impact.datasource.n.cacheinvalidation should be equal.