|
|
|
|
|
|
|
|
|
|
- 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
HExecuteSQLQueryAsynchronous (Function) 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.
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
Sintaxis
Executing an SQL query (without connection) Ocultar los detalles
<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 | | hQueryDefault
(default value) | Initialize the query. | | hQueryWithoutCorrection | | | hQueryWithoutHFCorrection | |
<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.
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) 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) | | | 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 Ocultar los detalles
<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: <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 | | hQueryDefault
(default value) | Initialize the query. | | hQueryWithoutCorrection | | | hQueryWithoutHFCorrection | |
<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.
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) 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) | | | 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. |
Observaciones 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 SQL query Text, 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:
- 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:
- Execute the SQL query with HExecuteSQLQueryAsynchronous. Example:
HExecuteSQLQueryAsynchronous(MyQuery,
"SELECT * FROM client WHERE name={p_name}",
hQueryDefault, myProcRec)
Note: Each time the parameterized query is run, the query parameter structure is reset. 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. 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 composed 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" Clasificación Lógica de negocio / UI: Lógica de negocio
Esta página también está disponible para…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|