Opus Tutorial:

Using Databases

Part 1: Retrieving Records from a Database

Introduction

This tutorial will demonstrate how to connect Opus Pro to a database and display records from that database in your Opus publication. It is only applicable to Opus Pro and for standalone publications. For accessing web-based databases see the tutorial on Post Web Data. It assumes that you have a copy of Microsoft Access to create a database you will use. This tutorial does not cover the use of databases in web-based publications.

Please find below a step-by-step procedure for creating a small test database, then setting up a Database Query action and displaying the returned records:

  1. Create an Access database with the fields:
    NAME
    AGE
    OCCUPATION

    All of these fields can be set as "Text" format

  2. Save the table as Table1 and do not set up a primary key
  3. Add a few rows of test data to your table then save and close Microsoft Access
  4. Launch Opus Pro and create a new publication
  5. Right-click on the page and select Edit Actions
  6. Select the Triggers tab
  7. Select the Synchronisation group
  8. Double-click on the On Show trigger to apply this to the page
  9. Select the Actions tab
  10. Select the Database group of actions then double-click the Database Query action to apply this. This should open a set of Database Query options
  11. Click on the Select button next to the DSN drop-down menu
  12. In the Select Data Source window, click New
  13. Select Microsoft Access Driver (*.mdb) from the list of drivers and click Next.
  14. You can name this connection what ever you choose. Make a note of the name given and click Next
  15. You will now be shown a summary of the changes made, just click OK to confirm
  16. You will now need to specify which database to use. Click on Select and navigate to you database. Once selected, click OK
  17. Ensure that the DSN you have just created is highlighted in the list and select OK
  18. You will now return to the Database Query tab, but the DSN will be set to FILEDSN=.dsn
  19. Click on SQL Wizard, remove the tick from the Select Data for Updating/Deleting and click Next
  20. From the table drop-down list, select the table in which your required data resides, ensure that all of the field names are ticked, then click Next
  21. This page allows you to apply SQL operators to filter the data (i.e; to search the database for certain information). For the purposes of this example, simply click Next
  22. This page allows you to sort the results by field. Leave the Field setting as (None) and click Finish
  23. You will now return to the Database Query tab, but the Query name will read Query1
  24. At the bottom of this window is a grid showing all of the variables assigned to your Field names. At present there are no variables assigned
  25. Click on the box next to the Name field (which currently reads None) and an Insert Variable window will open. Click New and type in NAME, then click OK twice
  26. Repeat Step 25 for your other Field names, calling their associated variables AGE and OCCUPATION respectively
  27. Click Apply then OK to return to the main editor window
  28. Add three text boxes to the page.
  29. Left-click in the first text box, wait a moment, then left-click again to display the caret (flashing I-beam)
  30. The Insert Variable icon (a capital A with a red asterisk in the top-left corner) on the toolbar should now be available. Click this and select the NAME variable
  31. Repeat this for the other two text boxes, inserting the AGE and OCCUPATION variables
  32. Preview the publication
  33. The first record of the database should now be displayed in the three text boxes

If you wish to add previous and next record actions to navigate through the recordes, simply add two buttons to the page and apply a Previous Record action to the left-most button and a Next Record action to the other. Please ensure that you select the appropriate query from the drop-down menu before clicking the Apply button.

tutorial on connecting to a database with Opus Pro