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
- Before connecting first install the !PostgreSQL ODBC driver, its best to use full installation.
- 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)
:
- Press
Add
and enter the new setup:
- Now in the Reportman data access settings press
Search...
:
- In the
Connection
tab press Build
:
- Then create a new datasource with the same settings as in the pgSQL ODBC Setup:
- Customize DATABASE, PASSWORD and PORT parameters in the Connection String: