Skip to main content
Version: ADONIS 16/ADOIT 17/ADOGRC 13

Creating a PostgreSQL Database

Introduction​

This document explains how to create a PostgreSQL database for ADOxx products. You can create the database automatically using via the Windows Command Prompt or create the database manually using pgAdmin or create the database from script.

Prerequisites​

Before you can create an ADOxx 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 ADOxx product to access the PostgreSQL database on the application server.

Creating a Database Automatically​

ADOxx 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.Open the Command Prompt
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"Create database automatically

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 ADOxx 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.pgAdmin login screen

Create New Role »ADOxx«​

The login to an ADOxx 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 an ADOxx 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 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.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».
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 Save to finish creating the ODBC data source.Test and save 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.Open the Command Prompt
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"Create tables and initialise database

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 ADOxx 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 the machine where the application server is installed (see Create ODBC Data Source).Select ODBC driver
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:
  • <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.
  • <pathname>\<licence file>.xxl: The full path and filename of the licence file. Wrap in quotes.
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 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.Open the Command Prompt
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>Change 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