QueryAsync function

QueryAsync() queries a SQL database for a specific set of data.

This topic provides information on configuring QueryAsync calls using either a private string standard (secured and recommended method) or a string value (unsecured method). By default, SQL queries can be executed only with the secured SQL query method (that is, using a private string standard). The unsecured SQL query method (that is, using a string value) is blocked. To provide users with the necessary time to migrate SQL database access to the secured method, you can temporarily enable unsecured access to SQL databases in DeltaV Live Administration, in the SQL Data Sources Management application. However, unsecured access will be removed in a future release of DeltaV.

Important:
To follow best security practices, Emerson recommends that SQL queries be defined using a private string standard. Private string standards are configured in Graphics Studio. (For detailed information on configuring a private string standard, see the topic, Create a standard, in the Related information section or in Graphics Studio application help.)
Arguments
2 or more, Optional arguments are indicated by the at symbol (@) following the number of the parameter, beginning with 1.
Object Argument Description
SQL Data Source The name of the SQL data source configured in DeltaV Live Administration, in the SQL Data Sources Management application.
Using the secured SQL query method (recommended)
Function argument Description
private string standard reference The reference to the SQL query that is defined in a private string standard in Graphics Studio.
@1...@N N number of parameters that specifies the criteria for which to query.
Using the unsecured SQL query method
Function argument Description
string value A combination of one or more values, operators, and SQL functions that evaluate to a value. The string value is written in query language. You can also use them to query the database for a specific set of data.
@1...@N N number of parameters that specifies the criteria for which to query.
Return values (string)

A Promise that awaits and then returns an array of rows, each row with an array of columns, all that meet the criteria.

Syntax
Syntax for the secured SQL query method (recommended):

SQL.<SQL Data Source>.QueryAsync(<private string standard reference>, <@1...@N>):string[][]

Syntax for the unsecured SQL query method:

SQL.<SQL Data Source>.QueryAsync(<string value>, <@1...@N>):string[][]

Examples
Example 1
Secured SQL query method (recommended) Unsecured SQL query method
Assume you have the following configurations:
  • a SQL data source named QueryTest, which contains a table named ExampleTable
  • a private string standard named SQLPrivateString1 under Graphics Studio Library1.
  • The value of SQLPrivateString1, as defined in Graphics Studio, is:

    SELECT [Name], [Date], [Cost], [Comments] FROM [dbo].[ExampleTable] WHERE [Name] = 'Duncan'

You could then perform the following query: 
const ret = await SQL.QueryTest.QueryAsync(GL.Library1.SQLPrivateString1);

Assume you have an SQL database named QueryTest, which contains a table named ExampleTable.

You could then perform the following query:
const ret = await SQL.QueryTest.QueryAsync("SELECT [Name], [Date], 
[Cost], [Comments] FROM [dbo].[ExampleTable] WHERE [Name] = 'Duncan'");
Example 2
Secured SQL query method (recommended) Unsecured SQL query method
Assume you have the following configurations:
  • A private string standard named SQLPrivateString2 under Graphics Studio Library1.

  • The value of SQLPrivateString2 is defined in Graphics Studio as:

    SELECT Top(2) Date_Time, Event_Type, Area, Module FROM Journal

  • A SQL data source named SQLDatasource1, created in DeltaV Live Administration, in the SQL Data Sources Management application.
You could then perform the following query: 
{
Dsp.Text1.Label = await SQL.SQLDatasource1.QueryAsync(GL.Library1.SQLPrivateString2);     
}

Assume you have a SQL data source named SQLDatasource1, created in DeltaV Live Administration, in the SQL Data Sources Management application.

You could then perform the following query: 
{
Dsp.Text1.Label = await SQL.SQLDatasource1.QueryAsync("SELECT Top(3) Date_Time, Event_Type, Area, Module FROM Journal"); 
}
Example 3: Parameterized SQL queries
Secured SQL query method (recommended) Unsecured SQL query method
Assume you have the following configurations:
  • A SQL data source named QueryTest, which contains a table named ExampleTable.
  • A private string standard named SQLPrivateString1 under Graphics Studio Library1.
  • The value of SQLPrivateString1, as defined in Graphics Studio, is:

    SELECT [Name], [Date], [Cost], [Comments] FROM [dbo].[ExampleTable] WHERE [Name] = @1

You could then perform the following query: 
const ret = await SQL.QueryTest.QueryAsync(GL.Library.SQLPrivateString1, 'Duncan');

Assume you have an SQL database named QueryTest, which contains a table named ExampleTable.

You could then perform the following query:
const ret = await SQL.QueryTest.QueryAsync("SELECT [Name], [Date], 
[Cost], [Comments] FROM [dbo].[ExampleTable] WHERE [Name] = @1", 'Duncan');
Exceptions

It is considered best practice to configure a try-catch statement for possible exceptions that can be thrown, in order to display the appropriate exception message to users. Therefore, Emerson recommends configuring a try-catch block around all calls to QueryAsync. (For more information on handling exceptions, see the topic, Scripting tips for DeltaV Live, in the Related information section.)

The following table lists some of the possible exceptions that can be thrown and the strings each exception returns:

Possible exception String returned
This DeltaV system either does not have the Premium-tier DeltaV Live engineering license, or that license is not assigned or downloaded. "This system is not licensed for Premium-level DeltaV Live engineering features: SQL Data Sources."
The SQL Data Source name cannot be resolved. "SQL Data Provider does not exist for connection named: <SQL Data Source>."
Note:
<SQL Data Source> is the SQL data source defined in DeltaV Live Administration, in the SQL Data Sources Management application.
Cannot connect to the SQL database. "Could not open connection to <database name>"
Note:
<database name> is the SQL database defined in the Database Name property of the SQL Data Source, configured in DeltaV Live Administration, in the SQL Data Sources Management application.

Unsecured SQL queries are blocked and cannot execute a string query value.

OR

Unsecured SQL queries are blocked, and the name of the private string standard value cannot be found or does not exist.

 "Unable to resolve SQL query for standard value named: <SQL query name/value>."
An error has occurred during query execution. "SQL query execution failed."
Tip:
Verify that the following items are correct:
  • the SQL query
  • the reference to the PrivateString standard
No DeltaV user is currently logged in. "No user is logged in. Query execution is disabled."
Other exception strings passed through from the SQL data source may also be returned (such as, the server is not currently communicating).