Database Read Adaptor

Creating a Database Read Adaptor

The connection should be selected from the Adaptor Connection dropdown at the top of the screen. The connection can be edited directly via the pencil icon.

The Database Read Adaptor allows SQL Select statements to be executed. The records returned by the Select statement will be put into the output destination schema or public variable location.

The Read Query should be specified as a standard SQL expression. Xponent uses the Python SQL Alchemy library to convert the SQL to the specific dialect of the database type. So there is no need to use database-specific encoding such as backticks (`identifier`) for MySQL or square brackets ([identifier]) for Microsoft SQL.

Final semi-colons are optional. The query can be split over several lines for readability purposes. The query can be parameterized using %%paramName%% anywhere within the query.

For Example: A query to select a customer record could look as follows: 

Anything used in a WHERE clause for a SELECT statement should use single or double quotes if the parameter is a string. If the parameter is numeric, as shown in the above query, quotation marks are not required.

Whenever the query is changed, and a parameter is added or removed, it is necessary to save the query using the Save Edits button. This will identify all of the parameters and create a slot for this in the Parameters list in the bottom left panel.

Once all of the parameter locations have been specified, the adaptor will become valid.

If this adaptor executes successfully, then it should populate the schema as follows:

Validation Warnings

: All the invalid notifications will be displayed after expanding the Invalid Dropdown.

Related Pages:

• Database Adaptor
• Database Write Adaptor
• Database Delete Adaptor
• Database Listener