Oracle Native Connector: Programming with HFSQL functions

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / Native Connectors/Native Accesses / Native Oracle Connector

Overview
Importing the structure of data files
The import steps
Special case: automatic identifier management
Taking into account the evolution of the Oracle database
Programming
1. Establishing the connection
2. Running queries
3. Retrieving the result
4. Closing the connection
Remarks

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Native Oracle Connector for WINDEV and WEBDEV

Oracle Native Connector: Programming with HFSQL functions

Available only with this kind of connection

Overview

To use an Oracle database with the Native Connector (also called Native Access), it is necessary to import the Oracle table structure into the WINDEV or WEBDEV analysis.

Differences from SQL programming: To manipulate Oracle data, queries are not mandatory. In fact, you can use HFSQL functions directly to read your data, perform searches, filters and so on. However, you will need to import the structure of your Oracle data files into WINDEV or WEBDEV.

Importing the structure of data files

The import steps

To perform this import:

Launch the data model editor: click on among the quick access buttons.
On the "Analysis" tab, in the "Creation" group, expand "Import" and select "Import file/table descriptions...".
The wizard starts. A connection to the Oracle database is automatically established and it is associated with the data files imported into the analysis. This connection will have to be used to handle the Oracle database programmatically.

In the different steps of the wizard, choose the following options:

Database type: Oracle.
Connection features: Data server, User and password, Database.
The application will access the data in the current format.
When the connection is established, all you have to do is select the tables that will be imported into the WINDEV or WEBDEV analysis. These tables can be handled through programming with the HFSQL functions of WLanguage. These tables will be displayed in blue in the data model editor.

Note: Managing synonyms

If the Oracle database contains synonyms, these ones will be imported into the data model editor as a new file description.

Caution: Whatever the origin of the file description (table, view or synonym), function HCreation always creates a table..

Special case: automatic identifier management

The Automatic ID type is not available in Oracle. This item type is not created when importing an Oracle table.

However, in the data model editor, it is possible to modify "Integer over 4" and/or "Integer over 8" type fields to define them as automatic identifiers.. In this case, these automatic identifiers will be managed by the Native Oracle Connector (when adding or updating records) via an Oracle "Sequence" named "WINDEV_SEQ". This sequence is automatically created by the Native Oracle Connector if it does not exist.

To add or modify records with automatic identifiers, you must:

modify the analysis. "4-byte integer" and "8-byte integer" items can be defined as "Automatic identifiers". In this case, the Native Oracle Connector will manage these items as automatic identifiers.
create a "sequence" named "WINDEV_SEQ" in the Oracle database
grant the "CREATE SEQUENCE" privilege to the user.

Taking into account the evolution of the Oracle database

To take into account the evolutions of the tables imported into the data model editor, go to the "Analysis" tab, "Analysis" group, expand "Synchronization" and select "Update analysis from external databases".

A wizard starts, allowing you to:

analyze the differences for the imported tables,
analyze the differences for all the Oracle tables.

Programming

1. Establishing the connection

To establish the connection to the Oracle database, use HOpenConnection and specify the name of the connection to open. The connection name was defined when importing the structure of files into the analysis.

To modify some parameters of this connection (username or password for example), call HConnect.

Remarks:

If you try to read the file directly (HReadFirst, ...), the connection associated with the file description in the analysis will be automatically opened.
You have the ability to perform an external authentication of the connection.

2. Running queries

HExecuteQuery and HExecuteSQLQuery are used to run queries on the current database.

HExecuteQuery: is used to run a preset query with the query editor.
HExecuteSQLQuery: is used to execute a query by specifying the SQL code of the query and the name that will be assigned to this SQL code.

// Initialisation de la requête "REQ_Client_84"
HExecuteQuery(REQ_CLIENT_84, "BaseOracle", hQueryWithoutCorrection)

HExecuteSQLQuery(REQ_RequeteClient, "BaseOracle", hQueryWithoutCorrection, ...
		"SELECT NOM FROM CLIENT")

3. Retrieving the result

The result is browsed using all the classic read functions: HReadFirst, HReadNext, HReadSeek, ...

The records can be locked via the lock options of these functions.

Note: The hLockWrite and hLockRead constants have the same effect: lock in write mode.. Oracle does not support read-only locks.

4. Closing the connection

HCloseConnection is used to close the connection to the database once all the necessary queries have been run.

Remarks

"Table file" fields based on queries are optimized: you can sort the contents of a Table field by clicking on one of its columns.
To avoid running the same query several times when reading the result, it is recommended to use the hNoRefresh constant (if the data is modified on a single computer, for example).