PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • Main remarks
  • Required versions
  • Using WLanguage commands in the SQL queries
  • Managing huge 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
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Native PostgreSQL Connector: Specific features and remarks
Native AccessesAvailable only with this kind of connection
Overview
The following paragraphs present the main remarks and the specific features of Native PostgreSQL Connector (also called Native PostgreSQL Access).
Main remarks

Required versions

Caution: This version of Native PostgreSQL Connector operates with:
  • WINDEV 23 or WEBDEV 23.
  • PostgreSQL from version 7.4 (tests have been run with version 8.3.6 and version 9.1.3).

Using WLanguage commands in the SQL queries

The WLanguage commands cannot be used in the SQL queries.

Managing huge files

When handling huge files, all the records are loaded in memory. To reduce the memory occupancy, we advise you to:
  • use a filter (HFilter) before initializing a browse (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 re-opened after each call to ChangeCharset in order for the data to be properly converted into 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.
See Programming a PostgreSQL database with the HFSQL functions for more details.

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.
Versions 18 and later

SSL certificates

The Native PostgreSQL Connector supports the connections that are using SSL certificates.
To support the connections that are using 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.
Note: 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, the "SSL Mode" keyword must be added into the extended information of the connection, followed by the requested 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
See Optional connection information for more details.
New in version 18

SSL certificates

The Native PostgreSQL Connector supports the connections that are using SSL certificates.
To support the connections that are using 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.
Note: 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, the "SSL Mode" keyword must be added into the extended information of the connection, followed by the requested 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
See Optional connection information for more details.

SSL certificates

The Native PostgreSQL Connector supports the connections that are using SSL certificates.
To support the connections that are using 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.
Note: 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, the "SSL Mode" keyword must be added into the extended information of the connection, followed by the requested 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
See Optional connection information for more details.
Main specific features

Locking records

The lock options (hLockWrite and hLockReadWrite) that can be used with the HFSQL functions are not supported in this version.

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 subscript 1 in the array will be handled by the HFSQL functions (in 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 using HCreation.
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.		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 re-initialize the start value for the sequence of an automatic identifier after the call to HAdd associated with the hForceIdAuto constant, run 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:
ALTER SEQUENCE "MyFile_AutoID_seq" 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)
See also
Minimum required version
  • Version 14
This page is also available for…
Comments
Click [Add] to post a comment