- Importing the structure of data files
- The import steps
- Special case: Managing the automatic identifier
- Taking into account the evolution of the Oracle database
- 1. Establishing the connection
- 2. Running queries
- 3. Retrieving the result
- 4. Closing the connection
Native Oracle Connector: Programming with HFSQL functions
Available only with this kind of connection
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 compared to SQL programming: The queries are not required to handle the Oracle data. Indeed, the HFSQL functions can be directly used to read data, perform searches, implement filters, ... However, the structure of Oracle data files must be imported into the WINDEV or WEBDEV analysis.
Importing the structure of data files
The import steps
To perform this import:
- Start the data model editor: click in 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 through programming.
In the different steps of the wizard, choose the following options:
- Type of database: Oracle.
- Characteristics of the connection: 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.
Remark: Managing the synonyms
If the Oracle database contains synonyms, these ones will be imported into the data model editor as a new file description.
Caution: Regardless of the origin of the file description (table, view or synonym), HCreation
always creates a table.
Special case: Managing the automatic identifier
The Automatic Identifier type is not available for Oracle: this type of item is not created when importing an Oracle table.
However, the "4-byte integer" item and/or "8-byte integer" item can be modified in the data model editor in order to be defined as automatic identifier. In this case, these automatic identifiers will be managed by the Native Oracle connector (in addition or in modification) via an Oracle "Sequence" named "WINDEV_SEQ". This sequence is automatically created by Native Oracle Connector if it does not exist.
To add or modify records with automatic identifier, you must:
- modify the analysis. Indeed, a "4-byte integer" item or a "8-byte integer" item can be defined as "Automatic identifier". In this case, this item will be managed like an automatic identifier by Native Oracle Connector.
- create the "sequence" object 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.
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
- 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: 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.
// Initialize the "QRY_Customer_84" query
HExecuteQuery(QRY_CUSTOMER_84, "OracleDatabase", hQueryWithoutCorrection)
HExecuteSQLQuery(QRY_CustomerQuery, "OracleDatabase", hQueryWithoutCorrection, ...
"SELECT NAME FROM CUSTOMER")
3. Retrieving the result
The records can be locked via the lock options of these functions.
Remark: The hLockWrite and hLockReadWrite constants have the same effect: lock in write mode. Oracle does not support read-only locks.
4. Closing the connection
is used to close the connection to the database once all the necessary queries have been run.
- The "Browsing table" controls based on queries are optimized: the content of the Table control can be sorted by clicking 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).
This page is also available for…