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 | Mobile Access |
---|---|---|---|---|---|---|
DBSelect | Database/ERP | Synchronous | Supported | Supported | Supported | Not supported |
Syntax
- 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 should be enclosed in quotes, as shown in the syntax diagram, or else the function will try to use the value of the 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" )