|
|
|
|
|
|
|
|
|
|
- Overview
- Main remarks
- Required versions
- Unicode on Oracle
- Locking data files (tables) in a transaction
- Automatic identifier management in writing: using an Oracle sequence
- Date format
- Record number
- List of tables accessed by the Native Oracle Connector according to the SYS schema
- Managing synonyms
- Default values of items
- Handling calculated items
- Main specific features
- Analysis options
- Empty strings and NULL value
- Array items
- Referential integrity constraints
- Locking records
- Using a query (with the hQueryWithoutCorrection constant) with HAdd, HModify or HDelete
- Specific features regarding HFSQL and SQL functions
Oracle Native Connector: Features and notes
 Available only with this kind of connection
The following paragraphs present the main aspects and specific features of the Native Oracle Connector. Required versions Attention: This version of the Oracle Native Connector works only with WINDEV and WEBDEV 2025. Unicode on Oracle - To manage Unicode on Oracle, the client library version 9i (or later) must be installed.
- If the following conditions are fulfilled, the unicode memo is truncated to 4000 characters:
- Using the selection condition LIKE with the ESCAPE clause,
- Unicode memo,
- Server in version 9.
Locking data files (tables) in a transaction HLockFile starts a transaction and locks the table in Exclusive mode. HUnlockFile validates the transaction and unlocks the table. Automatic identifier management in writing: using an Oracle sequence The Automatic ID type is not available in Oracle. This item type is not created when importing an Oracle table. However, the "4-byte integer" and "8-byte integer" items can be modified in the data model editor in order to be defined 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.
Date format When inserting or modifying a date in a query executed by the SQLExec function, the default format specified for the Oracle Native Connector is: "YYYYMMDDHHmmSS". In WINDEV 5.5, the date format depended on the parameters specified during the connection (default settings of the Oracle client library). In WINDEV, to modify the default format of the dates, display the description window of the connection used and select "Default connection format" in the "Date Format" combo box. You can also specify a custom date format. In this case, this format must be compatible with INSERT and UPDATE statements. Note: Empty dates correspond to strings completed with 0s. Record number You cannot specify a record number other than zero in HWrite and HRead. "Zero" corresponds to the current record number. List of tables accessed by the Native Oracle Connector according to the SYS schema The Native Oracle Connector tries to access the following tables according to the SYS schema: - V$VERSION
- ALL_OBJECTS
- ALL_IND_COLUMNS
- SYS.ALL_TABLES
- SYS.ALL_VIEWS
- Optional: SYS.ALL_SOURCE
By default, Oracle locks these tables in read-only mode. It may be necessary to grant permission to perform SELECT statements on these tables. Managing synonyms The Native Oracle Connector manages synonyms: - You can access a data file directly by its name or synonym.
- HListFile lists synonyms with the views.
- A synonym is imported into the analysis as "Description of a data file".
Warning: the HCreation function used on a data file description present in the analysis always creates a table, whatever its origin (table, view or synonym). Default values of items The Native Oracle Connector supports default item values. The default values can be: - a value,
- an SQL formula. In this case, the SQL expression must be recognized by the HFSQL engine and by the Oracle database.
Handling calculated items Calculated items can be used in the data files. The management of calculated items is available from Oracle version 11g (and later). Analysis options - Whether the "Case sensitive", "Accent sensitive", "Punctuation sensitive" options are checked or not in the data model editor, the search will always be case, accentuation and punctuation sensitive.
- The Native Oracle Connector does not support the full-text index options of the analysis.
- The Native Oracle Connector does not support the options for partial indexing of text memos.
Empty strings and NULL value The "Empty string" values are interpreted by Oracle as the NULL value. When performing an ascending browse, the ORDER BY command rejects all the NULL values at the end of the browse. Array items The Native Oracle Connector does not support Array items. If you are using an array item, only the value of index 1 in the array will be handled by HFSQL functions (read/write). To manage this type of item, the structure of your data files must be modified in order to replace the indexed items by several items.
Remark: In programming, it will be possible to use indirection to manipulate these fields in a generic way.. The following code with an indexed item: nItemIndex is int
FOR nItemIndex = 1 TO 10
DataFile.ItemTable[itemIndex]="Value"
END
Becomes with items whose name contains a number: nItemIndex is int
FOR nItemIndex = 1 TO 10
{"DataFile.ItemTable" + nItemIndex, indItem} = "Value"
END
Referential integrity constraints Referential integrity constraints described in the analysis are not automatically created on the database with HCreation. Referential integrity constraints must be described in the database accessed. Locking records The lock options (constants hLockWrite and hLockWrite) used with HFSQL functions have the same effect: lock in write mode.. Oracle does not support read-only locks. Using a query (with the hQueryWithoutCorrection constant) with HAdd, HModify or HDelete If the hQueryWithoutCorrection constant is used in a query, you must start a transaction before using this query with HAdd, HModify or HDelete. This transaction avoids the "ORA2002: Extraction out of sequence" error when running the query. Example: S1 is Data Source
SQLTransaction(sqlStart, MyConnection)
HExecuteSQLQuery(S1, MyConnection, hQueryWithoutCorrection, "select * from orders")
HReadFirst(S1)
WHILE NOT HOut(S1)
HReadSeekFirst(Customer, CUUniqueKey, S1.CUUniqueKey)
IF HFound(Customer) THEN
Customer.NbOrder = Order.NbOrder
HModify(Customer)
END
HReadNext(S1)
END
SQLTransaction(sqlCommit, MyConnection)
Specific features regarding HFSQL and SQL functions The specifics of using HFSQL functions with the Oracle Native Connector are as follows: | | | Specific features | Affected functions |
|---|
| The hAffectBrowse constant is ignored. At the end of the operation, the browse continues from the record that was the current record before the addition or the modification. | HAdd, HModify | | The hDistinct constant is ignored: all records (including duplicates) are scanned. | HForward, HBackward,
HReadPrevious, HReadNext
HPrevious, HNext | Multi-file query.
HAdd, HModify and HDelete are not supported. | HAdd,
HModify,
HDelete | HSeek*, HFirst and HNext are used to position on a record without reading it.
In this version, these functions also read the record. This problem will be fixed in a forthcoming version. | HSeek,
HFirst,
HNext | If a duplicate error occurs on HModify, HRead (with the hCurrentRecNum constant) does not read the requested record.
This problem will be fixed in a forthcoming version. | HRead | | After HAdd, the record is not re-read by the Native Connector. If the database modifies the values of the record, use HRead to retrieve the new values. | HAdd |
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|