DBExecute

DBExecute executes a custom SQL statement on an external database. If the statement is a query (for example, SELECT), the database values are copied to specified array tags.

Syntax

DBExecute(strDBConn, strSQL, opStrTags, optNumMaxRows, 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 comma-separated list of the names of array tags in your project, to which the columns of a SQL SELECT result set will be mapped. The database values will be copied to these array tags, with the first row of the result set being copied to array index 0. Make sure the arrays are large enough to receive all of the rows in the result set.

This parameter is required only when strSQL contains a SQL SELECT statement. For all other types of statements, this parameter is ignored and can be omitted. However, if you need to maintain the syntax of the function in order to continue through to optStrErrorTag, give this parameter an empty string ("").

optNumMaxRows

The maximum number of rows to be copied from a SQL SELECT result set. In most cases, to copy all of the rows, specify a number greater than the expected number of rows in the result set.

This parameter is required only when strSQL contains a SQL SELECT statement. For all other types of statements, this parameter is ignored and can be omitted. However, if you need to maintain the syntax of the function in order to continue through to optStrErrorTag, give this parameter a value of 0.

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 the total number of rows that were affected by the SQL statement.

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

This is the value returned by the function itself; in the case of a SQL SELECT statement, the database values are copied to the array tags specified by optStrTags.

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.

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, 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.

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.

Examples

DBExecute("DB1","INSERT INTO Table1(Column1,Column2) values(1,1)")

DBExecute("DB1","SELECT max(Column1),max(Column2) FROM Table1","MyArray1,MyArray2",1,"TagError")