Function.ODBC.Connection!conname.ExecuteAndReturn

From GAB Help
Jump to: navigation, search

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. Optionally transformations may be specified on the return, if the return holds multiple fields and/or fields.

Syntax

Function.ODBC.Connection.ExecuteAndReturn(SQL query [string], return)

Function.ODBC.Connection.ExecuteAndReturn(SQL query [string], transform, return)

Arguments

SQL query

string

Transform

Numeric expression that is the sum of values specifying transformation to be applied to the returned data.

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

Single value return: return variable must be the appropriate type (string, float, date, long, etc.)

Multiple field/row return: return variable must be string. In the returned string,

*!* delimits fields
#$# delimits rows

Examples

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)


Remarks

Specifying ALL or * in the select query (for returned fields) is not recommended, since addressing the data in the resulting string needs to be done by array index position.