HExecuteSQLQueryAsynchronous (Function)

This mode is intended for queries whose execution takes a few seconds. For immediate or almost immediate queries, this mode is too slow.
The query must be of type "SELECT": it must return records.
A query can only be executed asynchronously from the main context. In other cases, it is necessary to use threads.
This function does not allow passing parameters to a query that already has a parameter. Parameters must be passed before the query is executed.

You cannot execute queries with SQL code containing WLanguage functions.

Example

QRY_WDRSQL is Data Source
HExecuteSQLQueryAsynchronous(QRY_WDRSQL, MyCS2Connection, hQueryDefault, "SELECT * FROM CLIENT"
MyProcedureForEachRecord, MyFinalProcedure)

INTERNAL PROCEDURE MyProcedureForEachRecord(rec is Record)
Trace("Procedure for processing each Record" + rec.ItemName)
END

INTERNAL PROCEDURE MyFinalProcedure(MyRes is int)
Trace("Procedure for processing the final result")
SWITCH MyRes
CASE heqaOK:
Trace("The query and the record processing were correctly executed.")
CASE heqaCanceled:
Trace ("One process returned False. The query and the procedures have been canceled.")
OTHER CASE:
Trace("There was a problem with the procedure or with the execution of the query.")
END
END

Syntax

Executing an SQL query (without connection) Hide the details

<Result> = HExecuteSQLQueryAsynchronous(<Query> [, <Mode>] , <SQL query text> , <WLanguage procedure for each record> [, <End WLanguage procedure>])

<Result>: boolean

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

<Query>: Character string or data source

Name of the query that will be executed. This name is used to handle the result of <Text of SQL query> through programming. If a query with the same name is already declared, it is replaced by the new query.
Name of the SQL query variable containing the SQL code of the query. In this case, the <Text of SQL query> parameter must not be specified.

<Mode>: Optional Integer constant

Option for initializing the query:
hNoBind
SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
hQueryDefault
(default value) Initialize the query.
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).
the following function cannot be used on the query: HFilter.
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.

Access by JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
This parameter can only be set to hQueryDefault. An error is generated if another constant is used.

<SQL query text>: Character string

Text of the SQL query to execute. This text can correspond to a character string in ANSI format or in Unicode format.
You cannot execute queries with SQL code containing WLanguage functions.
This parameter is not available if <Query name> corresponds to an SQL Query variable.

<WLanguage procedure for each record>: Procedure name

WLanguage procedure ("callback") executed for each record that corresponds to the found query. This procedure has the following format:

PROCEDURE <Procedure name>(ARec is Record)

where <ARec> is a Record variable that corresponds to the current record (for the executed query).
By default, the procedure returns True and goes to the next record.
If this procedure returns False, the query is canceled and the <End WLanguage procedure> is executed.

<End WLanguage procedure>: Optional procedure name

WLanguage procedure ("callback") executed at the end of the execution of the query. This procedure has the following format:

PROCEDURE <Procedure name>(nResult is int)

where <nResult> is an Integer constant that can correspond to the following values:
heqaCanceled The procedure executed for each record returned False. The query and the different procedures were canceled.
heqaError The query and/or the procedure called for each record encountered an error. You can get more details on the error with HErrorInfo.
heqaOK The query and the procedure were correctly executed for each record.

Executing an SQL query via a connection Hide the details

<Result> = HExecuteSQLQueryAsynchronous(<Query> [, <Connection> [, <Mode>]] , <SQL query text> , <WLanguage procedure for each record> [, <End WLanguage procedure>])

<Result>: Boolean

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

<Query>: Character string or data source

Name of the query that will be executed. This name is used to handle the result of <Text of SQL query> through programming. If a query with the same name is already declared, it is replaced by the new query.
Name of the SQL query variable containing the SQL code of the query. In this case, the <Text of SQL query> parameter must not be specified.

<Connection>: Optional character string or Connection variable

Connection used to execute the query. This connection corresponds to:
a connection defined in the data model editor or through programming with HDescribeConnection or HOpenConnection.
a variable of type Connection.
<Result> is set to False if this parameter does not correspond to an existing connection.

<Mode>: Optional Integer constant

Option for initializing the query:
hNoBind
SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
hQueryDefault
(default value) Initialize the query.
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).
the following function cannot be used on the query: HFilter.
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.

Access by JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
This parameter can only be set to hQueryDefault. An error is generated if another constant is used.

<SQL query text>: Character string

Text of the SQL query to execute. This text can correspond to a character string in ANSI format or in Unicode format.
You cannot execute queries with SQL code containing WLanguage functions.
This parameter is not available if <Query name> corresponds to an SQL Query variable.

<WLanguage procedure for each record>: Procedure name

WLanguage procedure ("callback") executed for each record that corresponds to the found query. This procedure has the following format:

PROCEDURE <Procedure name>(ARec is Record)

where <ARec> is a Record variable that corresponds to the current record (for the executed query).
By default, the procedure returns True and goes to the next record.
If this procedure returns False, the query is canceled and the <End WLanguage procedure> is executed.

<End WLanguage procedure>: Optional procedure name

WLanguage procedure ("callback") executed at the end of the execution of the query. This procedure has the following format:

PROCEDURE <Procedure name>(nResult is int)

where <nResult> is an Integer constant that can correspond to the following values:
heqaCanceled The procedure executed for each record returned False. The query and the different procedures were canceled.
heqaError The query and/or the procedure called for each record encountered an error. You can get more details on the error with HErrorInfo.
heqaOK The query and the procedure were correctly executed for each record.

Remarks

Text of SQL query

If a name of a data file or a name of item contains space characters, these names must be enclosed in square brackets in the query text. For example:

SELECT [My File1].MyItem, [My File1].[My item1], MyFile2.[My key1]

FROM [My File1], MyFile2

WHERE [My File1].[My key1] = MyFile2.[My key1]

Executing an SQL query with parameters

An SQL query can contain parameters. To execute this type of SQL query with HExecuteSQLQueryAsynchronous, you must:

In the text of SQL query, define the different parameters by using the {Parameter Name} notation. For example:

"SELECT * FROM client WHERE name={p_name}"
Define a Data source variable. The name of this variable must correspond to the <Query name> parameter of HExecuteSQLQueryAsynchronous.
Example:

MyQuery is Data Source
Specify the value of parameters, via the following syntax:

<Data source variable >.<Name of Parameter1> = xxx
<Data source variable >.<Name of Parameter2> = xxx
<Data source variable >.<Name of Parameter3> = xxx

Example:

MyQuery.p_name = "Doe"
Execute the SQL query with HExecuteSQLQueryAsynchronous. Example:

HExecuteSQLQueryAsynchronous(MyQuery,
"SELECT * FROM client WHERE name={p_name}",
hQueryDefault, myProcRec)

Remark: The structure of the query parameters is reset each time the query is executed.

Queries with parameters are not available. However, 'SQL query' variables are available. This type of variable can correspond to a query with parameters. For more details, see SQL query variable.

SQL injection: How to prevent it?

SQL injection is a hacking technique. It consists of injecting SQL code into the parameters of queries, forcing the execution of unwanted SQL code.

To avoid SQL injection when executing queries via HExecuteSQLQueryAsynchronous, you must use queries with parameters and you must not concatenate strings to build the query.

Using parameters does not allow to interpret the content as being SQL code. This technique prevents from injecting SQL code and it eliminates several risks of hacking.

Queries with parameters are not available. However, 'SQL query' variables are available. This type of variable can correspond to a query with parameters. For more details, see SQL query variable.

Joins

The joins such as (A join B on x=y) join C on y=z ... are not automatically supported by Native Accesses: in this case, the hQueryWithoutCorrection constant must be used to manage this type of join.

Remark: These joins are managed by the HFSQL engine.

Why should the hQueryWithoutCorrection constant be used?

By default, WINDEV and WEBDEV interpret the SQL queries:

built via a Native Access,
built on OLEDB and on ODBC via the OLE DB provider.

In order for the query not to be interpreted, use the hQueryWithoutCorrection constant.

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 HExecuteSQLQueryAsynchronous.
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.
Format the dates and times according to 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.

Special case: Oracle and SQL Server: If the query to be executed contains a script with ":param" (Oracle) or "@param" (SQL Server), you must use the hQueryWithoutCorrection + hNoBind constants in order for the query not to be interpreted.

Condition on a composite key in an SQL query

To define a condition on a composite key in an SQL query, the conditions must be specified for each component of the key.

Do not attempt to directly assign a value to the composite key (indeed, the composite keys are stored as binary values).

Example: The composite key is made of LASTNAME and FIRSTNAME items (LASTNAMEFIRSTNAME item):

SELECT MyFile.MyItem, MyFile.MyItem1

FROM MyFile

WHERE FileName.LastName = "Smith" AND FileName.FirstName = "Fred"

Business / UI classification: Business Logic

Component: wd280hf.dll