ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / HFSQL functions
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
Remark: These functions are also available in prefix syntax (HFSQL functions (prefix syntax)).
The following functions are used to manage HFSQL data files:
ConvertConverts a numeric value into a binary string in order to perform a search on a numeric key.
HAccelerateSpeedReorganizes the internal structure of the indexes to optimize the speed for accessing the data.
HActivateAutoFilterEnables an automatic filter on the linked files when browsing an XML file.
HActivateFilterEnables the filter that was previously created for the specified data file (view or query).
HActivateTriggerRe-enables a trigger that was disabled by HDeactivateTrigger.
HAddAdds:
  • the record found in memory into the data file (query or view).
  • the record found in a Record variable into the data file (query or view).
HAliasCreates a logical alias of a data file (or query) or cancels all existing aliases.
HBackwardMoves backward several records from the current position in the data file, according to a specified item.
HBuildKeyValueBuilds the value of a composite key to implement a filter (HFilter) or to perform a search (HReadSeekFirst, HReadSeekLast, etc.).
HBuildKeyValueANSIRegardless of the platform used, the data stored in the HFSQL files is in ANSI format.
HCancelAliasCancels an alias that was declared beforehand by HAlias.
HCancelDeclarationUsed to:
HCancelSeekCancels the current search criterion.
HChangeConnectionDynamically changes the connection associated with a data file.
HChangeDirChanges the path to a data file (i.e. directory in which the file will be handled).
HChangeKeyChanges the browse item.
HChangeLocationModifies the search mode of physical data files (.FIC, .NDX, ...).
HChangeLogDirModifies the location of log files corresponding to an HFSQL data file.
HChangeNameModifies the physical name of a data file.
HChangeRplDirChanges the location of the subscriber replica description ("*.rpl" file).
HCheckIndexChecks whether the data found in the index file (.NDX file) properly refers the data found in the data file (.FIC file).
HCheckMemoChecks the structural integrity of memos in an HFSQL data file.
HCheckStructureDefines the mode for comparing data files.
HCheckUnalteredChecks whether one or all records in an unalterable data file have not been modified (with an hexadecimal editor for example).
HCloseCloses a data file or all the data files opened by the current user: all the corresponding physical data files are closed for the current user.
HCloseAnalysisCloses the current analysis.
HCloseConnectionCloses a connection to a database.
HCompareItemCompares two values by sorting them according to the specified HFSQL index item: all the sort options specified for the item are taken into account (sensitivity to the case, to the punctuation, sort direction, Unicode language, ...).
HComputerStores a unique computer number or identifier in order to use the log and transactions over the network.
HConnectRedefines one or more connection parameters via Native Connector (Access, SQL Server, MySQL or Oracle) or via an OLE DB access on a specific table or set of tables.
HConvertXConverts a numeric value into a binary string in order to perform a search on a numeric key.
HCopyRecordCopies:
  • the content of the current record (loaded in memory) to the current data file record.
  • the content of the Record variable to the current data file record.
HCreateCreates an empty data file (".FIC" extension) with the index file and the memo file if necessary.
HCreateIfNotFoundPerforms the following operations:
  • If the file does not exist, creates an empty data file (".FIC" file) with the index file and the memo file if necessary. The function is equivalent to HCreate.
  • If the data file exists, opens the data file.
HCreateMasterReplicaCreates the description file of a Master Replica (logged replication or universal replication).
HCreateMovableReplicaCreates a movable replication.
HCreateSubscriberReplicaCreates the description file of a subscriber replica.
HCreateViewCreates an HFSQL view.
This function uses the former view mechanism.
HCrossCrosses a record in a data file.
HDataSourceKnownDetermines if a data source has been initialized.
HDBCreationEnds the description of the structure of an xBase data file programmatically.
HDBDescribeFieldDescribes through programming each item found in the structure of an xBase file described by HDBDescribeFile.
HDBDescribeFileDescribes a file in dBase3 format (most common format) programmatically.
HDBDescribeIndexDescribes through programming the different index files that will be created.
HDBIndexOpens an xBase index file.
HDBOpenOpens the xBase data file and the "memo" file if it exists.
HDBOpenNoLockIn single-user mode, opens an xBase data file without locking it.
HDBSortTypeReturns or modifies the sequence of text items in the xBase files.
HDeactivateAutoFilterDisables an automatic filter on the linked files when browsing an XML file.
HDeactivateFilterTemporarily disables the filter on a data file (view or query).
HDeactivateTriggerDisables a trigger.
HDeclareDeclares a description of data file (found in an analysis) in the current project.
HDeclareExternalTemporarily imports into the current analysis the description of a data file from an existing HFSQL data file.
HDeleteDeletes a record from a data file (query or view).
HDeleteAllDeletes all records from a data file, an HFSQL view or a query.
HDeleteTriggerDeletes a trigger.
HDeleteViewDeletes a previously created view. This function uses the former view mechanism.
HDescribeConnectionDescribes a new connection to an external database.
HDescribeFileDescribes a data file programmatically.
HDescribeFullTextIndexDescribes a full-text index of data file created through programming.
HDescribeItemDescribes a file item programmatically.
HDescribeLinkDescribes a link between two data files through programming.
HDescribeTriggerAdds or modifies a trigger on an HFSQL data file.
HDuplicateRecordDuplicates the record read in a data file: the record found in memory is added into the data file (query or view).
HEndNoModifUnlocks a file that was locked by the same program with HNoModif.
HErrorReturns the number of the last error triggered by the HFSQL engine.
HErrorDuplicatesUsed to check whether a duplicate error occurred.
HErrorInfoReturns a detailed information about the last error triggered by the HFSQL engine.
HErrorIntegrityUsed to check whether an integrity error occurred.
HErrorLockUsed to check whether a lock error occurred.
HErrorModificationUsed to:
  • return the value of a record item if a modification conflict occurs.
  • check if a modification conflict occurred.
HErrorPasswordAfter the execution of a read or write function in a data file, used to find out whether an error caused by a wrong password occurred on this data file.
HErrorStatusModificationReturns the status of a record during a modification conflict.
HExecuteQueryDeclares a query created in the query editor to the HFSQL engine and runs this query.
HExecuteQueryAsynchronousExecutes a SELECT query asynchronously.
HExecuteSQLQueryInitializes a query written in SQL language and declares this query to the HFSQL engine.
HExecuteSQLQueryAsynchronousExecutes an SQL query asynchronously.
HExecuteViewRuns a view that was created beforehand (can be used to refresh the view data for example). This function uses the former view mechanism.
HExportCSVExports the records found in a data file (HFSQL or OLE DB), a view or a query to a CSV file.
HExportJSONExports the records found in a data file (HFSQL or OLE DB), view or query to a JSON file.
HExportJSONStringExports the records from a data file (HFSQL or OLE DB), view or query to a JSON string.
HExportXLSExports records from a data file (HFSQL or OLE DB), view or query to an Excel file (XLSX).
HExportXMLExports the records found in a data file (HFSQL or OLE DB), view or query to an XML file.
HExtractMemoExtracts the content of a binary memo item from an HFSQL file, a query or an HFSQL view to a physical file (on disk).
HFileExistDetermines:
  • whether a file exists, which means whether it was physically created (HCreation or HCreationIfNotFound).
  • whether a view or a query was defined.
HFilterDefines and enables a filter on a data file, view or query.
HFilterContainsDefines and enables a "Contains" filter on a data file, view or query.
HFilterIdenticalDefines and enables a filter used to find the exact value of a string item.
HFilterIncludedBetweenDefines and enables an "Included between" filter on a file, view or query.
HFilterStartsWithDefines and enables a "Start with" filter on a file, view or query.
HFirstSets the position on the first record of a data file according to the specified browse item.
HFlushForces the operating system of the computer where the data files are found to write data onto the disk.
HForwardMoves several records forward from the current position in the data file, according to a specified item.
HFoundChecks whether the current record corresponds to the current filter or search.
HFreeTransforms the crossed records (HCross) into deleted records.
HFreePositionDeletes a position saved by HSavePosition.
HFreeQueryFree the resources of a query (after the call to HExecuteQuery or HExecuteSQLQuery).
HFTListWordLists the words of the current record that will be taken into account by the full-text indexing.
HGetCurrentPositionReturns the approximate position of current record in the data file.
HHistoryModificationReturns the modifications performed on one or more items of a given record. The result can be displayed in a list box or in a table to allow the user to view the modifications made to the specified file.
HImportHF55Imports a Hyper File 5.5 file into an HFSQL Classic file.
HImportJSONImports a JSON file into a data file in HFSQL Classic format (data file described in the data model editor or declared by HDeclare, HDeclareExternal or HDescribeFile).
HImportTextImports a Text file into a data file in HFSQL Classic or Client/Server format (data file described in the data model editor, or declared by HDeclare, HDeclareExternal or HDescribeFile).
HImportXLSImports an XLS file into a data file in HFSQL Classic or Client/Server format (data file described in the data model editor, or declared by HDeclare, HDeclareExternal or HDescribeFile).
HImportXMLImports an XML file into an HFSQL Classic or Client/Server data file (data file described in the data model editor or declared by HDeclare, HDeclareExternal or HDescribeFile).
HIndexRebuilds the file index (".NDX" file).
HIndexingInProgressIndicates that a reindex operation is currently performed on an HFSQL data file and returns the percentage of reindexing already performed.
HInfoAnalysisReturns information about an analysis (WDD file).
HInfoFileReturns the characteristics of an HFSQL Classic or Client/Server data file.
HInfoMemoReturns the characteristics of binary and text memos.
HInfoReplicaReturns information about the specified replica.
HInitHF55Used to access specific functions for managing xBase (or dBase) data if Native xBase Access provided with WINDEV or WEBDEV 14 is not available.
HInitSubscriberInitializes the range of automatic identifiers for the description file of subscriber replica (".rpl" extension).
HJSONToRecordImports JSON data into the current record.
HLastSets the position on the last record of a data file according to a browse item.
HLinkMemoUsed to associate a file with a binary memo item or to cancel the existing link between a file and a binary item.
HListAnalysisReturns the list of WINDEV or WEBDEV analyses (in HFSQL Classic format) available in a given directory.
HListConnectionReturns the list of connections currently described in the application.
HListCustomFolderReturns a list of Custom-Folders (also called groups) defined by the user.:
  • in the current analysis,
  • in current analyses (case of projects with multiple analyses).
  • in the specified analysis.
HListFileReturns the list of files:
  • analysis in progress.
  • of analyses in progress (case of projects with multiple analyses).
  • specific analysis recognized by the HFSQL engine.
  • available on an Native Connector or OLE DB driver connection.
  • for a group of files defined in the data model editor.
HListFullTextIndexReturns the list of full-text indexes of a file (a query or a view) recognized by the HFSQL engine.
HListItemReturns the list of items:
  • found in a data file ( a query or a view) known to the HFSQL engine. The data files defined by HDeclare, HDeclareExternal and HDescribeFile are taken into account.
  • found in a Record variable.
HListKeyReturns the list of keys found in a data file (query or view) recognized by the HFSQL engine.
HListLinkReturns the list of a data file links (Merise type) present:
  • in the current analysis,
  • in current analyses (case of projects with multiple analyses).
  • in a specific analysis.
HListProviderReturns the list of OLE DB providers and/or Native Connectors installed on the current computer.
HListQueryParameterReturns the list of parameters for a query created in the query editor.
HListREPReturns the list of assignments for the data files handled by the current application, which means the list of physical files corresponding to the files described in the analysis.
HListSpatialKeysReturns the spatial keys of a data file (used in a query or view) recognized by the HFSQL engine.
HListStopWordReturns the list of stop words used by a full-text index.
HListSynonymReturns the list of synonyms used by a full-text index.
HListTriggerReturns the list of triggers applied to one or more HFSQL data files.
HLockFileLocks a data file and restricts the access to this data file for all other sites or applications.
HLockRecNumLocks a record and restricts the access to this record for all the other applications.
HLogInfoAdds comments to the log when saving the operation.
HLogRecreateUsed to re-create an empty log.
HLogRestartRestarts the logging process on a file.
HLogStopStops the log process of a file.
HMergeViewCreates an HFSQL view from two previously created views (HCreateView). This function uses the former view mechanism.
HMigrateLinkedCompositeKeyMigrates the values of the linked composite keys coming from a file in Hyper File 5.5 format to the HFSQL Classic format.
HModeChanges the mode and the method for locking data files.
HModifyModifies the specified record or the record found in memory in the data file (query or view).
HModifyStructureUsed to update the structure of an HFSQL data file by performing an automatic data modification (also called data synchronization).
HNbRecReturns the number of records in a file, query or HFSQL view: active records, crossed records, deleted records, etc.
HNextSets the position on the next data file record according to a browse item.
HNoModifForbids all the modifications on a data file (for all the programs, including the one that requested the no-modification policy).
HOnErrorCustomizes the management of HFSQL errors.
HOpenOpens a data file.
HOpenAnalysisOpens an analysis in HFSQL Classic format.
HOpenComplementaryAnalysisOpens a new analysis, but keeps the main analysis of the application.
HOpenConnectionOpens a connection to a specific database.
HOptimizeUsed to handle the idle periods of a software (period without processes) to optimize the queries and the browses that will be run thereafter.
HOptimizeQueryOptimizes the Select queries by using idle times when handling an application (period without processes).
HOutUsed to find out whether the record on which you want to be positioned is located outside the data file, filter, view or query.
HPassDefines the password used to create or open a data file.
HPrepareQueryInitializes a query and declares this query to the database server in order to optimize the next executions of this query.
HPrepareSQLQueryInitializes a query written in SQL language and declares this query to the database server in order to optimize the next executions of this query.
HPreviousPositions on the previous file record according to a browse item.
HQueryExecutedDetermines if a query has been executed.
HReadReads a record in a file according to a given record number.
HReadFirstSets the position on the first record according to a browse item.
HReadLastSets the position on the last record of a data file according to a browse item.
HReadNextSets the position on the next data file record according to a browse item.
HReadPreviousSets the position on the previous record of a file according to a browse item.
HReadSeekSets the position on the first record whose value for a specific item is greater than or equal to a sought value (generic search).
HReadSeekFirstSets the position on the first record whose value for a specific item is strictly equal to a sought value (exact-match search).
HReadSeekLastSets the position on the last record whose value for a specific item is less than or equal to a sought value (exact-match search).
HRecNumReturns:
  • the number of the current record in the HFSQL data file.
  • the number of the current record in the HFSQL view.
HRecordDateReturns the date and time of the last write operation performed on a record in an HFSQL file (Classic or Client/Server).
HRecordToJSONRetrieves the structure and value of the current record and exports them into a string in JASON format.
HRecordToStringConverts the content of the current record to string (in a data file, HFSQL view, query, etc.).
HRecordToXMLRetrieves the structure and the value of the current record and exports them into a character string in XML format.
HRecreateSubscriberReplicaRecreates the subscriber replica description file (universal replication only) based on the information of the master replica (.rpm and .syn files).
HRefreshViewAsks to recalculate the content of a materialized view.
HRegenerateFileRegenerates a data file from its log.
HResetInitializes:
  • one or all variables of file items with their default values.
  • one or all items of a Record variable with their default values.
HRestorePositionRestores the context of a previously saved file (HSavePosition): current record, filter, pointers.
HRetrieveItemReturns the content of an item found in the current record (in the data file, view, query, ...).
HRetrieveRecordReturns the content of the current record (in a file, a view or a query, ...).
HRplDeclareLinkDeclares a (1, 1) (0, n) link between two tables.
HRplFilterProcedureDefines the WLanguage procedure that will be called whenever a replication operation is performed on a specific file.
HRplManageFileDefines the options used for the universal replication of a file:
  • the replication direction.
  • the management mode of conflicts.
HRplManageItemSpecifies the replication options for an item: the item can be replicated or not.
HRplPassSets the passwords used to protect the universal replication files.
HSaveModifies the current record if it exists, otherwise adds a new record.
HSavePositionStores the current file context: current record, filter, pointers.
HSecurityEnables or disables the automatic security mechanism on one or more data files.
HSeekPositions on the first record of the data file whose value for a specific item is greater than or equal to a search value (generic search by default).
HSeekFirstPositions on the first file record whose value for a specific item is greater than or equal to a sought value.
HSeekLastSets the position on the last file record whose value for a specific item is less than or equal to a sought value.
HSetDuplicatesEnables or disables the management of duplicates on a unique key.
HSetIntegrityEnables or disables the management of an integrity constraint on a file link.
HSetLogEnables or disables log management on a file.
HSetMemoUsed to modify the management mode of memo items.
HSetPositionSets the position on a record, based on the approximate position of one of its items.
HSetREPEnables or disables the management of .REP files.
HSetReplicationTemporarily disables (or re-enables) the replication.
HSetTransactionEnables or disables the management of transactions for one or more files.
HSetTriggerEnables or disables the management of triggers.
HSortViewSorts an HFSQL view by creating an index on a view item. This function uses the former view mechanism.
HStatCalculatePerforms various statistical calculations on the file keys.
HStatDateReturns the date of the last update for the index statistics.
HStateUsed to find out the state of a record.
HStatNbDuplicatesReturns the number of duplicates for a given key item.
HStatNbRecReturns the number of entries for a given key item.
HStatNbRecRangeReturns an estimate regarding the number of entries for a given key item in a given interval of values.
HStatTimeReturns the time of the last update for the index statistics.
HSubstDirAssociates the data directory specified in the analysis with a directory found on disk.
HSynchronizeReplicaSynchronizes the master replica and the subscriber replica: the operations performed on one of the replicas are transferred into the other replica.
HToFileCopies a data source (file, query, view, ...) to a physical HFSQL file with the same description. This file is neither encrypted nor password protected.
HToItemAssigns the specified value to an item of the current record.
HTransactionStarts a transaction on the data files (HFSQL or accessed via a Native Connector) and creates the transaction file.
HTransactionCancelIf a transaction is in progress, cancels all the operations performed on the data files in transaction since the start of transaction.
HTransactionEndValidates the current transaction:
  • the modifications performed on the data file since the start of transaction (HTransactionStart) are validated.
  • the transaction file is deleted (if the transaction is the last transaction in progress for a network application)
  • the records locked in read-only by the transaction are unlocked.
HTransactionFreeTransforms all the records "in transaction" into "Normal" records if these records do not belong to a transaction currently in progress.
HTransactionInProgressUsed to find out whether a transaction is in progress.
HTransactionInterruptedUsed to find out whether a transaction was interrupted (the transaction was neither validated nor canceled).
HTransactionStartStarts a transaction on the data files (HFSQL or accessed via a Native Connector) and creates the transaction file.
HTriggerRecordBeforeRetrieves the value of the current record before the trigger(s) are executed.
HUnlockFileUnlocks the records of a data file:
  • locked by HLockFile.
  • individually locked by a locking read function.
HUnlockRecNumUnlocks a record locked by:
  • HLockRecNum,
  • a read function used with a locking parameter (HRead associated with the hLockWrite or hLockReadWrite constant for example).
HVersionUsed to find out:
  • whether the content of a file was modified.
  • whether the content of a file used by a query was modified.
HViewToFileSaves the modifications performed in an HFSQL view (by HModify, HDelete or HCross) in the corresponding data file. This function uses the former view mechanism.
HWriteWrites a record into a data file without updating the indexes corresponding to all keys used in the file.
WithSpaceAdds or deletes the spaces found on the right of a text item when reading it.
Related Examples:
WD Explorer Bar Sample components (WINDEV): WD Explorer Bar
[ + ] The WD Explorer Bar component is used to manage an address bar similar to the address bar of the explorer found in Windows Vista.
This address bar (also called 'Explorer Breadcrumb') is used to easily navigate in a treeview.
You have the ability to add elements by programming, like a TreeView control.
You also have the ability to fill the address bar via a TreeView control, and even from one or more HFSQL data files.
The navigation in the bar is automatically managed by the component via an internal window.
You also have the ability to branch the call to a WLanguage user procedure when an element is clicked.
WD Detecting HFSQL Errors Training (WINDEV): WD Detecting HFSQL Errors
[ + ] This educational example, powered by WINDEV, presents the module for assisted management of HFSQL errors:
Duplicate, Integrity, Lock, Password, Modification conflict.
This example presents:
- the default operating mode,
- the different methods for customizing this assisted management.
WD Search On Composite Keys Training (WINDEV): WD Search On Composite Keys
[ + ] Summary of the example supplied with WINDEV:
This example explains how to implement filters or searches on HFSQL files.
On the first tab, you have the ability to perform different searches and to see the result of your search.
On the second tab, you have the ability to implement a filter by using the composite keys and to see the result issued from your filter.
WD Trigger Training (WINDEV): WD Trigger
[ + ] This example explains how to use the triggers with WINDEV.
A trigger is a WLanguage procedure automatically called by the HFSQL engine whenever a HFSQL function is run.

The following topics are presented in this example:
1/ how to branch a trigger on a HFSQL function
2/ the code that must be used in the function called by trigger.

Summary of the example supplied with WINDEV:
This application is used to enter the expenses for the contributors of a company.
Two modes can be used:
- the mode with read/write rights
- the mode with read-only rights.
The triggers will be used to control the access to the files according to the current mode.
In this example, the triggers are also used to write the different events into a log file.
This log file can be directly consulted in the application.
WD Duplication Training (WINDEV): WD Duplication
[ + ] This educational example explains how to duplicate a database on a server.
The duplication is performed by programming and it allows you to give a new name to the database.
The example is based on the functions for saving and restoring a HFSQL backup.
WD Queries Training (WINDEV): WD Queries
[ + ] This example presents the use of SQL queries on HFSQL databases.
The following topic will be presented in this example: the SQL queries

Summary of the example supplied with WINDEV:
Creating queries with WINDEV is child's play.

This example presents the main features for using the SQL queries on HFSQL databases (calculations, passing parameters, printed reports).
The result of the queries can be used in different ways: in a table, in a report, exported (via a right mouse click) to Excel, Word and XML.

The SQL code used is compatible with all the databases.
With WINDEV, your applications are independent of the database.
WD Reflexive link Training (WINDEV): WD Reflexive link
[ + ] This example presents the management of a reflexive link with the WLanguage functions.
The following topics are presented in this example:
1/ Fill a control with HFSQL content (automatic management of reflexive link).
2/ Fill by programming.
WD OLEDB Training (WINDEV): WD OLEDB
[ + ] This example presents the access to a database via OLE DB with the WLanguage functions.
The following topics are presented in this example:
1/ the independence between your applications and the database
2/ the connection via OLE DB
Summary of the example supplied with WINDEV:
This example explains how to create an application independent of the database used.
The same application with the same source code accesses a HFSQL file or an Access database via OLE DB.
According to this principle, your applications will be able to access all the available databases.
WD Who is locking Training (WINDEV): WD Who is locking
[ + ] This example explains how to notify to the users of a HFSQL database in network "who" is locking an inaccessible record.

The following topics are presented in this example:
1/ management of concurrent accesses
2/ automatic refresh by timer
3/ management of a "system" file to store information about the locks.

Summary of the example supplied with WINDEV:
This example, powered by WINDEV, includes 2 projects:
- WD Who is locking: test application that manages a "client" file in network
- WD Supervisor of locks: management tool used to view the existing locks and to force the unlocking if necessary.
An edit form may have been left opened by a user for quite a long time ; this may interfere with the other users.
The 'Free' button sends a message to the relevant user and asks him to free the record.
The 'Unlock!' button is used to force the unlocking of the record.
Caution: This operation will send a message forcing the closing of the application that performed the lock.
WD Password Complete examples (WINDEV): WD Password
[ + ] The WD Password example is a "password chest". It can be used to manage the passwords created when using Internet sites but also passwords created for everyday life (such as PIN numbers, ATM codes, ....).
The security of the data is guaranteed by the use of HFSQL encryption. Therefore, even if the files containing this information happen to be stolen, this information cannot be used without knowing the main password.
The example can also be used to generate very strong passwords.
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 06/10/2024

Send a report | Local help