/
TN 039: Using PostgreSQL Database with Advanced ODBC Historical Values driver

TN 039: Using PostgreSQL Database with Advanced ODBC Historical Values driver

Tech Note – August 14, 2023

Dream Report has a generic driver to connect to different database type data sources using Windows ODBC driver or direct connections. PostgreSQL database is a specific database format which manages the SQL requests in a slightly different way than standard SQL databases. As such, using the Advanced ODBC Historical Values driver, we will use custom scripts to get the data from PostgreSQL tables.

Configuring ODBC driver in Windows

First, we need to configure the ODBC driver for PostgreSQL in Windows. Install the appropriate 32-bit PostgreSQL ODBC driver, referring to the PostgreSQL web site for more information on available drivers - https://odbc.postgresql.org/

After installing the PostgreSQL ODBC driver, we need to configure it in the Windows ODBC Data Sources (32-bit) manager. Open Control Panel > Administrative Tools> ODBC Data Sources (32-bit):

image-20240927-125935.png

 or by executing the command line ODBC and choosing the corresponding application in 32-bit:

image-20240927-130130.png

In ODBC Data Source Administrator (32-Bit), add a DSN in the System DSN tab:

image-20240927-130204.png

Note: create ANSI driver and NOT UNICODE one

In the configuration window, create a unique Data Source (name) which will be referenced later in the Dream Report project, specify the name of Database, User Name, Password and Port number. Test connection, and if the information is correct, you will receive a “Connection successful” message.

image-20240927-130306.png

Creating a DSN in Dream Report

The next step is to create the DSN (Data Source Name) in Dream Report Studio. Go to Tools>DSN Manager. In the DSN Manager configuration, create a new DSN with ODBC “Connection type”. Under “Use DSN”, select the ODBC Driver which was created in previous step. Test the connection, and then click the “Add” button to add it to the list:

image-20240927-130416.png

Configuring the Advanced ODBC Historical driver in Dream Report

Open the Driver Configuration window from Connectivity > Driver Configuration, or from the left-side Quick Launch Bar

image-20240927-130445.png

In the driver configuration, choose Open Communication Protocols > ODBC > Advanced ODBC Historical Values, enter a logical name for the driver, driver and click on “Configure”:

image-20240927-130512.png

In the configuration popup choose “Connect Using DSN” and select the DSN created in Dream Report DSN Manager. Connect to Database and verify the connection.  In “Select source table or view”, select “Use Custom SQL Script”, and define two scripts - one for tag names and another one for data:

image-20240927-130628.png

For the “SQL Script to retrieve tag browse”, the script should be similar to the following (specific to your database):

select distinct "Tagname" from public."Table_Name"

-- where Tagname is the column name containing the list of tags.

For the data retrieval script (“SQL script to retrieve value”), the script should similar to the following:

select "Timestamp", "Value" from public."Table_Name" where "Tagname" = #address# and "Timestamp" >= #starttime# and "Timestamp" < #endtime#  

-- where “Tagname” is the column name containing the list of tags. This particular script contains the internal variables #address#, #starttime# and #endtime# which should be used, as shown in this example.

After defining the “Mapping Rules” (refer to the Dream Report Help file under the Communication Drivers > Open Communication Protocol > Advanced ODBC Drivers section for more details), click “OK” to close the configuration window, add [+] the driver to the list, and then click “OK”:  

image-20240927-130715.png

You can now reference this driver in all reporting objects and functions, as with any other driver.