Syntax

strDBConn

The name of the database connection. Connections are configured in the Database/ERP folder in the Project Explorer.

strSQL

A complete, syntactically correct SQL statement.

Note: In SQL, curly brackets ({}) are typically used to enclose an expression that must be evaluated before the entire SQL statement is executed. For example:

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

In IWS, however, curly brackets are used to reference project tags in text fields that are not normally evaluated (e.g., in the caption of a Button object). If you pass a SQL statement that includes such an expression to DBExecute, then the project will try to evaluate the expression as a tag reference and the function will fail.

To pass the SQL statement so that the project can correctly evaluate the expression, create a new string tag that contains the text of the expression and then reference that tag in the SQL statement. For example:

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

optStrTags

A comma-delimited 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, then 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, then give this parameter a value of 0.

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 affected by the SQL statement. If an error occurs, then it returns a negative number.

Please note 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

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

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

  DBExecute( "DB1", "SELECT max(Column1),max(Column2) FROM Table1", "Tag1,Tag2", 1, "TagError" )