How to Access the Database via ODBC

To access the database via a ODBC interface, the ODBC drivers must be installed on the PC that needs the access. The interface must be setup then. Take into account whether the program you want to use is a 32 or 64 bit application. Additionally permissions must be entered in the database setup file on the PPS server.

Note

Starting with version V2.0.X of PPS, the databases of skr and pps were merged to simplify access.. Depending on the use case, one ODBC connection to the database is sufficient to gain access to tables of the skr and pps database.

ODBC access for a 32 bit program

  1. ODBC driver is installed on the client PC
  2. 32 bit application
    1
    Open the file in an editor on the PPS server.
    2
    In the upper part you will find explanatory notes for the use of the parameters. Depending on your requirements you can try to find out how to achieve more precise settings. That is rather task of an IT specialist. For most of the cases the following example is sufficient.
    Scroll to this point:
    # TYPE DATABASE USER ADDRESS METHOD
    # IPv4 local connections:
    host all all 127.0.0.1/32 md5
    3
    In an additional row enter the IP address of the PC that shall obtain the access to the database. Example: The PC which should get access has the IP address 172.125.168.25.
    4
    Then, you enter the following row:
    host all all 172.125.168.25/32 md5
    5
    If you use IPv6 addresses like these fe80::c99:877a:4a1:6717 in your network, then enter the following line beneath # IPv6 local connections:
    host all all fe80::c99:877a:4a1:6717/128 md5
    6
    Save the file. The changes are immediately effective without restarting the SKR3_DB service. However, pay special attention to the correct input as the service would not start otherwise.
    7
    Set up the ODBC connections for the 32 Bit Client applications.
    Depending on the operating system, whether it is a 32 bit or a 64 bit system, you call up a different program.
    You can save much input effort taking the D:\PPS\PpsJBossServer\PpsServerInstallScripts\ODBC-DefaultConnectionSetup1500.reg file from the PPS installation to the client PC and executing it there.
    (up to PPS V1.10.X: ODBC-DefaultConnectionSetup903.reg).
    This way, you only need to exchange the localhost entry by the PPS server name.
    8
    Start this program on the client PC.
    PC with 64 bit Windows:
    C:\windows\SysWOW64\odbcad32.exe
    PC with 32 bit Windows:
    C:\windows\system32\odbcad32.exe
    You get the programs offered, if you enter ODBC in the Windows search field of a Windows 10 based system.
    2. 9
    Click on Add.
    3. 10
    Scroll downwards and select the PostgreSQL Unicode driver.
    11
    Confirm with Finish .
  1. A dialog opens up where you must make entries.

SKR Database

    1
    Make the following settings to access the SKR database:
    2
    Click on the Test button.
  1. In case of success appears this dialog
    2. 3
    If an error message appears check your entries and whether the SKR3_DB service is running.
  1. You can access the SKR database now.
  1. You need access to the PPS database.
    1
    Carry out the above mentioned steps excepting the SKR entry.
    2
    For the access to the PPS database make the following settings:

If your application is a 64 bit one, execute this program:

c:\windows\System32\odbcad32.exe.

    3
    Select StollDB64_... as Data Source.
    Apart from that all steps are identical.

How to Use the Excel Program

If you have installed also the ODBC programing examples, then in the D:\PPS\ODBC_Examples\Excel\ folder you will find the StollReportFromSkrToExcel.xlsb program. To be able to use this program, Excel, version 2007 or higher must be installed.

    1
    Start the program double-clicking it.
    2
    As the program executes Visual Basic Code you need to confirm the security advice and allow the execution of the program.
    3
    The program makes extensive use of ActiveX Controls. If the work is done with remote desktop or with external monitors with different resolutions, the controls can change their position and the font their size due to a problem with the Microsoft libraries. In this case setup the same resolution for all the screens or use another PC.