How to set up and test using PostgreSQL

Environment

PostgreSQL 12.3 can be accessed via ODBC as any ODBC compliant RDBMS.

Make sure to download and install PostgreSQL 12.3 (or later), its accompanying management studio pgAdmin (https://www.postgresql.org/download/windows/) and its ODBC drivers (https://www.postgresql.org/ftp/odbc/versions/msi/) .

When configuring your ODBC Data Source, always set Use Declare/Fetch to OFF.

Also, the driver configuration Max_Rows_Fetched will be set to 100 and automatically added to tables’ INT files by default when creating new PostgreSQL tables or converting tables to PostgreSQL. That default may be changed by adjusting the Default_Max_Rows keyword in the backend’s configuration file (PostgreSQL.int) or its corresponding attribute, DF_DATABASE_DEFAULT_MAX_ROWS.

We advise using Max_Rows_Fetched to avoid performance issues and “out of memory” errors. When Max_Rows_Fetched is not used, Find GT/LT in PostgreSQL will be slow on large tables and may cause “out of memory” errors on extremely large tables.

Even if you are going to focus on 32-bit for your application, you must also install the 64-bit PostgreSQL ODBC driver because the DataFlex Studio and other DataFlex tools are 64 bits.

Creating a Unicode Database

  1. Open pgAdmin, the administration tool from PostgreSQL whose interface is a webpage

  1. Log in when prompted for a password
  2. pgAdmin will display its dashboard - it uses Chrome (browser)
  3. Expand the tree until you see Databases

  1. Right-click and select Create | Database
  2. Enter the database name and owner (role) for the new database
  3. Select the Definition tab to specify the collate that should be applied to the new database

  1. Under SQL, you can review the ‘create database’ statement
  2. Click on Save to proceed with the database creation

Setting up DSNs in ODBC Administrator

  1. In the ODBC Administrator, select the PostgreSQL Unicode to create a new data source

  1. Enter your server connection information

  1. Click on Datasource

  1. Make sure Use Declare/Fetch is unchecked
  2. Click on OK
  3. Click on Save

 

See Also

Environment Setup Notes