In order to create a Database Read Adaptor you should define a database connection first. The connection should be selected from the Adaptor Connection drop down 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.

Creating a Database Read Adaptor

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 used database specific encoding such as backticks (`identifier`) for MySQL or square brackets ([identifier]) for Microsoft SQL. It is good practice to develop your query and ensure that it returns the correct data in your usual database development tool before copying to Xponent and parameterizing.

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. So 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 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

	Warning	Note
	Parameters each need a data source	Every parameter in the query of the form %%param%% must be mapped to an input data source

Browser not supported