HExecuteSQLQueryAsynchronous (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

SQL query text
Executing an SQL query with parameters
SQL injection: How to protect yourself?
Joins
Why should the hQueryWithoutCorrection constant be used?
Condition on a composite key in an SQL query

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

HExecuteSQLQueryAsynchronous (Function)

In french: HExécuteRequêteSQLAsynchrone

Executes an SQL query asynchronously. This SQL query can correspond to:

the SQL code typed in HExecuteSQLQueryAsynchronous.
an SQL Query variable.

Use conditions:

This mode is intended for queries with an execution time of several seconds. For immediate or almost immediate queries, this mode is too slow.
The query must be of the "SELECT" type: 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

REQ_WDRSQL is Data Source
HExecuteSQLQueryAsynchronous(REQ_WDRSQL, MaConnexionCS2, hQueryDefault, "SELECT * FROM CLIENT"
		MaProcedurePourChaqueEnregistrement, MaProcedureFinale) 


INTERNAL PROCEDURE MaProcedurePourChaqueEnregistrement(enreg is Record)
	Trace("Procédure de traitement de chaque Enregistrement" + enreg.NomRubrique)
END

INTERNAL PROCEDURE MaProcedureFinale(MonRes is int)
	Trace("Procédure de traitement du résultat final")
	SWITCH MonRes
		CASE heqaOK : 
		Trace("La requête et le traitement des enregistrements se sont bien exécutés.")
		CASE heqaCanceled : 
		Trace("Un traitement a renvoyé Faux. La requête et les procédures ont été annulées.")
		OTHER CASE : 
		Trace("Une procédure ou l'exécution de la requête a rencontré une erreur.")
	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> programmatically. If a query with the same name is already declared, it is replaced with 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: Execute a query without bind enabled.
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.).
Warning: if this constant is used:
the connection name must be specified (<Connection> parameter).
the following function cannot be used on the manipulated request: HFilter.
you cannot cancel a condition by assigning it to NULL.
it is advisable not to specify a browse item 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.

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> programmatically. If a query with the same name is already declared, it is replaced with 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: Execute a query without bind enabled.
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.).
Warning: if this constant is used:
the connection name must be specified (<Connection> parameter).
the following function cannot be used on the manipulated request: HFilter.
you cannot cancel a condition by assigning it to NULL.
it is advisable not to specify a browse item 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.

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

SQL query text

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 the SQL query, define the various parameters using the notation {Parameter name}. For example:
"SELECT * FROM client WHERE name={p_name}"
Define a variable of type Data source. The name of this variable must correspond to the <Query name> parameter of HExecuteSQLQueryAsynchronous.
Example:
MaRequête 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:
MaRequête.p_nom = "Dupont"

Execute the SQL query with HExecuteSQLQueryAsynchronous. Example:

HExecuteSQLQueryAsynchronous(MaRequête, 
	"SELECT * FROM client WHERE nom={p_nom}", 
	hQueryDefault, maProcEnreg)

Note Each time a parameterized query is run, the query parameter structure is re-initialized.

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 protect yourself?

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

Joins of the type (A join B on x=y) join C on y=z ... are not handled automatically by Native Access: in this case, the constant hRequêteSansCorrection constant must be used to manage this type of join.

Remarque: 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.
Replacement of all PC SOFT proprietary signs (e.g. ']=' begins with) with their standard SQL equivalent.	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), use the constant combination hQueryWithoutCorrection + hSansBind constants to prevent the query from being 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 key made up of the NAME and FIRST NAME fields (NAMEFIRST NAME field):

SELECT MyFile.MyItem, MyFile.MyItem1
FROM MyFile
WHERE FileName.LastName = "Smith" AND FileName.FirstName = "Fred"

Business / UI classification: Business Logic

Component: wd300hf.dll