ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / HFSQL functions
  • Use conditions
  • Automatic reindexing
  • "Zero-downtime" reindexing
  • Calculating statistics
  • Directory of temporary files
  • Managing non-Latin character sets
  • Access rights to the data file
  • Special case: Files corrupted after a computer reboot or a fatal error
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Rebuilds the file index (".NDX" file). You can also reindex:
  • full-text indexes (".FTX" files),
  • spatial indexes (".SDX" files).
A data file can be reindexed even when it is accessed by applications. For more details, see remarks.
Reminder: Reindexing reorganizes indexes and can free up space left when records are deleted. After the reindex operation, the access to indexes is optimized. Reindexing a data file can become necessary further to the loss or modification of the index file. <Source>.CheckIndex is used to regularly check the status of the index file.
Example
IF AFile.Index(hNdxNormal) = True THEN
	TABLE_MyTable.Display(taStart)
END
Syntax
<Result> = <Source>.Index([<Mode> [, <Progress Bar> [, <Density rate>]]])
<Result>: Boolean
  • True if the reindex operation was successfully performed,
  • False otherwise. HError returns more details about the problem.
<Source>: Type corresponding to the specified source
Name of the data file used.
<Mode>: Optional Integer constant (or combination of constants)
Configure the reindex operation:
hChangeCharsetReindexing with change of character set.
The new character set defined by ChangeCharset will be taken into account during the reindexing. Sorts, searches, ... on string keys (strings, characters, date and time) will be performed according to this character set.
hFTXReindexing of full-text indexes.
hMmoCompactCompression and defragmentation of "Mmo" files.

Caution: This constant cannot be combined with the hNdxCompact, hCheckMemo, hFTX, hSDX, hNdxNormal or hNdxDelete constants.
hNdxCompactReindexing with compression: only active records are kept. All the other records are permanently deleted. The space left by the deleted records is deleted from the data file and the index (".FIC" and ".NDX" files, respectively).
Remark: If one or more records are in a transaction, compression is automatically disabled.
hNdxMinimalReindexing while taking into account only corrupted keys. This parameter is ignored if the hNdxDelete or hNdxCompact constant is enabled.
hNdxSilentReindexing without display.
hNdxNormal
(default value)
Standard reindexing (with statistics and without compression).
The statistics are used to optimize the queries and the filters (see the Notes). Only active records are taken into account for calculating statistics.
hNdxNoStatReindex without calculating statistics. This constant increases the available space by reducing the size of index files.
Caution: The statistics are very important to get correct execution times when using queries, filters with condition and views with condition.
hNdxDeleteReindex and delete damaged records.
hSDXReindexing of spatial indexes.
hBackgroundTask
HFSQL Client/Server Reindexing performed in background task. The applications are not locked during the reindex operation.
This feature is available in Client/Server mode only.
hCheckMemoStandard reindexing with memo check and correction if necessary (used to retrieve the damaged memos).
<Progress Bar>: Optional control or window name, or optional integer
  • Name of the window in which the progress bar will be displayed,
  • Name of the Progress Bar control that displays the reindexing progress,
  • Handle of the window that displays the progress bar.
<Density rate>: Integer included between 1 and 100
Density rate of indexes. This rate is set to 60 by default.
Remarks

Use conditions

  • <Source>.Index reindexes an xBase file.
  • <Source>.Index reindexes the files of a cluster. In this case:
    • The reindex operation is performed on the node to which the user is connected.
    • Only a zero-downtime reindexing is available (hBackgroundTask constant).
Universal Windows 10 AppiPhone/iPadHFSQL ClassicHFSQL Client/Server

Automatic reindexing

Reindexing a data file can become necessary further to the loss or alteration of index file. An automatic reindex operation can be implemented for the data files.
To implement an automatic reindex operation, you must:
  • configure the value of the H.AutoIndexingLimit variable.
    Indeed, if the data file contains less than H.AutoIndexingLimit records, the reindex operation is automatically performed if an index error occurs.
    The H.AutoIndexingLimit variable is set to 10000 by default. The automatic reindex operation is disabled if this variable is set to 0.
  • configure the value of the H.VisibleAutoIndexing variable.
    If this value is set to True (default value), a window is automatically displayed as long as the automatic reindex operation is not completed. This window contains a progress bar used to follow the progress of the current reindexing. This window can be customized. For more details, see HFSQL error handling help.
    During the reindex operation, the different applications that use the file data (read or write records) will be temporarily stopped and/or locked until the end of the reindex operation.
    HFSQL Client/Server If the hBackgroundTask constant is used, the reindex operation is performed in background task and the applications that use the data file are not stopped.
If an error occurs during the automatic reindex operation (lock error on the data file or on the record for example), the automatic reindex operation is stopped and a fatal error (corrupted index) is displayed. This operating mode is similar to the one without automatic reindexing.
Remarks:
  • During the automatic reindexing, the reindex operation is performed in normal mode (hNdxNormal constant).
  • No automatic reindexing is available for data files in Hyper File 5.5 format.
  • If an automatic reindexing is performed, the applications that use the data files currently reindexed are temporarily locked. A message is displayed, indicating that the file is currently maintained.
  • At the end of the reindex operation, the current HyperFileSQL context is automatically restored (except if a reindex operation with memo check was performed).
Universal Windows 10 AppAndroidAndroid Widget iPhone/iPadHFSQL ClassicHFSQL Client/Server

"Zero-downtime" reindexing

A data file can be reindexed even when it is accessed by applications.
During the reindex operation, the different applications that use the file data (reading or writing records) will be temporarily stopped/locked until the end of the reindex operation.

Calculating statistics

Statistics are used to optimize the queries and filters on the data file. Only active records are taken into account for these statistics. <Source>.StatCalculate also returns the results of this calculation.
Remark: To efficiently calculate statistics, all data files must be included. This is the case of a multi-file query, for example.

Directory of temporary files

Temporary files can be created during the reindex operation. By default, these files are created in the Windows temporary directory or in the current directory if there is not enough space.
The H.TemporaryDirectory variable allows you to configure the directory used to save these temporary files. This variable is recommended when reindexing large data files.
HFSQL Client/Server The directory used to store the temporary files on the server can be defined:

Managing non-Latin character sets

To modify the character set used for the searches, the sorts:
  1. Specify the character set to use with ChangeCharset.
  2. Reindex the data file with <Source>.Index. Use the hChangeCharset constant to use the character set specified by ChangeCharset.
For more details, see Managing specific character sets in the data files.

Access rights to the data file

The reindex operation provokes the recreation of the data file on disk. In an allocation system that manages permissions at file level (NTFS, etc.), the reindexed data file will have the permissions of the directory to which it belongs.
Before the reindex operation, if the data file had specific rights different from the rights of the folder, they must be redefined in Windows after the reindex operation.

Special case: Files corrupted after a computer reboot or a fatal error

The data files may be corrupted when the computer is unexpectedly rebooted. In this case, the data files contain invalid data (unreadable characters for example). To fix this problem:
  1. Perform a reindexing with deletion.
  2. Free the records in transaction with <Source>.TransactionFree.
  3. Delete the incorrect records with WDMAP.
Component: wd300hf.dll
Minimum version required
  • Version 25
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 04/11/2024

Send a report | Local help