Edge HMI

DBCursorOpenSQL

Save PDF

DBCursorOpenSQL

Save PDF

Last UpdatedAug 07, 2026
4 minute read

DBCursorOpenSQL selects a set of rows and columns in a database table, initializes the cursor at the first row of the result set, copies that row's values to mapped tags, and then returns a cursor handle that can be referenced by other Database/ERP functions. (This function is equivalent to a SQL SELECT statement.)

Function	Group	Execution	String Exp.	Windows	Windows Embedded	IoT View	Thin Clients	Mobile Access
DBCursorOpenSQL	Database/ERP	Synchronous	No	Supported	Supported	Supported (see Notes below)	Supported	Supported

Syntax

DBCursorOpenSQL(strDBConn, strSQL, optStrTags, "optStrErrorTag")

strDBConn

The name of the database connection. Connections are configured in the Database/ERP folder, in the Project Explorer. For more information, see Database/ERP worksheet.

strSQL

A complete, syntactically correct SQL SELECT statement. Unicode characters, including symbols and accented letters, are not supported in this statement.

Note: Braces ({}) can be used as escape characters in many programming languages, to enclose some part of a text string that should be handled differently during execution. In SQL, a statement might include literal text that should not be parsed or executed as part of the statement. The following example shows a valid SQL statement, with braces used to enclose the literal text:

SELECT * INTO inmates FROM OPENROWSET ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\;Extensions=CSV;','SELECT * FROM flat.csv')

In Studio, however, braces can be used to enclose project tags and expressions that should be evaluated in text strings that are not normally evaluated (such as the caption of a Button object). As such, if you pass a SQL statement that includes braces to this function, the contents of the braces will be evaluated as a tag/expression rather than as part of the SQL statement, and the function will fail.

To pass the SQL statement so that it will be handled correctly by this function, create a new project tag that contains the literal text and then reference that tag in the SQL statement. For example:

$AuxTag = "{Microsoft Text Driver (*.txt; *.csv)}" $DBCursorOpenSQL("inmates","SELECT * INTO inmates FROM OPENROWSET ('MSDASQL','Driver={AuxTag};DEFAULTDIR=C:\;Extensions=CSV;','SELECT * FROM flat.csv')")

This note applies only to the DBCursorOpenSQL and DBExecute functions. Braces cannot be used like this in any other function calls in Studio.

optStrTags

A string that lists the project tags to which the columns will be mapped. This list of tag names should be comma-separated and in the same order as the columns specified by the WHERE clause of strSQL. As the cursor is moved through the result set, the values in the current row are copied to these tags.

This parameter is optional; if no tags are specified, no values will be copied.

optStrErrorTag

The name of a project tag that will receive detailed error messages, if errors occur during project run time.

Note: If this parameter is enclosed in quotes, the string literal will be used. Otherwise, the value of the tag/expression will be used.

This parameter is optional.

Return value

In the case of success, this function returns a numeric value that can be used as a cursor handle in other Database/ERP functions.

In the case of error, this function returns one of the following possible values:

Value	Error Message
-1	DBERROR_DATABASE_ERROR
-2	DBERROR_CONNECTION_OPEN_ERROR
-3	DBERROR_CURSOREMPTY_FAILURE
-4	DBERROR_CURSORMOVE_FAILURE
-5	DBERROR_CURSORFETCH_FAILURE
-6	DBERROR_CURSORNOTOPEN_FAILURE
-7	DBERROR_CURSOR_EOF
-8	DBERROR_CURSOR_BOF
-9	DBERROR_INVALID_COMMAND
-10	DBERROR_INVALID_CURSOR
-100	DBERROR_NOT_ENOUGH_PARAMETERS
-101	DBERROR_INVALID_PARAMETER_DB
-102	DBERROR_INVALID_PARAMETER_TABLE
-103	DBERROR_INVALID_PARAMETER_COLLIST
-104	DBERROR_INVALID_PARAMETER_CONDITION
-105	DBERROR_INVALID_PARAMETER_ORDER
-106	DBERROR_INVALID_PARAMETER_SQL
-107	DBERROR_INVALID_PARAMETER_CURSOR
-108	DBERROR_INVALID_PARAMETER_VALUELIST
-109	DBERROR_INVALID_PARAMETER_TAGLIST
-110	DBERROR_INVALID_PARAMETER_ERRORTAG
-111	DBERROR_INVALID_PARAMETER_MAXROWS
-112	DBERROR_INVALID_PARAMETER_SQL_QUERY
-113	DBERROR_INVALID_PARAMETER_ROW
-114	DBERROR_INVALID_PARAMETER_COLUMNNUMBER
-115	DBERROR_INVALID_PARAMETER_COLUMNINFO
-199	DBERROR_TOO_MANY_PARAMETERS
-200	DBERROR_TCP_COMM_FAILURE
-201	DBERROR_EXCEEDED_OPEN_BUFFER
-202	DBERROR_EXECUTING_ONLY_CLEANUP_COMMANDS

Notes

Database/ERP functions emulate Structured Query Language (SQL) database operations. Before you use these functions, familiarize yourself with how SQL statements are formed and executed.

This function recognizes only 16000 rows of a table at one time. If a selected section of table has more than 16000 rows, only the first 16000 rows of the selection will be accessible to any built-in language Database/ERP functions. Exceeding this limit may return an EOF error even if the end of the file has not been reached.

By default, the database interface can have a maximum of 1000 database connections — including cursor handles — open at the same time. If this limit is reached, the database interface will automatically close the oldest connection before it opens a new one. As such, you should use the DBCursorClose function to close open cursor handles as soon as you are done with them.

You can also increase the maximum number of database connections, if necessary. To do that, use a text editor to open your project file (typically located at AVEVA\AVEVA Edge 2026\Projects\<project name>\<project name>.app) and edit the following property:

[StDB]

MaxConnections= <from 1 to 32767>

Having a large number of database connections open at the same time can affect runtime performance.

When this function is used in a project running on IoT View, if the WHERE clause of strSQL filters something that contains accented letters (such as "é"), the result set will be empty. This is true even when the WHERE clause itself does not contain accented letters, which are not supported by this function. The mere presence of accented letters in the results will cause the error.

The DBCursorOpenSQL and DBExecute functions can be used to execute a Stored Procedure on an SQL database. However, these functions can only handle the result set; they cannot pass parameters to or receive parameters from the Stored Procedure. In order access such parameters, use VBScript or Python.

When this function is used in a project running on IoT View, it supports only single-byte Unicode characters (up to U+007F). Multi-byte Unicode characters are not supported at this time.

You can use the Database Gateway (StADOSvr) to directly monitor database connections for leaks and errors. For more information, see Database Interface.

Examples

Open Table1 of DB1 and select all rows where Column1 has a value greater than 3; map Column1 to Tag1 and Column2 to Tag2; order the rows first by Column1, then by Column2, in descending order; and write error messages to TagError:

DBCursorOpenSQL("DB1","SELECT Column1, Column2 FROM Table1 WHERE Column1 > 3 ORDER BY Column1, Column2 DESC","Tag1, Tag2","TagError")

Edge HMI