HExecuteQuery (Function)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / WLanguage / Managing databases / HFSQL / HFSQL functions

Reading data during query initialization
Adding records and using the hModifyFile constant
Why should the hQueryWithoutCorrection constant be used
Passing parameters to the query
Freeing the resources of the query
Optimizing the queries on HFSQL data files
Running a query and Table control based on this query
Executing a query and a report based on this query
SQL queries (HExecuteQuery or execution of SQL queries created in the query editor)

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

HExecuteQuery (Function)

In french: HExécuteRequête

Not available with this kind of connection

Declares a query to the HFSQL engine and runs this query. This query can correspond to:

a query created in the query editor.
an SQL Query variable.

Then, the query result can be read. If a query with the same name already exists, this query is replaced. The result is recalculated.

Caution: To set the position on the first record of the query result, use HReadFirst, for example.

To free the resources of a query created in the query editor, use:

the HCancelDeclaration function.
the HFreeQuery function.

Remark: A query will not be initialized if:

the query name is already used in the current analysis (name of data file or name defined by one of the following functions: HDeclare, HDeclareExternal, HDescribeFile).
a syntax error is found in the query.

You cannot execute queries with SQL code containing WLanguage functions.

Example

See additional examples

// Initialisation de la requête "REQ_Client_84"
HExecuteQuery(REQ_Client_84)

// Requête sur fichier Oracle : Clients utilise la connexion "MaConnexion"
// REQ_Marequête = "Select * from clients where IDClient = ?"
HExecuteQuery(REQ_MaRequête, hQueryDefault, 5)

// Exécute et affiche les données d'une requête non contenue dans le projet
// "REQ_Liste_Produits.WDR" est une requête créée avec "Etats & Requêtes"
// Il faut déclarer une source de données du même nom logique que la requête. 
sFichierRequete is string 
sFichierRequete = fReportsAndQueriesDir() + ["\"] + "REQ_Liste_Produits.WDR"
REQ_Liste_Produits is Data Source

// Exécution de la requête pour une famille de produits :
// CODE_FAMILLE est un paramètre de la requête. 
HExecuteQuery(sFichierRequete, hQueryDefault, CODE_FAMILLE)

// Parcours de la requête
FOR EACH REQ_Liste_Produits 
	TableAddLine(TABLE_PRODUIT, sFichierRequete.NOM_PRODUIT, ...
		sFichierRequete.DESIGNATION, sFichierRequete.PrixHT)
END

Syntax

Executing a query (without connection) Hide the details

<Result> = HExecuteQuery(<Query name> [, <Mode> [, <Parameter 1> [... [, <Parameter N>]]]])

<Result>: boolean

True if the query was initialized,
False otherwise. HErrorInfo returns more details about the problem.

<Query name>: Character string

Name of query to be executed:
Name and full path of query (".WDR" file) to initialize.
Not available.
Logical name of the query to be initialized.
If a query with the same name is already declared, it is replaced with the new query.
Name of a variable of type SQL query.

<Mode>: Optional Integer constant

Option for initializing the query:
hCheckDuplicates Enables the management of duplicates during the query execution. The query is not run if a duplicate error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: The query execution may be slowed down.

The management of duplicates is disabled if the hCheckDuplicates constant is not specified.
An error occurs if a duplicate management operation must be performed, but the user has no rights to enable/disable the management of duplicates (HModifyDatabaseRights).
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hCheckIntegrity Enables the management of integrity during the query execution. The query is not run if an integrity error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: The query execution may be slowed down.

The integrity management is disabled if the hCheckIntegrity constant is not specified.
An error occurs if an integrity management operation must be performed, but the user does not have the rights to enable/disable the integrity management (HModifyDatabaseRights).
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hModifyFile
(option that can be combined with the other constants) On HFSQL data files: When the result of the query is modified (HAdd, HWrite, HModify, HCross and HDelete), these changes are propagated to the data files used in the query.
If this option is not specified, only the query result is modified.
For more details on modifying the result of a query, see Modifying the query content.
On data files in a format other than HFSQL, the hModifyFile constant is useless: the data files taking part in the query are automatically modified when modifying the query result.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hNoHourglass When reading the query result, this read operation may be locked and an hourglass appears by default. This constant is used not to display the hourglass in this case.
This constant is not available.
hQueryBreakable The query initialization can be interrupted by the ESC key.
Remark: This constant can only be used with a selection query (SELECT).. Other query types (INSERT, UPDATE, DELETE, etc.) are not interruptible..
This constant is not available.
This constant is ignored.
The Esc key must be pressed on the client computer.
hQueryDefault
(default value) The query is initialized without interruption.
hQueryWithoutCorrection
No check is performed by the HFSQL engine. This option must be used if the query contains commands specific to a connection type (Oracle, SQL Server, etc.).
Caution: if this constant is used:
the connection name must be specified (<Connection> parameter).
HFilter cannot be called on the query used.
you cannot cancel a condition by assigning it to NULL.
no browse item should be specified in the following functions: HLast, HReadLast, HReadFirst, HFirst, HSeekLast, HSeekFirst.
hQueryWithoutHFCorrection
The file format (filled with space characters or not) is not checked by the HFSQL engine.
To be used if the query handles both HFSQL data files in a format that completes items with space characters and HFSQL data files in a format that does not complete items with space characters.
hWithFilter
(option that can be combined with the other constants) If this parameter is specified:
the query result corresponds to a selection of filter records if the HFSQL engine allows it. Otherwise, the query result corresponds to an HFSQL view
the following operations are not allowed: search on the query result, query on query, view on query, query on view.
the browse items specified when looping through the result are ignored.
the magnifier is not available for the tables linked to a query.
HNbRec cannot be used on the queries.
Caution: the hModifyFile constant is automatically used if this option is specified.
If this parameter is not specified (by default):
the query result corresponds to an HFSQL view
the following operations can be performed on the queries: search on the query result, query on query, view on query, query on view.
the browse items specified when looping through the result are taken into account.
the magnifier is available on tables linked to a query.
HNbRec can be used on the queries.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hWithTransaction The query is run in a transaction.
A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of the query execution, regardless of the query result.
If the query cannot end properly (locked records, power failure, etc.), the transaction is canceled and the data files are restored to their previous status (before the query execution).
Remark: The query execution may be slowed down.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hYield For the UPDATE and DELETE queries performed on an important number of records and being quite long, this constant is used to give control back to the other applications that use the data files.
In this case:
the query runs a little slower,
the file server is not saturated,
the other users of data files can still access them without being locked.
This constant is ignored.
This constant is not available.

Android and Java (access by JDBC): This parameter can only correspond to the hQueryDefault or hQueryWithoutCorrection constant. If another constant is used, it will be ignored.

<Parameter 1>: Type of parameter

Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma.
Remarks:
Passing parameters in HExecuteQuery is kept for backward compatibility. It is recommended to pass parameters using the following notation: <Query name>.<Parameter name> = <Value>.
To specify the query parameters, the <Mode> parameter must be specified.
The value of each parameter can be specified or not.
You can specify a list of values or a list of parameters.
For more details, see Passing parameters. This parameter is not available if <Query name> corresponds to an SQL Query variable.

<Parameter N>: Type of parameter

Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma.
Remarks:
Passing parameters in HExecuteQuery is kept for backward compatibility. It is recommended to pass parameters using the following notation: <Query name>.<Parameter name> = <Value>.
To specify the query parameters, the <Mode> parameter must be specified.
The value of each parameter can be specified or not.
You can specify a list of values or a list of parameters.
For more details, see Passing parameters. This parameter is not available if <Query name> corresponds to an SQL Query variable.

This syntax is not available in stored procedures

Executing a query via a connection Hide the details

<Result> = HExecuteQuery(<Query name> [, <Connection> [, <Mode> [, <Parameter 1> [... [, <Parameter N>]]]]])

<Result>: boolean

True if the query was initialized,
False otherwise. HErrorInfo returns more details about the problem.

<Query name>: Character string

Name of query to be executed:
Name and full path of query (".WDR" file) to initialize.
Not available.
Logical name of the query to be initialized.
If a query with the same name is already declared, it is replaced with the new query.
Name of a variable of type SQL query.

<Connection>: Optional character string

Name of the connection, defined in the data model editor or dynamically with HDescribeConnection. The query will be executed via this connection.
If this parameter is not specified and if the query affects data files accessed by OLE DB or by a native access, the query is executed via the connection used by the first data file found in the query. If the connection used by a data file is modified before the call to HExecuteQuery, the new connection will be used.

<Mode>: Optional Integer constant

Option for initializing the query:
hCheckDuplicates Enables the management of duplicates during the query execution. The query is not run if a duplicate error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: The query execution may be slowed down.

The management of duplicates is disabled if the hCheckDuplicates constant is not specified.
An error occurs if a duplicate management operation must be performed, but the user has no rights to enable/disable the management of duplicates (HModifyDatabaseRights).
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hCheckIntegrity Enables the management of integrity during the query execution. The query is not run if an integrity error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: The query execution may be slowed down.

The integrity management is disabled if the hCheckIntegrity constant is not specified.
An error occurs if an integrity management operation must be performed, but the user does not have the rights to enable/disable the integrity management (HModifyDatabaseRights).
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hModifyFile
(option that can be combined with the other constants) On HFSQL data files: When the result of the query is modified (HAdd, HWrite, HModify, HCross and HDelete), these changes are propagated to the data files used in the query.
If this option is not specified, only the query result is modified.
For more details on modifying the result of a query, see Modifying the query content.
On data files in a format other than HFSQL, the hModifyFile constant is useless: the data files taking part in the query are automatically modified when modifying the query result.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hNoHourglass When reading the query result, this read operation may be locked and an hourglass appears by default. This constant is used not to display the hourglass in this case.
This constant is not available.
hQueryBreakable The query initialization can be interrupted by the ESC key.
Remark: This constant can only be used with a selection query (SELECT).. Other query types (INSERT, UPDATE, DELETE, etc.) are not interruptible..
This constant is not available.
This constant is ignored.
The Esc key must be pressed on the client computer.
hQueryDefault
(default value) The query is initialized without interruption.
hQueryWithoutCorrection
No check is performed by the HFSQL engine. This option must be used if the query contains commands specific to a connection type (Oracle, SQL Server, etc.).
Caution: if this constant is used:
the connection name must be specified (<Connection> parameter).
HFilter cannot be called on the query used.
you cannot cancel a condition by assigning it to NULL.
no browse item should be specified in the following functions: HLast, HReadLast, HReadFirst, HFirst, HSeekLast, HSeekFirst.
hQueryWithoutHFCorrection
The file format (filled with space characters or not) is not checked by the HFSQL engine.
To be used if the query handles both HFSQL data files in a format that completes items with space characters and HFSQL data files in a format that does not complete items with space characters.
hWithFilter
(option that can be combined with the other constants) If this parameter is specified:
the query result corresponds to a selection of filter records if the HFSQL engine allows it. Otherwise, the query result corresponds to an HFSQL view
the following operations are not allowed: search on the query result, query on query, view on query, query on view.
the browse items specified when looping through the result are ignored.
the magnifier is not available for the tables linked to a query.
HNbRec cannot be used on the queries.
Caution: the hModifyFile constant is automatically used if this option is specified.
If this parameter is not specified (by default):
the query result corresponds to an HFSQL view
the following operations can be performed on the queries: search on the query result, query on query, view on query, query on view.
the browse items specified when looping through the result are taken into account.
the magnifier is available on tables linked to a query.
HNbRec can be used on the queries.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hWithTransaction The query is run in a transaction.
A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of the query execution, regardless of the query result.
If the query cannot end properly (locked records, power failure, etc.), the transaction is canceled and the data files are restored to their previous status (before the query execution).
Remark: The query execution may be slowed down.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
hYield For the UPDATE and DELETE queries performed on an important number of records and being quite long, this constant is used to give control back to the other applications that use the data files.
In this case:
the query runs a little slower,
the file server is not saturated,
the other users of data files can still access them without being locked.
This constant is ignored.
This constant is not available.

Android and Java (access by JDBC): This parameter can only correspond to the hQueryDefault or hQueryWithoutCorrection constant. If another constant is used, it will be ignored.

<Parameter 1>: Type of parameter

Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma.
Remarks:
Passing parameters in HExecuteQuery is kept for backward compatibility. It is recommended to pass parameters using the following notation: <Query name>.<Parameter name> = <Value>.
To specify the query parameters, the <Mode> parameter must be specified.
The value of each parameter can be specified or not.
You can specify a list of values or a list of parameters.
For more details, see Passing parameters. This parameter is not available if <Query name> corresponds to an SQL Query variable.

<Parameter N>: Type of parameter

Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma.
Remarks:
Passing parameters in HExecuteQuery is kept for backward compatibility. It is recommended to pass parameters using the following notation: <Query name>.<Parameter name> = <Value>.
To specify the query parameters, the <Mode> parameter must be specified.
The value of each parameter can be specified or not.
You can specify a list of values or a list of parameters.
For more details, see Passing parameters. This parameter is not available if <Query name> corresponds to an SQL Query variable.

Remarks

Reading data during query initialization

Depending on the query (sorts, groups, etc.), the data can be read (or not) when initializing the query.

Remark: ExecutionCompleted is used to find out whether the query is entirely filled (and therefore if the read operations can be performed without lock).

Adding records and using the hModifyFile constant

When records are added into a data file via a query, the query takes into account the records added into the data file regardless of the condition defined in the query. The number of records in this query is systematically modified.

When records are added into a data file via a query, the query takes into account the condition defined in the query. The number of records in this query is modified only if the records added into the data file correspond to this condition.

Why should the hQueryWithoutCorrection constant be used

By default, WINDEV and WEBDEV interpret the SQL queries on OLE DB and on ODBC via the OLE DB provider. To prevent the query from being interpreted, use the hQueryWithoutCorrection constant.

The hQueryWithoutCorrection constant can be used if you directly enter the SQL code of your query in the query editor.

hQueryWithoutCorrection is not specified	hQueryWithoutCorrection is specified

The connection associated with the data files in the query is defined automatically.	The connection to use must be specified in HExecuteSQLQuery.
All PC SOFT proprietary signs are replaced (e.g.: ']=' starts with) with their equivalent in standard SQL.	No replacement is performed. The standard SQL symbols must be used.
Dates and times adopt the format used by the database. For example, the dates are in 'YYYYMMDD' format in WINDEV and WEBDEV while in Access, the dates are in #YYYYDDMM# or #YYYYMMDD# format depending on the system language.	No formatting is performed. The format recognized by the database must be used.
Floats are formatted (the decimal separator can be '.' or ',')	No formatting is performed for the floats.
Depending on the database used, the alias names are replaced with the full names of the items in Where, Order by and Group by For example, the JET engine (Access, dBase, etc.) accepts no alias name in the Where clause of a query	No replacement is performed. The full names of items must be used in the query code for Where, Order by and Group by.

Passing parameters to the query

Several methods can b e used to pass parameters to a query:

Passing parameters with the following notation: <Query name>.<Parameter name> = <Value> (recommended)
Passing parameters in HExecuteQuery (kept for backward compatibility, compatible with PHP).

For more details on the different ways of passing parameters to a query, their advantages and disadvantages, see Methods for passing parameters to a query.

Let's present these two methods:

Passing parameters with the following notation: <Query name>.<Parameter name> = <Value> (recommended)
To pass parameters to a query with parameters before executing it, you can also use the following syntax:
<Nom de la requête>.<Nom du paramètre 1> = xxx
<Nom de la requête>.<Nom du paramètre 2> = xxx
<Nom de la requête>.<Nom du paramètre 3> = xxx
HExécuteRequête(<Nom de la requête>)
SI ErreurDétectée = Vrai ALORS ...

Remarks:
- This syntax makes it easier to pass parameters. Indeed, if the parameters are directly passed to HExecuteQuery, the order of parameters must be complied with.
- The structure of the query parameters is reset each time the query is executed.
Passing parameters in HExecuteQuery (kept for backward compatibility)
In this case, the values of query parameters must be specified in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma. The value of each parameter can be specified or not.
A parameter can be ignored by using the Null value. In this case, the selection conditions that uses this parameter will be ignored.

You can specify a list of values or a list of parameters.
If the selection condition for one of the query parameters is "is included in the list" or "is not included in the list", you must specify a list of values or parameters.
To specify a list of values or a list of parameters, the different values or parameters must be separated by a semi-colon (';'), a carriage return ('CR') or a tabulation ('TAB').
For example:
HExecuteQuery(NomRequête, hQueryDefault, "34000;34200;34400")
ou
HExecuteQuery(NomRequête, hQueryDefault, "34000 "+ CR + "34200" + CR + "34400")
ou
HExecuteQuery(NomRequête, hQueryDefault, "34000" + TAB + "34200" + TAB + "34400")
Remark: If one of the values or parameters contains a semi-colon, a carriage return or a tabulation, this value or parameter must be enclosed in quotes. For example:
HExecuteQuery(NomRequête, hQueryDefault, "'A;A';'A;B';'A;C'")
Remark: All the query parameters do not necessarily have to be specified. The query conditions that use unspecified parameters will be ignored.
Example: Let's consider the query named "Customer_LastName_FirstName" whose SQL code is as follows:
SELECT * FROM CLIENT WHERE NOM = {Param1} AND PRENOM = {Param2}
- The 2 parameters are specified:
  HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, "Smith", "John")
  will execute the query
  SELECT * FROM CLIENT WHERE NOM = 'Dupond' AND PRENOM = 'Jean'
- Only the name is given:
  HExecuteQuery(REQ_Clients_nom_prénom, hQueryDefault, "Dupond")
  will execute the query
  SELECT * FROM CLIENT WHERE NOM = 'Dupond'
- Only the first name is specified:
  sNom is Variant = Null // Utilisation du variant obligatoire pour variable de type Null (non renseignée) HExecuteQuery(REQ_Clients_nom_prénom, hQueryDefault, sNom, "Jean") OR HExecuteQuery(REQ_Clients_nom_prénom, hQueryDefault, Null, "Jean")
  will execute the request
  SELECT * FROM CLIENT WHERE PRENOM = 'Jean'

Freeing the resources of the query

To free the resources of a query, use:

the HCancelDeclaration function.
the HFreeQuery function.

If this operation is not performed, the query is still found in the current HFSQL context and it can be accessed from any process.

Remarks:

The query resources are not automatically released when the window that executed the query is closed (via HExecuteQuery).
The SQL Query variables are automatically freed at the end of variable scope. For more details, see SQL queries in WLanguage.

Optimizing the queries on HFSQL data files

To optimize the query execution time, use HOptimize or HOptimizeQuery on the different data files included in the query.
To optimize query processing time, use HStatCalculate or HIndex on all data files included the query.

Running a query and Table control based on this query

For more details, see Table control based on a data file and management of queries.

Executing a query and a report based on this query

When printing a report based on a query, if the query has already been executed, it is not executed again. To re-execute the query (to add records, for example), simply use HExecuteQuery or iInitReportQuery.

SQL queries (HExecuteQuery or execution of SQL queries created in the query editor)

When using the SQL DELETE, INSERT or UPDATE statements in the SQL queries, no integrity check and no duplicate check are performed on an HFSQL database.
- To perform an automatic integrity check, simply specify the hCheckIntegrity constant. This constant is used to enable the management of integrity during the query execution. The query is not run if an integrity error occurs. Indeed, a transaction is started, the records are locked in write mode during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
  The integrity management is disabled if the hCheckIntegrity constant is not specified. However, an error occurs if an integrity management operation must be performed, but the user does not have the rights to disable integrity management (HModifyDatabaseRights).
- To perform an automatic check for duplicates, simply specify the hCheckDuplicates constant. This constant is used to enable the management of duplicates during the query execution. The query is not run if a duplicate error occurs. Indeed, a transaction is started, the records are locked in write mode during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
  Note: If a duplicate error is detected during the execution of an UPDATE query, HExecuteQuery returns False and the process continues. The corresponding error is returned by HErrorInfo.
  Remark: The default automatic assistance is not called for the duplicate errors. A simple duplicate error is generated. If an assistance was redefined by HOnError, then this custom assistance is called.
  The management of duplicates is disabled if the hCheckDuplicates constant is not specified. An error occurs if a duplicate management operation must be performed, but the user has no rights to disable the management of duplicates (HModifyDatabaseRights).
The UPDATE, DELETE and INSERT queries only exist during their execution. No WLanguage function can be used on this type of query after its execution.
No cascading deletion is supported by the DELETE queries.
When running an INSERT/UPDATE/DELETE query on HFSQL data files, the number of added, modified or deleted records is returned by the H.NbRecModificationQuery variable.

Business / UI classification: Business Logic

Component: wd300hf.dll