Function.ODBC.Connection.ExecuteANDReturn

This command executes the specified query against the specified database connection and returns the results in the return variable.

Syntax

Function.ODBC.Connection!conname.ExecuteAndReturn(SQL query, return)

or

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

Arguments

SQL query

String

Transform

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

• 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.

Examples

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

• This returns a double-delimited, uppercase string

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

• This returns the job count in a long variable