Creating a PostgreSQL Database
Introduction
This document explains how to create a PostgreSQL database for BOC Management Office® products. You can create the database automatically via the Windows Command Prompt or create the database manually using pgAdmin or create the database from script.
Prerequisites
Before you can create a BOC Management Office® database with PostgreSQL, you need to have the following software:
- The latest 64 Bit version PostgreSQL server on the database server.
- pgAdmin, a database management software for PostgreSQL on the database server, if you prefer to create the database manually. When you install PostgreSQL server, pgAdmin is installed along with the other tools.
- The latest 64 Bit Unicode PostgreSQL ODBC driver which allows the BOC Management Office® product to access the PostgreSQL database on the application server.
Creating a Database Automatically
BOC Management Office® products require a dedicated database that can be created automatically via the Windows Command Prompt. Perform the following operation on the machine where the application server is installed:
1. Open the Command Prompt as administrator and navigate to the application server installation directory. | |
2. Adapt the following command with your actual values and execute it: .\amain.exe -mode install -db <new-database-name> -dbadmin <my-database-admin-name> -dbadminpw <my-database-admin-password> -dt PostgreSQL -lib "<pathname>\<library file>.axl" -lic "<pathname>\<licence file>.xxl" |
The database is ready for use. For security reasons, the password of the role »ADOxx« should be changed now (see Change Password of role »ADOxx« (Optional)).
Placeholders
Here is an explanation of the placeholders in the command:
<new-database-name>
: The name of the new database.<my-database-admin-name>
: The username of your database administrator.<my-database-admin-password>
: The password of your database administrator.<pathname>\<library file>.axl
: The full path and filename of the library file. Wrap in quotes.<my-database-admin-password>
: The full path and filename of the licence file. Wrap in quotes.
Example
.\amain.exe -mode install -db adodb -dbadmin postgres -dbadminpw secret -dt PostgreSQL -lib "C:\Temp\Standard Library.axl" -lic "C:\Temp\licence.xxl"
Creating a Database Manually
If creating a database automatically is not possible in a specific scenario (e.g. because of specific organisational restrictions), you can create and configure a database for your BOC Management Office® product manually using pgAdmin. pgAdmin is a graphical administration tool for PostgreSQL. It is part of the standard setup of PostgreSQL server.
Start pgAdmin
1. Start pgAdmin and enter the master password you have set during the PostgreSQL server installation. |
Create New Role »ADOxx«
The login to a BOC Management Office® database requires a role »ADOxx«.
1. In the Browser pane, under Servers\<my-server-name>, right-click Login/Group Roles, point to Create, and then click Login/Group Role. | |
2. In the Name box, enter the name »ADOxx«. | |
3. Go to the Definition tab. In the Password box, enter the password »r0KaQIFA]cPd2Ave«. | |
4. Go to the Privileges tab. Turn on Can login?, and then click Save. |
Create New Role »ADOXX_BOOT«
The role »ADOXX_BOOT« is needed to change the password of the role »ADOxx«.
1. In the Browser pane, under Servers\<my-server-name>, right-click Login/Group Roles, point to Create, and then click Login/Group Role. | |
2. In the Name box, enter the name »ADOXX_BOOT«. | |
3. Go to the Definition tab. In the Password box, enter the password »iCfCK!lHP8S1L]Ry«. | |
4. Go to the Privileges tab. Turn on Can login?, and then click Save. |
Create New Database
1. In the Browser pane, under Servers\<my-server-name>, right-click Databases, point to Create, and then click Database. | |
2. In the Database box, enter the name of the new database. From the Owner list, select the role »ADOxx«. | |
3. Go to the Security tab. In the Privileges pane, click + Add row twice to add two rows. Add »ADOxx« and »ADOXX_BOOT« as grantees and assign them both Connect privileges to the database. Then, click Save. |
The new database will be created.
Create New Schema »ADOxx«
Creating tables in a BOC Management Office® database requires a database schema »ADOxx«.
1. In the Browser pane, under Servers\<my-server-name>\Databases, right-click your database, point to Create, and then click Schema. | |
2. In the Name box, enter the name »ADOxx«. | |
3. Go to the Security tab. In the Privileges pane, click + Add row twice to add two rows. Add »ADOxx« and »ADOXX_BOOT« as grantees and assign them both Usage privileges to the schema. | |
4. Go to the Default privileges tab. In the Tables tab, click + Add row to add a row. Add »ADOxx« as a grantee and assign them the Insert, Select, Update and the Delete privileges for tables. Then, click Save. |
The new schema »ADOxx« will be created.
Create ODBC Data Source
Create an ODBC data source for PostgreSQL server on the server where pgAdmin is installed, as well as on the machine where the application server is installed.
The ODBC data source must be created using the 64-bit version of the odbcad32.exe
file located in the <WINDIR>\system32
folder. <WINDIR>
is the path to your Windows installation directory.
1. Go to the System DSN tab and click Add. | |
2. Select the PostgreSQL Unicode(x64) driver and click Finish. | |
3. Enter the following data:
| |
4. In the Options area, click the Datasource button and go to Page 2. Clear the LF <-> CR/LF conversion check box and then click OK. | |
5. Click Save to finish creating the ODBC data source. |
Create Tables and Initialise Database
Finally, you need to create tables and initialise the database. Perform the following operation on the machine where the application server is installed.
1. Open the Command Prompt as administrator and navigate to the application server installation directory. | |
2. Adapt the following command with your actual values and execute it: .\amain.exe -mode install -dbimode tablesonly -db <new-database-name> -dbadmin <my-database-admin-name> -dbadminpw <my-database-admin-password> -dt PostgreSQL -lib "<pathname>\<library file>.axl" -lic "<pathname>\<licence file>.xxl" |
The database is ready for use. For security reasons, the password of the role »ADOxx« should be changed now (see Change Password of role »ADOxx« (Optional)).
Placeholders
Here is an explanation of the placeholders in the command:
<new-database-name>
: The name of the new database.<my-database-admin-name>
: The username of your database administrator.<my-database-admin-password>
: The password of your database administrator.<pathname>\<library file>.axl
: The full path and filename of the library file. Wrap in quotes.<my-database-admin-password>
: The full path and filename of the licence file. Wrap in quotes.
Example
.\amain.exe -mode install -dbimode tablesonly -db adodb -dbadmin postgres -dbadminpw secret -dt PostgreSQL -lib "C:\Temp\Standard Library.axl" -lic "C:\Temp\licence.xxl"
Creating a database from Script
As an alternative to creating the database automatically or manually using PgAdmin, you can create a database from script.
The following scripts will be used:
prepareDB.sql
postgresql.sql
You can find these scripts in the installation package of your BOC Management Office® product in the folder 02 Rich Client\dbinfo
.
1. Launch psql and enter the required information such as Server, Database, Port, Username and Password. Press Enter to use the default values in the square brackets. You can go with the default database and the database superuser »postgres«. | |
2. First, the roles »ADOxx« and »ADOXX_BOOT«, the new database and the schema »ADOxx« need to be created with the script prepareDB.sql. Open prepareDB.sql in a text editor and set the following parameter:
| |
3. Next, the tables need to prepared with the script postgresql.sql. To run the script in psql, type \i <pathname>/postgresql.sql. | |
4. Create an ODBC data source on the machine where the application server is installed (see Create ODBC Data Source). | |
5. Finally, the new database needs to be initialised. Open the Command Prompt as administrator and navigate to the application server installation directory. Adapt the following command with your actual values and execute it: .\amain.exe -mode install -dbimode initonly -db <new-database-name> -dbadmin <my-database-admin-name> -dbadminpw <my-database-admin-password> -dt PostgreSQL -lib "<pathname>\<library file>.axl" -lic "<pathname>\<licence file>.xxl". Here is an explanation of the placeholders in the command:
|
The database is initialised and is then ready for use. For security reasons, the password of the role »ADOxx« should be changed now (see Change Password of role »ADOxx« (Optional)).
Change Password of role »ADOxx« (Optional)
The role »ADOxx« is required for login to an ADOxx database. For security reasons, the default password »r0KaQIFA]cPd2Ave« of the role »ADOxx« should be changed after the initialisation of the database.
To change the password of the role »ADOxx«, the role »ADOXX_BOOT« is required. Perform the following steps on the machine where the application server is installed:
1. Open the Command Prompt as administrator and navigate to the application server installation directory. | |
2. Adapt the following command with your actual values and execute it: .\amain.exe -mode install -db <my-database-name> -dbadmin ADOxx -dbadminpw <old-database-user-password> -dt PostgreSQL -changeownerpw -newpw <new-database-user-password> |
The password of the role »ADOxx« is changed.
Placeholders
Here is an explanation of the placeholders in the command:
<my-database-name>
: The name of the database.<old-database-user-password>
: The current password of the database user »ADOxx«.<new-database-user-password>
: The new password that you want to assign to the database user »ADOxx«.
Example
.\amain.exe -mode install -db adodb -dbadmin ADOxx -dbadminpw r0KaQIFA]cPd2Ave -dt PostgreSQL -changeownerpw -newpw secret