Skip to main content

SQL queries

A SQL query (SqlQuery) defines a query that returns rows for use in your rule application. SQL queries are created and edited in the Data section. You can also add a SQL query under a database endpoint.

Create a SQL query​

  1. In the Data section, open the ellipsis menu (...).

    screenshot of the data ellipsis menu

  2. Select Add SQL Query.

    screenshot of data menu options

  3. Enter a name for the query.

  4. See Inline tables if the table the query runs against doesn't exist yet.

  5. Enter the SQL query.

  6. Add any parameters the query needs. For inline tables, only the Input direction is valid.

    image of a completed sql query template

Editor fields​

image of a completed sql query template showing numbers on the editor fields

1. Name Enter the name of the query.

2. Cache duration Set how long results are cached.

3. Command type

  • Select Rows: returns rows.
  • Select Value: returns a single value.
  • Execute: runs a statement and returns the number of rows changed.

4. Parameters Define the query parameters in the parameters table. Each parameter has the following columns, and rows can be added and deleted:

  • Name
  • Type — Text, Integer, Decimal, Boolean, Date, or Date/Time.
  • Direction — Input, Output, Input/Output, or Return Value.

5. SQL query Enter the query text.

Limitations​

Current limitations:

  • SQL is not yet ready for external databases. Environment connection details for a database endpoint can't yet be set from irAuthor Web.
note

irAuthor Desktop supports a fuller SQL Query feature — external database sources, all parameter directions, and stored procedures. See the irAuthor Data page for that reference.