PostgreSQL Native Connector: Features and notes

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / Native Connectors/Native Accesses / Native PostgreSQL Connector

Overview
Main remarks
Required versions
Using WLanguage commands in SQL queries
Managing large data files
Support for Unicode with the HFSQL functions
Managing character sets with the SQL functions
SSL certificates
Main specific features
Locking records
Identical records
Array items
Partial indexing of memos and full-text indexing
Nested queries
Specific features for the HFSQL and SQL functions
Managing automatic identifiers
Unsupported SQL commands

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Native PostgreSQL Connector for WINDEV and WEBDEV

PostgreSQL Native Connector: Features and notes

Available only with this kind of connection

Overview

The following paragraphs present the main aspects and specific features of the Native PostgreSQL Connector.

Main remarks

Required versions

Warning: This version of the PostgreSQL Native Connector only works with:

WINDEV or WEBDEV 2025.
PostgreSQL from version 7.4 (tests have been run with version 8.3.6 and version 9.1.3).

Using WLanguage commands in SQL queries

WLanguage commands cannot be used in SQL queries.

Managing large data files

When handling large data files, all records are loaded into memory. To reduce memory usage, it is recommended to:

apply a filter (using HFilter) before starting a read operation (with HReadFirst / HReadLast, etc.).
use queries whose selection conditions are selective enough.
avoid performing searches.

Support for Unicode with the HFSQL functions

To use Unicode strings, the "UTF8" charset must be specified when creating the PostgreSQL database. In this case, all the strings will be Unicode strings.

When Unicode is supported on the connection (default mode), the text items of the queries run with the hQueryWithoutCorrection constant are in Unicode format. If a Unicode string is assigned to an ANSI string, the conversion is automatically performed by WINDEV and WEBDEV by using the character set defined in ChangeCharset.

Caution: The ChangeCharset function must be used BEFORE opening the connection.. The connection must be closed and reopened after each call to ChangeCharset in order for the data to be properly converted to the specified character set.

If the connection is ANSI ("WD Unicode Support = 0" in the extended information specified by HDescribeConnection or HOpenConnection), the type of the text items for the queries run with the hQueryWithoutCorrection constant is ANSI.

For more details on Unicode or ANSI connections, see Programming a PostgreSQL database with the HFSQL functions.

Managing character sets with the SQL functions

All the exchanges are performed in the current character set (charset). An error occurs during the connection if the server does not support the conversions from or to the current character set.

In this case, ChangeCharset must be used before SQLConnect to select a character set compatible with the server.

SSL certificates

The Native PostgreSQL Connector supports the connections that use SSL certificates.

To support the connections that use SSL certificates:

On the server: Use a PostgreSQL server that supports SSL connections. See the PostgreSQL documentation to find out how to generate the SSL certificate files and how to configure the server to only allow the SSL connections.
On the client workstation: Using a PostgreSQL client library compiled with SSL connection management.
By default, the PostgreSQL client library will Seek SSL certificate files in the directory: %APPDATA%\postgresql. For example: "C:\Users\Florence\appData\roaming\postgresql". These files can be renamed and placed in different directories by indicating their location in the connection chain using the keywords: SSL Cret, SSL Key, SSL CA.
Use the keywords corresponding to the SSL options in the connection information. The recognized keywords are:
- SSL Key
- SSL Cert
- SSL CA
- SSL CRL
- SSL Mode

These parameters correspond to the parameters of same name found in PQconnectdb.

Note: By default, the Native Connector tries to open an SSL connection, then, if this fails, a non-SSL connection..
To check the opening mode of the connection, add the "SSL Mode" keyword to the extended information of the connection, followed by the desired mode.

Example of extended information for using default certificates: The files postgresql.crt, postgresql.key, Root.crt are located in the %APPDATA%\postgresql directory:

Server Port=5432;SSL Mode = verify-full

Example of extended information to use renamed certificates:

Server Port=5432;
SSL CA = C:\Certificats SSL\CA.crt ;
SSL Cert = C:\Certificats SSL\my.crt ;
SSL Key = C:\Certificats SSL\my.key ;
SSL Mode = verify-full

Note: the "Server Port" extended information allows you to modify the port number used for the connection..

For more details, see Optional connection information.

Main specific features

Locking records

The lock options (hLockWrite and hLockReadWrite constants) that can be used with HFSQL functions are not supported. In fact, lock operations are linked to transactions. A lock can only be removed once the transaction is completed, and there can be no lock without a transaction.

Identical records

The Native PostgreSQL Connector cannot differentiate between two identical records (which means with identical values for all items, excluding memo items).

When a record is modified or deleted, all the identical records will be modified or deleted.

Array items

The Array items are not supported by the Native Connector. If you are using an array item, only the value of index 1 in the array will be handled by HFSQL functions (read/write).

Partial indexing of memos and full-text indexing

The partial indexing of memos as well as the full-text indexing are not taken into account:

when importing PostgreSQL tables into the analysis.
when HCreation is used.

If these features are defined in the analysis, they will be taken into account only when running the SQL queries (with the hQueryWithoutCorrection constant).

Nested queries

The correction of nested queries is not supported in this version of Native PostgreSQL Connector. However, you have the ability to use the hQueryWithoutCorrection constant.

Specific features for the HFSQL and SQL functions

The specifics for using HFSQL and SQL functions with the PostgreSQL Native Connector are as follows:

Specific features	Affected functions

Multi-file query: HAdd, HModify and HDelete are not supported.	HAdd HModify HDelete

Managing automatic identifiers

The management of the hForceIDAuto and hSetIDAuto constants is identical to the management performed by the HFSQL engine.

To reset the start value of the sequence of an automatic identifier after calling HAdd with the hForceIDAuto constant, execute the following query with the hQueryWithoutCorrection constant:

ALTER SEQUENCE "<NomFichierDeDonnées>_<NomRubrique>_seq"
RESTART WITH [ Start identifier ]

Example: In order for the next automatic Identifier managed by PostgreSQL to start from 5, the following query must be used:

ALTER SEQUENCE "MyIdAuto_seqDataFile" RESTART WITH 5

Unsupported SQL commands

The following SQL commands are not supported on a PostgreSQL data source:

TOP
CHARACTER_LENGTH
BIN
OCT
HEX
LOG10
PATINDEX
LEFT(String, length)