Help / Native Connectors/Native Accesses / Native SQL Server Connector
  • Overview
  • Specific features of the Native SQL Server Connector
  • Table control based on a data file loaded in memory
  • Locking a data file (table) in a transaction
  • Record number
  • Retrieving the DATETIME types
  • Analysis options
  • Array items
  • Encryption via SSL
  • Stored procedures
  • Default values of items
  • Specific features for the HFSQL functions
  • Operating problems
  • Special case: SQL Azure
  • Handling calculated items
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WindowsLinuxPHPWEBDEV - Browser code
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Stored procedures
Native SQL Server Connector: Specific features and remarks
The following paragraphs present the main aspects and specific features of the Native SQL Server Connector.
Specific features of the Native SQL Server Connector

Table control based on a data file loaded in memory

The new records added into a Table control based on a data file loaded in memory cannot be modified straightaway. To modify these new records directly in the Table control, you must use TableDisplay with the taInit constant after the additions.

Locking a data file (table) in a transaction

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

Record number

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

Retrieving the DATETIME types

  • The precision of values read and written is limited to the WLanguage precision: the millisecond.
    Special case: The datetime type: The precision is limited by SQLServer to 0.00333 second.
  • The new SQLServer 2008 date and time types are supported only from SQLnCLI for SQL Server 2008.. For earlier client layers, these items are considered as "unicode text" items.

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 analysis options regarding the full-text indexes are not supported by the Native Connector.
  • The analysis options regarding the partial indexing of text memos are not supported by the Native Connector.

Array items

The array items are not supported by the Native Connector. If you are using an array item, only the value of index 1 in the array will be handled by HFSQL functions (read/write).

Encryption via SSL

The encryption by SSL (Secure Socket Layer) is not supported in this version.

Stored procedures

Limit: HPrepareSQLQuery cannot be used to prepare a stored procedure that returns:
  • records.
  • columns.
You must use HExecuteSQLQuery.

Default values of items

The default values of items are managed by the Native SQL Server 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 SQL Server database.

Specific features for the HFSQL functions

The specific features for using the HFSQL functions with Native SQL Server Connector are as follows:
Specific featuresAffected functions
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
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.
The hForceIDAuto constant is ignored by HModify. An "IDENTITY" column cannot be modified in SQL Server.HModify
The lock options of the read functions (HReadSeek, etc.) are ignored.Read functions (HReadFirst, etc.)
A record locked by HLockRecNum is unlocked when it is read again.Read functions (HReadFirst, etc.)
HCreation creates DATE and TIME items if the version of the server and the client layer correspond to SQL Server 2008 (or later).HCreation

Operating problems

When using the Native SQL Server Connector, some features such as Drag and Drop, ActiveX or .NET may not work.
To restore a correct operating mode, the following lines of code should be added into the project initialization code:
API("ole32", "CoInitializeEx", 0, 2)

Special case: SQL Azure

SQL Azure requires the presence of a "clustered index" in the files.
  • If the data files are created by HCreation, the Native Connector automatically creates the "clustered index" if the description of the data file contains an automatic identifier.
  • If you create the data files with SQL queries (or to add a "clustered index" to an existing data file), see the documentation of SQL Azure to find out the syntax to use.

Handling calculated items

Calculated items can be used in the data files.
The management of calculated items is available in SQL Server 2005 (or later) with a Native SQL Server Connector via OLE DB or ODBC.
Minimum version required
  • Version 9
This page is also available for…
Click [Add] to post a comment

Last update: 03/28/2023

Send a report | Local help