• Overview
  • Main remarks
  • Required versions
  • Using WLanguage commands in SQL queries
  • Managing large 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
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WindowsLinuxPHPWEBDEV - Browser code
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Stored procedures
Native PostgreSQL Connector: Specific features and remarks
Native Connectors (Native Accesses)Available only with this kind of connection
The following paragraphs present the main aspects and specific features of the Native PostgreSQL Connector.
Main remarks

Required versions

Caution: This version of the Native PostgreSQL Connector works with:
  • WINDEV 28 or WEBDEV 28.
  • 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 files

When handling large files, all the records are loaded in memory. To reduce memory usage, it is recommended to:
  • use a filter (HFilter) before initializing an iteration (HReadFirst / HReadLast, ...).
  • 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: ChangeCharset 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:
  1. On the server: Use a PostgreSQL server that supports the 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.
  2. On the client computer: Use a PostgreSQL client layer compiled with the support for SSL connections.
  3. By default, the PostgreSQL client layer will find the SSL certificate files in the directory: %APPDATA%\postgresql. For example: "C:\Users\Florence\appData\roaming\postgresql". You have the ability to rename and place these files in different directory by specifying their locations in the connection string via the keywords: SSL Cret, SSL Key, SSL CA.
  4. 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.
Remark: By default, the Native Connector tries to open an SSL connection then a non-SSL connection in case of failure.
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 to use the default certificates : The postgresql.crt, postgresql.key, Root.crt files are found 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
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 specific features for using the HFSQL and SQL functions with Native PostgreSQL Connector are as follows:
Specific featuresAffected functions
Multi-file query:
HAdd, HModify and HDelete are not supported.

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 "<FileName>_<ItemName>_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:

Unsupported SQL commands

The following SQL commands are not supported on a PostgreSQL data source:
  • TOP
  • BIN
  • OCT
  • HEX
  • LOG10
  • LEFT(String, length)
Minimum version required
  • Version 14
This page is also available for…
Click [Add] to post a comment