Native PostgreSQL Connector: Programming with SQL functions

WINDEV, WEBDEV AND WINDEV MOBILE
ONLINE HELP

Version:

Home | Sign in | English

Help

Overview

Programming

See also

Native PostgreSQL Connector: Programming with SQL functions

Available only with this kind of connection

Overview

The SQL functions can be used to handle the PostgreSQL database.

Differences compared to HFSQL programming: The use of SQL functions for handling PostgreSQL data does not require to import the structure of the files into the analysis. However, the data must be handled by queries only. The content of the PostgreSQL files cannot be read directly.

Programming

1. Establishing the connection

SQLConnect is used to establish a connection to a PostgreSQL database via Native Access. The type of database used (PostgreSQL in this case) must be passed in parameter to SQLConnect.

Note: the name of the PostgreSQL data source ("PostgreSQLDatabase" in the example) is the name or the IP address of the computer where the database is available. In any case, the name of the "Database" must be specified.

ConnectionNum = SQLConnect("PostgreSQLDatabase", "", "", "", "PostgreSQL")
// or
// ConnectionNum = SQLConnect("PostgreSQLDatabase", "User", "Password", "MyDatabase", ...
// "PostgreSQL")

// Connection to a remote PostgreSQL database
ConnectionNum = SQLConnect("192.168.1.51", "User", "Password", "test", "PostgreSQL")

Managing the character sets: 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.

2. Running queries

The queries are run by SQLExec. SQLExec expects in parameter the text of the SQL query as well as the name that will be given to this query.

SQLExec("SELECT LASTNAME, FIRSTNAME, EXTENSION, PHOTO FROM CUSTOMER", "QRY1")

3. Retrieving the result

Several methods can be used to retrieve the result:

SQLFirst: Used to retrieve the result and to initialize an SQLFirst/SQLCol browse.
SQLTable: Used to retrieve the result of the query in a memory table of the WINDEV application or WEBDEV site. The result of the query can be retrieved by partial Fetch.
SQLFetch: Used to browse the different lines of query result. The browse performed in the query result is an SQLFetch/SQLGetCol browse.

SQLLock is used to lock either the table records, or the records selected by the query. The time-out (maximum wait) to lock or modify a locked record is infinite by default on a PostgreSQL database. To modify the time-out, we recommend that you use "WD Command Timeout" in the optional information of SQLConnect. In this case, an error will be returned by the database when the time-out is reached.

4. Closing the connection

SQLClose is used to close the connection to the database when all the necessary queries have been run.