By Query Function

$Query function allows you to generate data based on the database query. There are two versions of the function. The first uses the default connection. The second allows you to specify custom connection (predefined ODBC data source name). Both have two parameters. They are:

  1. SQL statement or function (like $Pattern) call
  2. (optional) Query Mode, the default is 0.
    '1' means use data from result set sequentially i.e. the first row will be used after the last if necessary.
    '2' means rerun the query for each output row1. It is suitable for Oracle sequence requests, UID requests, etc.

Also, the second version has three additional parameters:

  1. Predefined Data Source Name2. String. Use ODBC Administrator to prepare or manage it.
  2. (optional) Login3. String, default is empty.
  3. (optional) Password. String, default is empty.

1 - this mode uses the first value of the resultset only.
2 - use JDBC driver name for Java edition of the engine.
3 - use connect string for Java edition of the engine.

Function returns NULL value for the empty recordset.

Examples

  1. $Query(select [Name] from Customers,0)
  2. $Query(select ProfileName from Profile,1,supv3) uses 'supv3' data source name with empty user name and password.
  3. $Query($Vars(select [Name] from Customers where ID=@1)) - refers to column #1 in the WHERE clause.

Notes

  • Additional quotation by '(' and ')' is required for query that contains ','.
  • Identifiers must be quoted if necessary.