Access to PostgreSQL Database

On this page:

Setting Up pgAdmin With Tunneling

It is convenient to use pgAdmin for the PostgreSQL database administration.

After installing the pgAdmin on a desktop machine, a tunneling can be used for accessing the framework remotely. In our case the tunneling was used because there wasn't a LAN connection. The same can be achieved with VPN. When on LAN, pgAdmin can connect directly to the database using its IP address.

We used Putty - here are the Putty tunnel settings. And here are the pgAdmin tunnel settings.

Be aware that Putty must run in parallel to pgAdmin since it maintains the tunnel connection.

User Login

To lift login restrictions for the database the following lines are added in our /etc/postgresql/9.6/main/pg_hba.conf file:

local masonsql all                                     password
host  masonsql all        127.0.0.1    255.255.255.255 password

Apache

The Apache has an administrative (read/write) permissions.

In our examples the Apache username is set in the /opt/demo_masonsql/etc/apache2.demo_masonsql.conf file.

Here is the masonsql user setting from our Apache configuration file:

PerlSetVar DBIconnect "dbi:Pg:dbname=masonsql;user=masonsql;password=<password>"

Reportman

Wine Version

The Reportman application has a guest (read only) permissions.

The Reportman Wine's server IP, username and password are set in the ADO string embedded in every .rep file.
The application updates it automatically with values set in these variables of Apache configuration:

PerlSetVar ReportmanADOserver "127.0.0.1"
PerlSetVar ReportmanADOdatabase "masonsql"
PerlSetVar ReportmanADOuser "report"
PerlSetVar ReportmanADOpassword "******"

Kylix Version (deprecated)

Do not use the Kylix version in new applications.

The Reportman Kylix version is deprecated because of the problems with supporting of the UTF encoding.
We report variable configuration only for legacy with old application:

# Reportman ADO server not used:
PerlSetVar ReportmanADOserver "0"
# Borland dir configuration
PerlSetVar BorlandConfig "/opt/my_application/etc/my_application.borland"

Repornam uses the libpq library to access the PostgreSQL database. In the /opt/masonsql/report/printreptopdf.kylix file, the environment variable LD_LIBRARY_PATH must be initialized to search libpq.so file located in the server.

Reportman Queries

The reporting queries in the .rpt files contain local parameters that are substituted with their values when a report is generated.

For example, the anagrafiche.wine.rep file contains following query:

select 
    anagrafiche.*
from 
    report_id, anagrafiche
where 
  :GROUP_ID > 0 and
  report_id.group_id = :GROUP_ID
  and anagrafiche.id=key

UNION

select 
    anagrafiche.*
from 
    anagrafiche
where 
  :GROUP_ID < 0 and
  anagrafiche.id = - :GROUP_ID

order by  id;

The parameter GROUP_ID has value -1. When the report is generating the Reportman substitutes occurrences of the :GROUP_ID with its current value.

Now the example query now looks like this:

select 
    anagrafiche.*
from 
    report_id, anagrafiche
where 
  -1 > 0 and
  report_id.group_id = -1
  and anagrafiche.id=key

UNION

select 
    anagrafiche.*
from 
    anagrafiche
where 
  -1 < 0 and
  anagrafiche.id = - -1

order by  id;

As seen in the example the Reportman parameters can be used similarly to pre-processor directives.

Database Connection

1. Before connecting first install the !PostgreSQL ODBC driver, its best to use full installation.
2. After the driver is installed add it to the System DNS in the ODBC Data Source Administrator which is available from Administrative Tools->Data Sources (ODBC):
   
3. Press Add and enter the new setup:
   
4. Now in the Reportman data access settings press Search... :
   
5. In the Connection tab press Build :
   
6. Then create a new datasource with the same settings as in the pgSQL ODBC Setup:
   
7. Customize DATABASE, PASSWORD and PORT parameters in the Connection String: