PC SOFT

ONLINE HELP
 WINDEV, WEBDEV AND WINDEV MOBILE
  • Text of SQL query
  • Executing an SQL query with parameters
  • SQL injection: How to prevent it?
  • Joins
  • Why should the hQueryWithoutCorrection constant be used?
  • Condition on a composite key in an SQL query
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
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 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.
AndroidAndroid Widget Java 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
Native Connectors (Native Accesses) SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
hQueryDefault
(default value)		Initialize the query.
hQueryWithoutCorrection
OLE DBNative Connectors (Native Accesses) 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
HFSQL 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.

Java Access by JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
AndroidAndroid Widget 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.
AndroidAndroid Widget Java 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:
heqaCanceledThe procedure executed for each record returned False. The query and the different procedures were canceled.
heqaErrorThe query and/or the procedure called for each record encountered an error. You can get more details on the error with HErrorInfo.
heqaOKThe 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: <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
Native Connectors (Native Accesses) SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
hQueryDefault
(default value)		Initialize the query.
hQueryWithoutCorrection
OLE DBNative Connectors (Native Accesses) 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
HFSQL 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.

Java Access by JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
AndroidAndroid Widget 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.
AndroidAndroid Widget Java 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:
heqaCanceledThe procedure executed for each record returned False. The query and the different procedures were canceled.
heqaErrorThe query and/or the procedure called for each record encountered an error. You can get more details on the error with HErrorInfo.
heqaOKThe 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:
  1. 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}"
  2. Define a Data source variable. The name of this variable must correspond to the <Query name> parameter of HExecuteSQLQueryAsynchronous.
    Example:
    MyQuery is Data Source
  3. 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"
  4. 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.
AndroidAndroid Widget 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.
AndroidAndroid Widget 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.
Native Connectors (Native Accesses)

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.
WINDEVReports and QueriesUser code (UMC)OLE DBNative Connectors (Native Accesses)

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 specifiedhQueryWithoutCorrection 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.

Native Connectors (Native Accesses) 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
See also
Minimum version required
  • Version 26
This page is also available for…
Comments
Click [Add] to post a comment