Skip to main content

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 using the tool Database Administration (adbinstws.exe) 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 using the tool Database Administration (adbinstws.exe). This tool can be launched from the BOC Management Office® product start menu by selecting Create new database, or from the installation directory by running adbinstws.exe.

To create a new database, proceed as follows:

1. Select Create new database, and then click Next.Create new database
2. From the Database system list, select PostgreSQL. Enter the name of the new database in the Database name box. In the Library File and Licence File boxes, select the library file and the licence file provided by your BOC account manager. The file extension of the library file is "*.axl" and the extension of the licence file "*.xxl". The licence also indicates which components are available in the tool and the number of users who can use the application in parallel.Enter data of new database
3. Enter the name and password of the database administrator in the DB-administrator user name and DB-administrator password boxes.Enter database administrator
4. Optionally, activate Use customary schema name/DB user if you want to use another database login than »ADOxx« to connect to the database and enter name and password of the user to be used in the fields DB-user name and DB-user password.Enter customary database user (optional)
5. Make sure that the settings are correct, and then click Create. The database is created. From the Selected primary language list, select the governing language for creating models and objects. The primary language serves as fallback language if some text is not available in other languages. Confirm with OK.Select primary language

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)).

Creating a Database Manually

note

The following complex procedure is only necessary if creating a database automatically is not possible in a specific scenario (e.g. because of specific organisational restrictions). Otherwise you can use the tool Database Administration (adbinstws.exe), which is installed together with every version of any BOC Management Office® product. It creates and configures BOC Management Office® databases automatically (see Creating a Database Automatically).

The following sections explain in detail, how a database for your BOC Management Office® product can be created and configured 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.pgAdmin login screen

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.Create new role »ADOxx«
2. In the Name box, enter the name »ADOxx«.Enter role name
3. Go to the Definition tab. In the Password box, enter the password »r0KaQIFA]cPd2Ave«.Enter role password
4. Go to the Privileges tab. Turn on Can login?, and then click Save.Turn on login privileges

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.Create new role »ADOXX_BOOT«
2. In the Name box, enter the name »ADOXX_BOOT«.Enter role name
3. Go to the Definition tab. In the Password box, enter the password »iCfCK!lHP8S1L]Ry«.Enter role password
4. Go to the Privileges tab. Turn on Can login?, and then click Save.Turn on login privileges

Create New Database

1. In the Browser pane, under Servers\<my-server-name>, right-click Databases, point to Create, and then click Database.Create new database
2. In the Database box, enter the name of the new database. From the Owner list, select the role »ADOxx«.Enter database name
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.Edit security settings

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.Create new schema
2. In the Name box, enter the name »ADOxx«.Enter schema name
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.Edit security settings
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.Edit default privileges

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 each computer with a BOC Management Office® product installation.

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.Add ODBC connection
2. Select the PostgreSQL Unicode(x64) driver and click Finish.Select ODBC driver
3. Enter the following data:
  • Data Source: A descriptive name for the ODBC data source. Can be the same as the database name.
  • Database: The name of the database.
  • Server: The host name or IP address of the database server.
  • Port: The port on which the PostgreSQL services are running. The default value is «5432».
  • User Name: The name of the database super user («postgres»).
  • Password: The password of the database super user that you provided during the PostgreSQL server setup.
Enter 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.Deactivate LF <-> CR/LF conversion
5. Click Test to test the newly created data source. If the test is successful, the data source can be used. Click Save to finish creating the ODBC data source.Test and save data source

Create Tables and Initialise PostgreSQL Database

Fill the new database using the tool Database Administration (adbinstws.exe). You can start this tool via the BOC Management Office® product start menu via the entry Create new database or in the installation directory using the file adbinstws.exe.

1. Select Create new database, and then click Next.Select »Create new database«
2. From the Database system list, select PostgreSQL. In the Database name box, enter the name of the ODBC data source you created. In the Library File and Licence File boxes, select the library file and the licence file provided by your BOC account manager. Clear the Create new database check box. Make sure that the Create tables check box is selected. Enter the name and password of the database administrator in the fields DB-administrator user name and DB-administrator password, and then click Create.Create tables

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)).

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«.Launch psql
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:
  • /set .vDBName 'adoxxdb': Replace adoxxdb with the name of your new database.
To run the script in psql, type \i <pathname>/prepareDB.sql.
Execute script prepareDB.sql
3. Next, the tables need to prepared with the script postgresql.sql. To run the script in psql, type \i <pathname>/postgresql.sql.Execute script postgresql.sql
4. Create an ODBC data source on every computer with an installation of a BOC Management Office® product (see Create ODBC Data Source).Select ODBC driver
5. Finally, the new database needs to be initialised. Launch the Windows Command Prompt and navigate to the BOC Management Office® product installation directory. Adapt the following command with your actual values and execute it: amain.exe -ws dbiws -dt PostgreSQL -db <my-database-name> -dbadmin postgres -dbadminpw <my-database-admin-password> -dbimode initonly -lib "<pathname>/library.axl" -lic "<pathname>/license.xxl".Initialise database

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 password 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. The password can be changed using the tool Database Administration (adbinstws.exe).

To change the password of the role »ADOxx«, proceed as follows:

1. Select Change database user password for an existing database, and then click Next.Select »Change password«
2. Enter the name and password of the role »ADOxx« or a database administrator in the DB-user name and DB-user password boxes. Enter the name of the database in the Database name box. From the Database system list, select PostgreSQL, and then click Next.Enter database data
3. Enter the new password in the New DB-user password and New DB-user password (confirmation), and then click Change.Change password

The password of the role »ADOxx« is changed.