The Database Query action is used to:
1. Set up the connection with a Database.
2. Select the records from the database by creating a Query – this will create a Select List.
3. Set the fields names in the database to variable names in Opus.
Note:
At least one Database Query action must be set up for each database you want to use in an Opus publication.
1. Add the Database Query action to your trigger, as described in adding actions. The Database Query tab will automatically appear when you add the action.
2. Click on the Select… button to create a new Data Source Name (DSN) – this will open the Select Data Source dialog box. Alternatively, click on the down arrow to the right of the DSN box to list all of the DSN’s that have previously been used in this publication.
Note:
Once you have created a DSN on your machine, you can select it from the Select Data Source dialog box. However, if you have not created a DSN for your database already, you will have to create it before you can proceed with this action – see Connecting to a Database for more information.
3. Use the Query ID box to type a description of the query you are about to build with the SQL Wizard. By default, this field will automatically create a new name for the query, the first time it will be Query 1, the second time Query 2, and so on. We recommend you type your own, more descriptive name in the box before you click the SQL Wizard button.
Note:
This description (i.e. the Query ID) is used by other Database actions (e.g. the Next Record action) to identify which query you want to use.
4. Click on the SQL Wizard… button to build the query – this will open the first page of the SQL Wizard.
Note:
The wizard is described in detail in Creating a Query – The SQL Wizard. The wizard will create the query and return a Select List when this action is triggered.
5. Use the Field / Variable panel at the bottom of the tab to assign an Opus variable name to each of the field names that appear in the Field column. To assign a variable to a field, select the field name in the Field column and click on the Change… button – this will open the Insert Variable or Expression dialog box. Repeat this exercise for each field that appears in the Field column until each field name has a variable name in the Variable column.
Note:
Use a different variable name for each field displayed in the Field column and make sure the variable names are descriptive. For example: if the field name is Postcode, name the variable db_Postcode; if it is Telephone, use db_Telephone. The prefix db_ is an easy way to know which variables in your publication are linked to fields in a database; however, it is not compulsory to use this prefix, only a suggestion.
6. Click on the Apply button to save your changes.
Note:
This action is the most important Database action as it both opens the database and creates a select list of records based on the search criteria you entered in the SQL Wizard. All other Database actions use the name you entered in the Query ID box (point 3 above) to identify which database you want to use with that action.
When this action is triggered, the select list is created and the contents of the first record entered in the variables entered in the Variable column (point 5 above) – see Showing Records from a Database for more information.
Working with Databases - Overview