DBSelect

DBSelect is a built-in scripting function that selects a result set from an external database (equivalent to a SQL SELECT statement), maps the columns to array tags in your project, and copies the values from the result set to the array tags.

Function Group Execution Windows Embedded Thin Client
DBSelect Database/ERP Synchronous Supported Supported Supported

Syntax

DBSelect(strDBConn,strTable,strTags,strColumns,strCondition,strOrder { | ,optNumMaxRows { | ,optStrErrorTag } } )
strDBConn
The name of the database connection. Connections are configured in the Database/ERP folder in the Project Explorer.
strTable
The name of the database table from which you want to select.
strTags
A comma-delimited list of the names of array tags in your project, to which the columns of the database table 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.
strColumns
A comma-delimited list of which columns in the database table to select. The list order should correspond to the list in strTags.

To select all of the columns in the table, in their original order, give this parameter an empty string ("").

strCondition
A statement specifying which rows in the database table to select. This is equivalent to the SQL WHERE clause and must follow the same syntax.

To select all of the rows in the table, give this parameter an empty string ("").

strOrder
A statement specifying the order in which the rows should be sorted. This is equivalent to the SQL ORDER BY clause and must follow the same syntax.

To leave the rows in their original order, give this parameter an empty string ("").

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

This is an optional parameter; if no value is specified, then only the first row of the result set will be copied.

optStrErrorTag
The name of a String tag that will receive detailed error messages, if errors occur during runtime.
Note: The tag name must be enclosed in quotes, as shown in the syntax diagram, or else the project will try to get the value of the named tag.

This is an optional parameter.

Returned value

This function returns the total number of rows in the result set. If an error occurs, then it returns a negative number.

Please note this is the value returned by the function itself; the database values are copied to the array tags specified by strTags.

Notes

This feature emulates SQL (Structured Query Language) database operations. You should be familiar with how SQL statements are formed and executed before you use this feature.

Examples

DBSelect( "DB1", "Table1", "Array1,Array2", "Column1,Column2", "", "" )
DBSelect( "DB1", "Table1", "Array1,Array2", "Column1,Column2", "Column2 < Column1", "Column1", 4, "TagError" )