- 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
Native PostgreSQL Connector: Specific features and remarks
Available only with this kind of connection
The following paragraphs present the main aspects and specific features of the Native PostgreSQL Connector.
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
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
), the type of the text items for the queries run with the hQueryWithoutCorrection
constant is ANSI.
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.
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 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.
- On the client computer: Use a PostgreSQL client layer compiled with the support for SSL connections.
- 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.
- 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:
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
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.
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.
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).
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 features||Affected 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
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:
- LEFT(String, length)
This page is also available for…