Scripting Guide - Server.Query (Server and ServerObject Functions and Properties)

Server.Query

The Query property is a server property that - when called from a Mimic - returns a ServerQuery (similar to a ServerObject in that it is only used internally to represent data, only a ServerQuery is used specifically to represent the resultset of an SQL Query). The ServerQuery has its own properties which include ColumnCount, ColumnNames, RowCount, Rows, Error, and ErrorMessage (see ServerQuery Properties).

Syntax	Server.Query
Description	Performs a query on the database. The Server.Query function returns a ServerQuery - a ClearSCADA specific script object that has its own properties which can be manipulated.
Arguments	SQL {string} The SQL for the query. The SQL Query can make use of any of the SQL commands that are available in ClearSCADA, including SELECT, UPDATE, INSERT, and DELETE. The permissions of your User account may restrict the query—the SQL query can only access those database items to which your User account has the Read permission enabled.
Returns	ServerQuery The result of the SQL Query is returned as a ServerQuery. The ServerQuery has its own properties which you can use to access the data in the query (see ServerQuery Properties).

For more information on SQL queries, see SQL Query Structure of the Guide to Lists.

Example:

In this example, a script is used to perform an SQL Query on a Data Grid database item. The script uses the Server.Query property and also makes use of the properties of the ServerQuery that is returned by the Server.Query property.

The example script has been created specifically to show the syntax for the Server.Query property and the properties of the resultant ServerQuery script object. To keep the example simple, we have used message boxes to display the values retrieved by the query. On a live system, you would be more likely to want to write the values to another part of the database or use them in calculations.

Option Explicit

Public Sub ServerQueryTest

Dim DGRS

Dim DGRows

Dim DGColNames

Dim r, c

Dim string

Set DGRS = Server.Query("SELECT * FROM DGTest")

If Not(DGRS.Error) Then

DGRows = DGRS.Rows

DGColNames = DGRS.ColumnNames

For c = 0 To (DGRS.ColumnCount - 1)

MsgBox DGColNames(c)

For r = 0 To (DGRS.RowCount - 1)

string = ""

for c = 0 to (DGRS.ColumnCount - 1)

string = string & DGRows(r,c) & " , "

Msgbox Cstr(r) & string

Else

Msgbox DGRS.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"

End If

Set DGRS = Server.Query("INSERT INTO DGTest(Str, Lng, Bool) VALUES ('Test3', 45, False)")

If DGRS.Error Then

Msgbox DGRS.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"

End If

End Sub

The script works like this:

The DGRS variable is set to represent the result of the SQL Query that is performed on the 'DGTest' database item. The SQL Query requests the data from DGTest (SELECT* FROM DGTest).

If the SQL Query is successful, the Server.Query property returns a ServerQuery. The DGRows variable is then set to be the same value as the value of the ServerQuery’s Rows property.

The 'c' variable is set to represent the columns of the Query result set. The range of columns to be accessed by the script is defined as column 0 to the ColumnCount value of the ServerQuery -1. The -1 is used with the ColumnCount property of the ServerQuery so that the range has 3 values, with the count starting at 0. The columns that are included in the range then have their column names displayed in message boxes (each column name is displayed in a separate message box). The column names are accessed by using the ColumnNames property of the ServerQuery.

The 'r' variable is set to represent the rows of the Query result set. The range of rows to be accessed by the script is defined as row 0 to the RowCount value of the ServerQuery -1. The -1 is used with the RowCount property of the ServerQuery so that the range has the correct number values, with the count starting at 0. For each row in the range, the script initializes the value to be empty, then inserts the value that is stored for the row (in the Rows property of the ServerQuery). The values of each row are displayed in a message box (1 message box per row of values) and the values show in the message box are separated by a comma.

If the query specified for the Server.Query property cannot be completed successfully, a message box is displayed showing an error message. The title of the error message box is 'Error in SQL Command' and the error message that is displayed is the value stored by the ErrorMessage property of the ServerQuery. The message box is set to have a critical warning symbol and a single OK button.

When the script has displayed the relevant message box(es), it will attempt to insert a new row of values into the 'DGTest' database item. The three values it will insert are of the types Str, Lng, and Bool respectively and are 'Test 3', '45', and 'False'. If the script is unable to create this row of values for the 'DGTest' item, it will display a message box. The title of the error message box is 'Error in SQL Command' and the error message that is displayed is the value stored by the ErrorMessage property of the ServerQuery. The message box is set to have a critical warning symbol and a single OK button.