Note: First see Working with File Sets for a discussion on how data flows through step to step.
The SQLQuery step executes an SQL statement and optionally sets macro values that may be used in subsequent steps.
Friendly Name |
INI Variable |
Required? |
Description |
SQL Query |
query |
required |
The text of the SQL statement to be executed |
Populate Macros? |
is_select |
optional |
"true" or "false", without quotes. The default is "true". If "true" this step fails when the execution of the SQL statement does not return a row |
Data Source |
dsn |
optional |
The name of the odbc DSN or database file name, depending on the value of the type option. |
Data Provider |
type |
optional |
Possible database types are: "sqlce", "odbc" and "sqlite", without quotes. The default is the same as in the framework |
Username |
username |
optional |
Username to pass to odbc DSN. default is empty |
Password |
password |
optional |
Password to pass to odbc DSN. default is empty |
This step's behavior is independent of the job's current file set.
Setting Macro Values
When the value of is_select is true the results of the query are loaded into the %stepname[] macros produced by this step.
Suppose the step was named "sqlquery" and the following query statement was executed:
select col1, col2 from table1
The first row returned by the database would be used to populate macros as follows:
%sqlquery[col1] = The value of col1 on the first returned row.
%sqlquery[col2] = The value of col2 on the first returned row.
These values are not set if the step fails because no rows are returned.
Example Usage:
