Introduction

This article describes how to connect r_keeper to an installed PostgreSQL.
It is necessary to first complete the PostgreSQL installation and configuration.

Connecting to r_keeper 7

  1. To connect r_keeper 7 to PostgreSQL, download the psqlodbc driver corresponding to your PostgreSQL version from https://www.postgresql.org/ftp/odbc/versions/msi/
  2. Extract it from the archive and run psqlodbc-setup.exe.

Configuring ODBC Connections

  1. To install the drivers correctly, find and run Application Stack Builder installed with the PostgreSQL database
    image2021-6-23_11-25-50.png
  2. The application will offer you to select a database.

    Select PostgreSQL 13 and click Next.
  3. Select Database Drivers > psql ODBC (32 bit) and click Next

  4. Select a location to store the installation files.
    Proceed with the installation and wait for it to complete.
    Wait for the system to reboot after installation if necessary.
  5. Restart Application Stack Builder.
    All steps are identical, but you need to select another component Database Drivers> pgsql ODBC (64 bit).
  6. Launch Application Stack Builder one last time.

  7. All steps are identical, but you need to select both Database Drivers> pgsql ODBC (32 bit) and (64 bit) components.
    The system will notify you that all installations are successfully completed.

    Why do we need multiple installations

    The reason is that there was the following problem with the application earlier: with different combinations, it downloaded different installers. This caused different driver components to be installed. You can skip these steps, but we strongly recommend following the instruction above to avoid errors.

Settings in r_keeper 7

To connect r_keeper to the PostgreSQL database, you also need to create a new connection in the External Database Configurations reference.

  1. Go to Service > Data Export > External Database Configurations
  2. Make a copy of PostgreSQL, for example, pgs. To do this:
    • Right-click on the database name
    • Select New (as copy) External Base Configuration
    • Enter the name of the new database
    • Change the status to Active.
    • Replace the database name for the DATABASE parameter in the Connection string with the one entered above. You will get the following connection string:
      Provider = MSDASQL.1; Persist Security Info = False; User ID = username; Extended Properties = "DRIVER = {PostgreSQL ODBC Driver (UNICODE)}; DATABASE = pgs; SERVER = localhost; PORT = 5432; UID = username; MaxVarcharSize = 2048 "
    • To test the connection, click on the value of the Connection string parameter and then on three dot
    • Click Build

    • Enter the password for the superuser and click Test Connection
    • In case of a successful connection, the system will display the following message:

If the connection has not been established, you can correct the connection string manually:

  1. Go to the Provider tab in the Data Link Properties window
  2. Select Microsoft OLE DB Provider for ODBC Drivers
  3. On the Connection tab, select Use connection string and click Build... 
  4. In the Select Data Source window, click New next to the empty file name
  5. Select PostgreSQL ODBC Driver (Unicode). If there is no such option, then select PostgreSQL Unicode. Click Next.
  6. Fill in an arbitrary data source file name. This file will not be used when working. Click Next and Finish.
  7. The driver setup window will appear:
    • Enter your login and password. You can use the data for the postgres superuser.
    • Leave the Server and Port fields empty and the default value: localhost:5432
    • If you leave the Database field blank, then postgres will be used as the default database. Therefore, it is better to specify the name of the database that you created to work with r_keeper.
    • Click OK.
  8. The Data Link Properties window will reopen. Check the connection again and click OK.
  • Add a link to the created connection to the database in the report server: Options > OLAP reports > Reporting Servers.
    On the same tab, specify the user and password.
  • Make sure the export works. Go to Service > Data Export > Export to Other Database.
    Enter the database, password, and username and click Test.

    The rest of the report server settings are standard.
  • Write UseSQL = 1 in the rk7srv.INI file and restart the reference server.
    The connection is established. Now you can work, for example, with cubic reports.
  • No labels