PC SOFT - Online help
Version: 18.0
Community: 0 annotation | Annotate RSS feed
Warning: Content of this document will be soon extended. Warning: Content of this document will be soon extended.
This page is also available for: WINDEV, WEBDEV
Displayed version:
Native PostgreSQL Access: Limitations and notes
 

 
WinDev WebDev - Server code WebDev - Browser code WinDev Mobile Reports and Queries   Windows Linux Windows Mobile Windows Phone Android Android Widget iPhone/iPad Windows Store apps   Java PHP Ajax User code (UMC) External language   HyperFileSQL HyperFileSQL Client/Server Stored procedures Hyper File 5.5 OLE DB ODBC Native Accesses

Overview
The following paragraphs present the notes and the limits specific to the Native PostgreSQL Access.

General notes

Required versions

Caution: This version of the Native PostgreSQL Access operates with:
  • WinDev 18 or WebDev 18.
  • PostgreSQL from version 7.4 (the tests have been run with version 8.3.6 and version 9.1.3).

Using the WLanguage commands in the SQL queries

The WLanguage commands cannot be used in the SQL queries.

Managing the large files

When handling large files, all the records are loaded in memory. To reduce the amount of memory used, 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 HyperFileSQL 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, WinDev/WebDev automatically perform the conversion 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 HyperFileSQL functions for more details.

Managing the 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 Access 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.

Note: By default, the Native Access tries to open a 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:\SSL Certificates\CA.crt ; SSL Cert = C:\SSL Certificates\my.crt ; SSL Key = C:\SSL Certificates\my.key ; SSL Mode = verify-full

See Optional connection information for more details.
New in version 18
SSL certificates

The Native PostgreSQL Access 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.

Note: By default, the Native Access tries to open a 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:\SSL Certificates\CA.crt ; SSL Cert = C:\SSL Certificates\my.crt ; SSL Key = C:\SSL Certificates\my.key ; SSL Mode = verify-full

See Optional connection information for more details.
SSL certificates

The Native PostgreSQL Access 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.

Note: By default, the Native Access tries to open a 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:\SSL Certificates\CA.crt ; SSL Cert = C:\SSL Certificates\my.crt ; SSL Key = C:\SSL Certificates\my.key ; SSL Mode = verify-full

See Optional connection information for more details.

General limitations
Locking the records

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

Identical records

The Native PostgreSQL Access cannot differentiate between two identical records (which means records with identical values for all the items, excluding the 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 Access. If you are using an array item, only the value of subscript 1 in the array will be handled by the HyperFileSQL 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 the 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 the Native PostgreSQL Access. However, you have the ability to use the hQueryWithoutCorrection constant.

Limitations for the HyperFileSQL and SQL functions
The limitations for using the HyperFileSQL and SQL functions with the native PostgreSQL access are as follows:

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

Managing the automatic identifiers

The management of the hForceIdAuto and hSetIdAuto constants is identical to the management performed by the HyperFileSQL engine.

To re-initialize the start value of the sequence of an automatic identifier after the call to HAdd associated with the hForceIdAuto, 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)
 
 
Keywords: Native Access, Native PostgreSQL Access, HyperFileSQL, PostgreSQL (Native access), SQL
Minimum required version: 14

Last update: 02/07/2013 | Send a report | Local help
Community: 0 annotation RSS feed
Please login to annotate | Terms of service
    PC SOFT | Home | PC SOFT Forums | PC SOFT FAQ | PC SOFT Blogs | WINDEV Express | Technical Support | Contacts | Online help services | PC SOFT on Facebook
    Official Supplier of the French Olympic Preparation

    Registered trademarks. Copyright ©2009-2013 PC SOFT. All rights reserved
    This website has been developed with WEBDEV and HyperFileSQL
    Developed with WebDev and HyperFileSQL