DBSelect

DBSelect selects a result set from an external database (equivalent to a SQL SELECT statement), maps the columns of the result set to array tags in your project, and then copies the values from the result set to the array tags.

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. For more information, see Database/ERP worksheet.

strTable

The name of the table or view (see Notes for more information) in the database.

strTags

A comma-separated list of the names of array tags in your project, to which the columns of the database table or view 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-separated list of which columns you want to select in the database table or view. The order of this list should correlate with the order of the list that you specified for strTags.

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

For more information about selecting columns in a view, see Notes below.

strCondition

A statement specifying which rows in the database table or view 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 or view, specify an empty string ("") for this parameter.

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, specify an empty string ("") for this parameter.

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 parameter is optional; if no value is specified, only the first row of the result set 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 the total number of rows in the SQL result set.

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

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

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.

For this function, you can select from either an entire database table or a specific database view. A view is a virtual table that is created by a stored query; users can query the view just as they would query a table. If you select from a view, however, you must select all of the columns in the view. In other words, when you specify the list of columns for strColumns, you must include all of the columns in the view, albeit in the order you choose. It is assumed that the view was created to include only the columns you need for this function.

As an alternative to this function, you can use the DBCursorOpenSQL function which allows you to compose your own SQL SELECT statement without the limitations of this function.

Examples

DBSelect("DB1","Table1","Array1,Array2","Column1,Column2","","")

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