Function.ODBC.Connection!ConnectionName.ExecuteAndReturn

This command executes the specified query against the specified database connection, and returns the results in the return variable. If the query returns a single value (one field and one row), the return variable should be declared as the appropriate type (string, float, long, date, etc.). If the query returns more than a single field or row, the return variable must be a string. The returned string will be double-delimited, with *!* delimiting the fields, and #$# delimiting the rows.

Syntax

Function.ODBC.Connection!conname.ExecuteAndReturn(SQL query [String], return [String]) or

Function.ODBC.Connection!conname.ExecuteAndReturn(SQL query [String], transform, return [String])

Arguments

SQL Query

[String]

Transform

[Long]

Optionally transformations may be specified on the return, if the return holds multiple fields and/or fields. The transforms may be specified as follows:

1 - Left trim

2 - Right trim

4 - Trim

8 - Lowercase

16 - Uppercase

Multiple transform values can be added together to create a composite transformation, but the trim values should be treated as mutually exclusive, as should be the casing values. A value of 18 will result in return string, with each field right trimmed and uppercase.

Return

[String]

Returned string will be double-delimited, with *!* delimiting the fields, and #$# delimiting the rows.

Example

Function.ODBC.Connection!Conn.ExecuteAndReturn("Select Count(*) From Job_Header",Variable.Local.iRet)

This returns a double-delimited, uppercase string

Function.ODBC.Connection!conP.ExecuteAndReturn("Select JOB, SUFFIX, PART, QTY_ORDER From Job_Header Where CUSTOMER = 'BMF'",8,Variable.Local.sRet)

This returns the job count in a long variable

Function.ODBC.Connection!conP.ExecuteAndReturn("Select Count(*) From Job_Header",Variable.Local.iRet)