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)