Two methods for passing parameters to a query

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Help

Editors

Query editor

Query with parameters

Two methods for passing parameters to a query

Overview

Using HExecuteQuery to pass parameters to a query

Using the . notation

Avoid specifying all the parameters

See also

Query with parameters

Two methods for passing parameters to a query

Overview

Two methods can be used to pass parameters to a query with parameters used in a window or in a control (List Box, Combo Box or Table control for example):

pass the parameters in HExecuteQuery.
use <Query name>.<Parameter name>, then HExecuteQuery.

This help page presents the operating mode and the benefits of each one of these methods.

Remark: The method for passing parameters differs if the query with parameters is used in a report. For more details, see How to use a query with parameters in a report.

Remark: From version 19, HFSQL is the new name of HyperFileSQL.

Using HExecuteQuery to pass parameters to a query

Syntax to use

When a query is run by HExecuteQuery, the last parameters passed to the function correspond to the parameters of the query.

The following syntax is used:

<Result> = HExecuteQuery(<Query Name> [, <Connection>] [, <Mode> [, <Parameters> [, ...]]])

Caution: In this case:

The values of the 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.
To specify the parameters of the query, you must specify the initialization mode of the query.

Using the <Query name>.<Parameter name> notation

Syntax to use

To pass parameters to a query, all the query parameters can be specified to the HFSQL engine before running the query. This method allows you to specify the parameters in any order (useful for the queries that require several parameters).

The following syntax must be used:

<Query name>.<Name of parameter 1> = <Value of parameter 1>
<Query name>.<Name of parameter 2> = <Value of parameter 2>
...
<Query name>.<Name of parameter N> = <Value of parameter N>

<Result> = HExecuteQuery(<Query name>
[ , <Connection>] [, <Mode>])

Caution:

The name of the parameter must be the name specified when the query was created in the query editor. This name must differ from an item name.
The indirection operators or the dynamic compilation (Compile) cannot be used with this syntax.
The values of the parameters are reset each time the query is executed with HExecuteQuery.
If at least one parameter is specified in HExecuteQuery, all the parameters specified beforehand are ignored.
The value of the parameter can correspond to NULL.
If one of the parameters is not specified, the corresponding condition is ignored.

All the expected parameters must necessarily be entered.

Avoid specifying all the parameters

Unspecified query parameters

Regardless of the method used to pass parameters to the query, all the query parameters do not necessarily have to be specified. The query conditions that use unspecified parameters will be ignored.

All the expected parameters must necessarily be entered.

Example: Let's consider the "QRY_Customer_LastName_FirstName" query with the following SQL code:

SELECT * FROM CUSTOMER WHERE LASTNAME = {Param1} AND FIRSTNAME = {Param2}

The 2 parameters are specified:

HExecuteQuery(REQ_Clients_nom_prénom, hQueryDefault, "Dupond", "Jean")

will execute the query

SELECT * FROM CUSTOMER WHERE LASTNAME = 'Smith' AND FIRSTNAME = 'John'
Only the last name is specified:

HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, "Smith" )

will execute the query

SELECT * FROM CUSTOMER WHERE LASTNAME= 'Smith'
Only the first name is specified. However, the name must be specified and must correspond to Null.

sLastName is Variant = Null
// Use of variant required
// for the Null variable (not filled)

HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, sLastName, "John")

// OR

HExecuteQuery(QRY_Customers_LastName_FirstName, hQueryDefault, Null, "John")

will execute the query:

SELECT * FROM CUSTOMER WHERE FIRSTNAME= 'John'