- Reading data during the initialization
- Text of SQL query
- Executing an SQL query with parameters
- SQL injection: How to prevent it?
- Inserting binary data
- Joins
- Why should the hQueryWithoutCorrection constant be used?
- Condition on a composite key in an SQL query
- Queries on HFSQL data files
- Optimization for running the same query several times (native Oracle and SQL Server accesses only)
- Specifying and retrieving values when running a query (stored procedures)
- SQL queries (HExecuteSQLQuery or execution of SQL queries created in the query editor)
- Browsing queries (HReadFirst)
HExecuteSQLQuery (Function) In french: HExécuteRequêteSQL
 Not available with this kind of connection
ReqClient is Data Source
// Initialisation de la requête "Client"
HExecuteSQLQuery(ReqClient, "SELECT NOM FROM CLIENT")
ReqTache is Data Source
// Initialisation de la requête "ReqTache"
// Cette requête utilise une fonction WLangage
// Cette requête sélectionne les tâches de plus de 5 jours
HExecuteSQLQuery(ReqTache, ...
"SELECT Tâche.Libellé FROM Tâche " +...
"WHERE WL.DateDifférence(Tâche.DateDébut, Tâche.DateFin) > 5")
// Modification dynamique de la condition LIMIT d'une requête SQL
REQ is Data Source
sMaReq is string = [
SELECT * FROM
CODEPOSTAUX
%1
]
sMaReq = StringBuild(sMaReq, "LIMIT 1, 10")
IF NOT HExecuteSQLQuery(REQ, hQueryDefault, sMaReq) THEN
Error(HErrorInfo())
ELSE
Info("La requête contient " + HNbRec(REQ) + " enregistrements.")
END
Syntax
Executing an SQL query (without connection) Hide the details
<Result> = HExecuteSQLQuery(<Query> [, <Mode>] , <SQL query text>)
<Result>: Boolean - True if the SQL query was initialized,
- False otherwise. HErrorInfo 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 with the new query.
 Versions 23 and laterName 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.  This type of variable is not available.  New in version 23Name 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. This type of variable is not available.
This type of variable is not available.
<Mode>: Optional Integer constant Option for initializing the query: | | | hCheckDuplicates | Enables the management of duplicates during the query execution. The query is not run if a duplicate error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: the query execution may be slowed down.
| | hCheckIntegrity | Enables the management of integrity during the query execution. The query is not run if an integrity error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: the query execution may be slowed down.
| hModifyFile
(constant that can be combined with the other constants) | - On HFSQL data files: When the result of the query is modified (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be propagated in the data files included in the query.
If this option is not specified, only the query result is modified.
For more details on modifying the result of a query, see Modifying the query content. - On data files in a format other than HFSQL, the hModifyFile constant is useless: the data files taking part in the query are automatically modified when modifying the query result.
| | hNoBind | | Versions 21 and laterhNoHourglass New in version 21hNoHourglass hNoHourglass | When reading the query result, this read operation may be locked and an hourglass appears by default. This constant is used not to display the hourglass in this case.
| | hQueryBreakable | The query initialization can be interrupted by the ESC key.
| hQueryDefault
(default value) | The query is initialized without interruption. | | hQueryWithoutCorrection | | | hQueryWithoutHFCorrection | | | hSQLUnicode | Used to specify that the query text must be sent in Unicode format to the server. | hWithFilter
(constant that can be combined with the other constants) | If this parameter is specified:- the query result corresponds to a selection of filter records if the HFSQL engine allows it. Otherwise, the query result corresponds to an HFSQL view.
- the following operations are not allowed: search on the query result, query on query, view on query, query on view.
- the browse items specified when browsing the result are ignored.
- the magnifier is not available for the tables linked to a query.
- HNbRec cannot be used on the queries.
Caution: if this option is specified, the hModifyFile constant is automatically used.
If this parameter is not specified (by default):- the query result corresponds to an HFSQL view.
- the following operations can be performed on the queries: search on the query result, query on query, view on query, query on view.
- the browse items specified when browsing the result are taken into account.
- the magnifier is available on tables linked to a query.
- HNbRec can be used on the queries.
| | hWithTransaction | Only write requests (INSERT, UPDATE and DELETE) are executed in a transaction.A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). The transaction is always completed at the end of the execution of HExecuteSQLQuery. There is no data source for write requests: there is no need to call HFreeQuery. If the query cannot end properly (locked records, power failure, ...), the transaction is canceled and the data files are restored to their previous status (before the query execution). Remark: the query execution may be slowed down.
| | hYield | For the UPDATE and DELETE queries performed on an important number of records and being quite long, this constant is used to give control back to the other applications that use the data files.
In this case:- the query runs a little slower,
- the file server is not saturated,
- the other users of data files can still access them without being locked.
|
Versions 16 and later New in version 16 <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.
Versions 16 and later New in version 16 Versions 23 and laterThis parameter is not available if <Query name> corresponds to an SQL Query variable. New in version 23This parameter is not available if <Query name> corresponds to an SQL Query variable. This parameter is not available if <Query name> corresponds to an SQL Query variable.
Executing an SQL query via a connection Hide the details
<Result> = HExecuteSQLQuery(<Query> [, <Connection> [, <Mode>]] , <SQL query text>)
<Result>: Boolean - True if the SQL query was initialized,
- False otherwise. HErrorInfo 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 with the new query.
Versions 23 and laterName 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. This type of variable is not available. New in version 23Name 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. This type of variable is not available. 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.
This type of variable is not available.
<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: | | | hCheckDuplicates | Enables the management of duplicates during the query execution. The query is not run if a duplicate error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: the query execution may be slowed down.
| | hCheckIntegrity | Enables the management of integrity during the query execution. The query is not run if an integrity error occurs.
This constant forces the hWithTransaction constant. A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). If the application is stopped during the query execution (power outage for example), the transaction will be canceled when the data files of query are used for the next time.
Remark: the query execution may be slowed down.
| hModifyFile
(constant that can be combined with the other constants) | - On HFSQL data files: When the result of the query is modified (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be propagated in the data files included in the query.
If this option is not specified, only the query result is modified.
For more details on modifying the result of a query, see Modifying the query content. - On data files in a format other than HFSQL, the hModifyFile constant is useless: the data files taking part in the query are automatically modified when modifying the query result.
| | hNoBind | | Versions 21 and laterhNoHourglass New in version 21hNoHourglass hNoHourglass | When reading the query result, this read operation may be locked and an hourglass appears by default. This constant is used not to display the hourglass in this case.
| | hQueryBreakable | The query initialization can be interrupted by the ESC key.
| hQueryDefault
(default value) | The query is initialized without interruption. | | hQueryWithoutCorrection | | | hQueryWithoutHFCorrection | | | hSQLUnicode | Used to specify that the query text must be sent in Unicode format to the server. | hWithFilter
(constant that can be combined with the other constants) | If this parameter is specified:- the query result corresponds to a selection of filter records if the HFSQL engine allows it. Otherwise, the query result corresponds to an HFSQL view.
- the following operations are not allowed: search on the query result, query on query, view on query, query on view.
- the browse items specified when browsing the result are ignored.
- the magnifier is not available for the tables linked to a query.
- HNbRec cannot be used on the queries.
Caution: if this option is specified, the hModifyFile constant is automatically used.
If this parameter is not specified (by default):- the query result corresponds to an HFSQL view.
- the following operations can be performed on the queries: search on the query result, query on query, view on query, query on view.
- the browse items specified when browsing the result are taken into account.
- the magnifier is available on tables linked to a query.
- HNbRec can be used on the queries.
| | hWithTransaction | Only write requests (INSERT, UPDATE and DELETE) are executed in a transaction.A transaction is started, the records are locked in read/write during the execution of the query and they are unlocked at the end of its execution, regardless of the result (failure or success). The transaction is always completed at the end of the execution of HExecuteSQLQuery. There is no data source for write requests: there is no need to call HFreeQuery. If the query cannot end properly (locked records, power failure, ...), the transaction is canceled and the data files are restored to their previous status (before the query execution). Remark: the query execution may be slowed down.
| | hYield | For the UPDATE and DELETE queries performed on an important number of records and being quite long, this constant is used to give control back to the other applications that use the data files.
In this case:- the query runs a little slower,
- the file server is not saturated,
- the other users of data files can still access them without being locked.
|
Versions 16 and later New in version 16 <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.
Versions 16 and later New in version 16 Versions 23 and laterThis parameter is not available if <Query name> corresponds to an SQL Query variable. New in version 23This parameter is not available if <Query name> corresponds to an SQL Query variable. This parameter is not available if <Query name> corresponds to an SQL Query variable. Remarks Reading data during the initialization Depending on the query (sorts, groups, ...), the data can be read (or not) when the initializing the query. Remark: ExecutionCompleted is used to find out whether the query is entirely filled (and therefore if the read operations can be performed without lock). To access the query items, a Data source variable must be declared (see the Example section). The query is automatically freed when the variable is destroyed. To force the resources of this query to be freed, use: 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 [Mon Fichier1].MaRubrique, [Mon Fichier1].[Ma rubrique1], MonFichier2.[Ma clé1]
FROM [Mon Fichier1], MonFichier2
WHERE [Mon Fichier1].[Ma clé1] = MonFichier2.[Ma clé1]
Executing an SQL query with parameters An SQL query can contain parameters. To execute this type of SQL query with HExecuteSQLQuery, you must: - In the text of SQL query, define the different parameters by using the {Parameter Name} notation. For example:
"SELECT * FROM client WHERE nom={p_nom}"
- Define a Data source variable. The name of this variable must correspond to the <Query name> parameter of HExecuteSQLQuery.
Example:
- Specify the value of parameters, via the following syntax:
<Variable Source de données>.<Nom Paramètre1> = xxx
<Variable Source de données>.<Nom Paramètre2> = xxx
<Variable Source de données>.<Nom Paramètre3> = xxx
Example:
MaRequête.p_nom = "Dupont"
- Execute the SQL query with HExecuteSQLQuery. Example:
HExecuteSQLQuery(MaRequête, "SELECT * FROM client WHERE nom={p_nom}")
Remark: The structure of the query parameters is reset each time the query is executed. 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 HExecuteSQLQuery, you must use queries with parameters and you must not concatenate strings to build the query. Example:
// Construction de la requête par concaténation
// -> faille de sécurité
sdReq is Data Source
HExecuteSQLQuery(sdReq, "SELECT * FROM client WHERE nom = '" + SAI_Nom + "'")
// Utilisation de paramètres dans la requête
// -> code sûr et sécurisé
sdReq is Data Source
sdReq.p_nom = SAI_Nom
HExecuteSQLQuery(sdReq, "SELECT * FROM client WHERE nom={p_nom}")
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. Inserting binary data When inserting data via INSERT or UPDATE, if a memo item must be assigned with binary data that can contain the character \0 (ASCII code for zero), the data must be placed in a hexadecimal buffer. Example:
RequeteSQL is string
SourceDeDonnees is Data Source
b is Buffer = "Données binaires incluant "+Charact(0)+" (zéro binaire)"
// ou: b est un Buffer = fChargeBuffer("<chemin>\photo.jpg")
RequeteSQL = "INSERT INTO Produit (Photo) " + ...
"VALUES (UNHEX('" + BufferToHexa(b, NoGrouping, NoLine)+"'))"
HExecuteSQLQuery(SourceDeDonnees, hQueryDefault, RequeteSQL)
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 MonFichier.MaRubrique, MonFichier.MaRubrique1
FROM MonFichier
WHERE NomFichier.Nom = "Dupont" AND NomFichier.Prénom = "Florence"
Business / UI classification: Business Logic
This page is also available for…
|
|
|
| |
| | PROCEDURE UI_CargaTablegrid(Codigo)
SqlScript is SQL Query =
[
SELECT F028_FATOR_CONVERSAO.F003_PROD_ID,
F028_FATOR_CONVERSAO.F028_QTDE_ORIGEM,
F028_FATOR_CONVERSAO.F028_UNIDADE_ORIGEM
FROM F028_FATOR_CONVERSAO
WHERE
F028_FATOR_CONVERSAO.F003_PROD_ID = {ParamF003_PROD_ID}
]
SqlScript.ParamF003_PROD_ID = Codigo
IF HExecuteSQLQuery(SqlScript) = True THEN
FOR EACH SqlScript
TableAdd(TABLEGRID_F028_FATOR_CONVERSAO,SqlScript.F028_FATOR_CONVERSAO_ID +TAB+
SqlScript.F003_PROD_ID +TAB+
SqlScript.F028_QTDE_ORIGEM)
END
END
TableDisplay(TABLEGRID_F028_FATOR_CONVERSAO,taInit) |
|
|
|
| |
| |
| |
|
| Uso de SysDate and Dblink |
|
| To use sysdate and dblink with another database it is important to inform in the parameters: hQueryWithoutCorrection
Para usar o sysdate e fazer um dblink com outro banco de dados é importante informar nos paramentros: hQueryWithoutCorrection
Pour utiliser sysdate et dblink avec une autre base de données, il est important de renseigner les paramètres: hQueryWithoutCorrection |
|
|
|
| |
| |
| |
|
| | https://forum.pcsoft.fr/fr-FR/pcsoft.br.windev/3133-winformatica-example-hexecutesqlquery-with-break-and-filter-custom/read.awp |
|
|
|
| |
| |
| |
|
| Exemplo HexecuteSqlQuery com WHILE HOut () = False |
|
| numero_corrida é int = 0
MyProcedureUltimaCorrida é fonte de dados
IF HExecuteSQLQuery (MyProcedureUltimaCorrida, ConnNativa, hQueryWithoutCorrection, "NG0002_Procedure_UltimaCorrida") THEN
HReadFirst (MyProcedureUltimaCorrida, num_corrida)
ENQUANTO Hout () = False
ok = HReadNext (MyProcedureUltimaCorrida, num_corrida)
numero_corrida = MyProcedureUltimaCorrida.num_corrida
END
ELSE
Erro (HErrorInfo ())
END
info(numero_corrida) |
|
|
|
| |
| |
| |
|
| | n_value_demo_number is int = 1
s_value_demo_string is string = "abc"
ds_result is Data Source
b_result is boolean
s_sql is string = "CALL pr_test ("+n_value_demo_number+","+"'"+s_value_demo_string+"')"
b_resultado = HExecuteSQLQuery(ds_result,CONNECTION_NAME_HERE,hQueryDefault+hQueryWithoutCorrection,s_sql)
IF b_resultado = False THEN
error("Error Running Procedure ",herrorinfo())
END |
|
|
|
| |
| |
| |
| |
| |
| |
| | |
|