- Reading data during the initialization
- Text of SQL query
- Running 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
- 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
CustomerQry is Data Source
// Initialize the "Customer" query
HExecuteSQLQuery(CustomerQry, "SELECT NAME FROM CUSTOMER")
TaskQry is Data Source
// Initialize the "TaskQry" query
// This query is using a WLanguage function
// This query selects the tasks exceeding 5 days
HExecuteSQLQuery(TaskQry, ...
"SELECT Task.Caption FROM Task" +...
"WHERE WL.DateDifference(Task.StartTime, Task.EndDate) > 5")
// Dynamic modification of the LIMIT condition of a SQL query
QRY is Data Source
sMyQry is string = [
SELECT * FROM
ZIPCODES
%1
]
sMyQry = StringBuild(sMyQry, "LIMIT 1, 10")
IF NOT HExecuteSQLQuery(QRY, hQueryDefault, sMyQry) THEN
Error(HErrorInfo())
ELSE
Info("The query contains " + HNbRec(QRY) + " records.")
END
Syntax
Running an SQL query (without connection) Hide the details
<Result> = HExecuteSQLQuery(<Query name> [, <Mode>] , <Text of SQL query>)
<Result>: Boolean - True if the SQL query was initialized,
- False otherwise. HErrorInfo returns more details about the problem.
<Query name>: Character string (with or without quotes) or data source - Name of query that will be run. This name is used to handle the result of <Text of SQL query> by programming. If a query with the same name is already declared, it is replaced by the new query.
 Versions 23 and laterName of SQL query variable containing the SQL code of 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 SQL query variable containing the SQL code of 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: | | 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 search 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 a 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 search items specified when browsing the result are taken into account.
- the magnifier is available for the tables linked to a query.
- HNbRec can be used on the queries.
| | hWithTransaction | The query is run 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). 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). Note: the query execution may be slowed down. | hModifyFile
(constant that can be combined with the other constants) | - On HFSQL data files: When modifying the query result (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be applied to the data files taking part in the query.
If this option is not specified, only the query result is modified.
See Modifying the query content for more details. - 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.
| | hQueryBreakable | The query initialization can be interrupted by the ESC key. | hQueryDefault
(default value) | The query is initialized without interruption. | | hQueryWithoutCorrection | | | hQueryWithoutHFCorrection | | | hNoBind | | Versions 21 and laterhNoHourglass New in version 21hNoHourglass hNoHourglass | When reading the query result, this read operation may be locked and a hourglass appears by default. This constant is used not to display the hourglass in this case.
| | hSQLUnicode | | | 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.
| | 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.
Note: 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.
Note: the query execution may be slowed down. |
Versions 16 and later New in version 16 <Text of SQL query>: Character string (with quotes) Text of SQL query to run. 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.
Running an SQL query via a connection Hide the details
<Result> = HExecuteSQLQuery(<Query name> [, <Connection> [, <Mode>]] , <Text of SQL query>)
<Result>: Boolean - True if the SQL query was initialized,
- False otherwise. HErrorInfo returns more details about the problem.
<Query name>: Character string (with or without quotes) or data source - Name of query that will be run. This name is used to handle the result of <Text of SQL query> by programming. If a query with the same name is already declared, it is replaced by the new query.
Versions 23 and laterName of SQL query variable containing the SQL code of 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 SQL query variable containing the SQL code of query. In this case, the <Text of SQL query> parameter must not be specified. This type of variable is not available. Name of SQL query variable containing the SQL code of 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 Name of connection used to run the query. This connection was defined in the data model editor, by programming with HDescribeConnection or HOpenConnection, or by specifying the properties of connection variable. <Result> is set to False if this parameter does not correspond to an existing connection. <Mode>: Optional Integer constant Option for initializing the query: | | 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 search 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 a 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 search items specified when browsing the result are taken into account.
- the magnifier is available for the tables linked to a query.
- HNbRec can be used on the queries.
| | hWithTransaction | The query is run 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). 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). Note: the query execution may be slowed down. | hModifyFile
(constant that can be combined with the other constants) | - On HFSQL data files: When modifying the query result (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be applied to the data files taking part in the query.
If this option is not specified, only the query result is modified.
See Modifying the query content for more details. - 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.
| | hQueryBreakable | The query initialization can be interrupted by the ESC key. | hQueryDefault
(default value) | The query is initialized without interruption. | | hQueryWithoutCorrection | | | hQueryWithoutHFCorrection | | | hNoBind | | Versions 21 and laterhNoHourglass New in version 21hNoHourglass hNoHourglass | When reading the query result, this read operation may be locked and a hourglass appears by default. This constant is used not to display the hourglass in this case.
| | hSQLUnicode | | | 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.
| | 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.
Note: 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.
Note: the query execution may be slowed down. |
Versions 16 and later New in version 16 <Text of SQL query>: Character string (with quotes) Text of SQL query to run. 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. Note: ..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 Example). The query is automatically freed when the variable is destroyed. To force the resources of this query to be freed, use: 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]
Running an SQL query with parameters An SQL query can contain parameters. To run 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 customer WHERE name={p_name}"
- 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:
<Data source variable >.<Name of Parameter1> = xxx
<Data source variable >.<Name of Parameter2> = xxx
<Data Source Variable >.<Name of Parameter3> = xxx
Example:
- Run the SQL query with HExecuteSQLQuery. Example:
HExecuteSQLQuery(MyQuery, "SELECT * FROM customer WHERE name={p_name}")
Note: The structure of query parameters is re-initialized whenever the query with parameters is run. SQL injection: How to prevent it? The SQL injection is a hacking technique ; it consists in injecting SQL code into the parameters of queries, forcing the execution of unwanted SQL code. To avoid SQL injection when running queries via HExecuteSQLQuery, you must use queries with parameters and you must not concatenate strings to build the query. Example:
// Build the query by concatenation
// -> security failure
sdQuery is Data Source
HExecuteSQLQuery(sdQuery, "SELECT * FROM customer WHERE name = '" + EDT_Name + "'")
// Using parameters in the query
// -> secure code
sdQuery is Data Source
sdQuery.p_name = EDT_Name
HExecuteSQLQuery(sdQuery, "SELECT * FROM customer WHERE name={p_name}")
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 a 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"
This page is also available for…
|
|
|
| |
| 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 |
|
|
|
| |
| |
| |
| |
| |
| |
| | |
|