PC SOFT

WINDEV, WEBDEV AND WINDEV MOBILE
ONLINE HELP

Home | Sign in | English UK
  • Overview
  • Specific features common to Native SQL Server Connector via SQLnCli and via DB-Library
  • Browsing Table control 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
  • Additional specific features of Native Connector via DB-Library
  • Managing UNICODE items
  • Transactions
  • Stored procedures
  • varchar(max), nvarchar(max) type
  • Additional specific features of Native Connector via SQLnCli
  • Operating problems
  • Special case: SQL Azure
  • Managing calculated items
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 SQL Server Connector: Specific features and remarks
Overview
The following paragraphs present the main remarks and the specific features of Native SQL Server Connector:
Specific features common to Native SQL Server Connector via SQLnCli and via DB-Library

Browsing Table control loaded in memory

  • Adding records into a browsing Table control loaded in memory can only be done via the option for cascading input in the Table controls. TableSave cannot be used.
  • The new records added into a browsing Table control loaded in memory cannot be modified straightaway. To modify these new records in the Table control directly, you must use TableDisplay associated with the taInit constant after the additions.
The new records added into a browsing Table control loaded in memory cannot be modified straightaway. To modify these new records in the Table control directly, you must use TableDisplay associated 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. Indeed, "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 only supported by the client layer SQLnCLI for SQL Server 2008. For the earlier client layers, these items are considered as being "text" items (Native Connector via DB-Library) or "Unicode text" items (Native Connector via SQLnCLI).

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 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 subscript 1 in the array will be handled by the HFSQL functions (in 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.
Versions 21 and later

Default values of items

The default values of items are managed for the different Native SQL Server Connectors (SQL Server via SQLnCli by OLE DB or ODBC and SQL Server via DB-Library). 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.
New in version 21

Default values of items

The default values of items are managed for the different Native SQL Server Connectors (SQL Server via SQLnCli by OLE DB or ODBC and SQL Server via DB-Library). 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.

Default values of items

The default values of items are managed for the different Native SQL Server Connectors (SQL Server via SQLnCli by OLE DB or ODBC and SQL Server via DB-Library). 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
HDelete
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
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 server version and the version of the client layer correspond to SQLServer 2008 (or later).HCreation
Additional specific features of Native Connector via DB-Library

Managing UNICODE items

  • The Unicode memos are truncated to 4000 characters when reading the value on the database.
  • The type of the item in the analysis must correspond to the type defined in the SQL Server database:
    • A Unicode item in the analysis must correspond to a Unicode item in the database.
    • An ANSI item in the analysis must correspond to an ANSI item in the database.
    • If the types do not correspond:
      • For the memos: if the ANSI memo of the analysis is associated with a Unicode item in the database, the Native Connector systematically asks the SQL Server server to convert the item into ANSI text memo. This conversion is performed by the SQL Server server and ChangeCharset is ignored.
      • Query that is using a (non-memo) ANSI item in the analysis and a Unicode item in the database: An SQL Server error occurs: No Unicode data can be returned to DB_Library.
      • Query that is using an Unicode item in the analysis and an ANSI item in the database: The conversion to Unicode is performed by the SQL Server server and ChangeCharset is ignored.
  • The Unicode format is not supported by the "without correction" queries. Any access to a Unicode item will trigger an SQL Server error ("Unable to return a Unicode data to DB_Library"). However, CONVERT can be used in the query to ask the SQL Server server to perform a conversion into ANSI. For example:
    CONVERT(TEXT, MyUnicodeItem)

    or
    CONVERT(VARCHAR(4000), MyUnicodeItem)
  • In the SQL queries, the Unicode format is not managed in the queries containing WLangage commands and/or SQL functions (LEFT, CONCAT, ...). For example, the following query triggers an error:
    SELECT LEFT(MyUnicodeItem, 2) FROM MyFile

Transactions

In a transaction, running an update query (INSERT, UPDATE or DELETE) or any query with the hQueryWithoutCorrection constant closes the browse of the previous query (if the previous query was run with the hQueryWithoutCorrection constant).
This limitation is due to the client layer: a single DBPROCESS is used by the transaction, which only allows for one execution context of the query.
Solutions:
  1. Use the Native Connector via SQLnCli.
  2. Solution with the Native Connector via DB-Library: Don't use the hQueryWithoutCorrection constant when running the first query (SELECT). The Native Connector will run the query in a context "external to the transaction". Then, the update query can be run in the context of the transaction without having to close the browse.

Stored procedures

The Native SQL Server Connector via DB-Library does not support the stored procedures that return a "resultset" (result of SELECT).
To run a stored procedure, this stored procedure must have been created on the server beforehand. Then, you have the ability to run it by specifying its name. You cannot create a new stored procedure with HPrepareSQLQuery.

varchar(max), nvarchar(max) type

The Native SQL Server Connector via DB-Library does not support the varchar(max) and nvarchar(max) types. Therefore, using a table with this type of data requires a connection with the Native SQL Server Connector via SQLnCli.
Additional specific features of Native Connector via SQLnCli

Operating problems

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

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.
New in version 16

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.

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.
Versions 21 and later

Managing calculated items

Calculated items can be used in the data files.
The management of calculated items is available with SQL Server 2005 (or later) with a Native SQL Server Connector via SQLnCli by OLE DB or ODBC.
New in version 21

Managing calculated items

Calculated items can be used in the data files.
The management of calculated items is available with SQL Server 2005 (or later) with a Native SQL Server Connector via SQLnCli by OLE DB or ODBC.

Managing calculated items

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