PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

  • Overview
  • Main remarks
  • Required versions
  • Unicode on Oracle
  • The lock of a data file (table) is performed in a transaction
  • Managing the automatic identifier in write mode: using an Oracle sequence
  • Format of dates
  • Record number
  • List of tables accessed by Native Oracle Connector according to the SYS diagram
  • Managing synonyms
  • Default values of items
  • Managing the calculated items
  • Main specific features
  • Analysis options
  • Empty strings and NULL value
  • Array items
  • Constraints for referential integrity
  • Locking records
  • Using a query (with the hQueryWithoutCorrection constant) with HAdd, HModify or HDelete
  • Specific features regarding the HFSQL and SQL functions
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Native Oracle Connector: Specific features and remarks
Native AccessesAvailable only with this kind of connection
Overview
The following paragraphs present the main remarks and the specific features of Native Oracle Connector (also called Native Oracle Access).
Main remarks

Required versions

Caution: This version of Native Oracle Connector operates with WINDEV 23 and WEBDEV 23.

Unicode on Oracle

  • To manage Unicode on Oracle, the client layer 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.

The lock of a data file (table) is performed in a transaction

HLockFile starts a transaction and locks the table in Exclusive mode. HUnlockFile validates the transaction and unlocks the table.

Managing the automatic identifier in write mode: using an Oracle sequence

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.

Format of dates

When inserting or updating a date in a query run by SQLExec, the default format for Native Oracle Connector is as follows: "YYYYMMDDHHMMSS".
In WINDEV 5.5, the format of the dates depended on the parameters specified during the connection (default configuration settings for the Oracle client layer).
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. A custom date format can also be specified in this combo box. In this case, this format must be compatible with insert queries (INSERT) and with update queries (UPDATE).
Note: Since version 12, the empty dates correspond to strings competed by zeros. In the earlier versions, the empty dates corresponded to empty strings.

Record number

You cannot specify a record number other than zero in HWrite and HRead. Indeed, "zero" corresponds to the current record number.
Similarly, HLockRecNum and HUnlockRecNum can only be used on the current record.

List of tables accessed by Native Oracle Connector according to the SYS diagram

The list of tables to which Native Oracle Connector tries to access according to the SYS diagram is as follows:
  • V$VERSION
  • ALL_OBJECTS
  • ALL_IND_COLUMNS
  • SYS.ALL_TABLES
  • SYS.ALL_VIEWS
  • Optional: SYS.ALL_SOURCE
By default, these tables are locked in read-only by Oracle. It may be required to allow SELECT on these tables.
Versions 18 and later

Managing synonyms

The Native Oracle Connector manages the synonyms:
  • You have the ability to access a file via its name or via its synonym.
  • The synonyms are listed with the views by HListFile.
  • The synonyms are imported as file description into the analysis.
Caution: HCreation used on a file description found in the analysis always creates a table, regardless of its origin (table, view or synonym).
New in version 18

Managing synonyms

The Native Oracle Connector manages the synonyms:
  • You have the ability to access a file via its name or via its synonym.
  • The synonyms are listed with the views by HListFile.
  • The synonyms are imported as file description into the analysis.
Caution: HCreation used on a file description found in the analysis always creates a table, regardless of its origin (table, view or synonym).

Managing synonyms

The Native Oracle Connector manages the synonyms:
  • You have the ability to access a file via its name or via its synonym.
  • The synonyms are listed with the views by HListFile.
  • The synonyms are imported as file description into the analysis.
Caution: HCreation used on a file description found in the analysis always creates a table, regardless of its origin (table, view or synonym).
Versions 21 and later

Default values of items

The default values of items are managed by Native Oracle Connector. 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.
New in version 21

Default values of items

The default values of items are managed by Native Oracle Connector. 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.

Default values of items

The default values of items are managed by Native Oracle Connector. 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.
Versions 21 and later

Managing the calculated items

Calculated items can be used in the data files.
The management of calculated items is available from Oracle version 11g (and later).
New in version 21

Managing the calculated items

Calculated items can be used in the data files.
The management of calculated items is available from Oracle version 11g (and later).

Managing the calculated items

Calculated items can be used in the data files.
The management of calculated items is available from Oracle version 11g (and later).
Main specific features

Analysis options

  • No matter whether the options ("Case sensitive", "Accent sensitive" and "Punctuation sensitive") are checked in the data model editor, the search will always be sensitive to the case, to the accented characters and to the punctuation.
  • The analysis options regarding the full-text indexes are not supported by Native Oracle Connector.
  • The analysis options regarding the partial indexing of text memos are not supported by Native Oracle Connector.

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 Array items are not supported by Native Oracle Connector. If you are using an array item, only the value of subscript 1 in the array will be handled by the HFSQL functions (in read/write).
To manage this type of item, the structure of your data files must be modified in order to replace the subscripted items by several items.
Note: In programming, you will have the ability to use the indirection to handle these items in a generic way.
The following code with a subscripted item:
nItemSubscript is int
FOR nItemSubscript = 1 TO 10
        File.ArrayItem[nItemSubscript] = "Value"
END
Becomes with items whose name contains a number:
nItemSubscript is int
FOR nItemSubscript = 1 TO 10
{"File.ArrayItem" + nItemSubscript, indItem} = "Value"
END

Constraints for referential integrity

The constraints for referential integrity defined in the analysis are not automatically created on the database by HCreation.
The constraints for referential integrity must be defined in the database accessed.

Locking records

The lock options (hLockWrite and hLockReadWrite constants) that can be used with the HFSQL functions have the same effect: lock in write mode. Indeed, the locks in read-only are not supported by Oracle.

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 is used to avoid the error "ORA2002: Extraction out of sequence" when browsing 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, CUUNIKKEY, S1.CUUNIKKEY)
IF HFound(CUSTOMER) THEN
CUSTOMER.NBORDERS = ORDERS.NBORDERS
HModify(CUSTOMER)
END
HReadNext(S1)
END
SQLTransaction(sqlCommit, MyConnection)
Specific features regarding the HFSQL and SQL functions
The specific features for using the HFSQL functions with Native Oracle Connector are as follows:
Specific featuresAffected 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 the records (including the duplicates) will be browsed.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 (associated 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 values of the record are modified by the database (trigger on Insert, ...), HRead must be used to retrieve the modified values.HAdd
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment