- Reading data during the initialization
- Text of SQL query
- Running 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
Initializes a query written in SQL language and declares this query to the HFSQL engine. This query can correspond to: - the SQL code typed in HExecuteSQLQuery.
 Versions 23 and lateran SQL Query variable.  New in version 23an SQL Query variable.
Then, the query result can be browsed. If a query with the same name already exists, this query is replaced. The result is re-calculated. Caution: To set the position on the first record of the query result, use HReadFirst, for example. To free the resources of a query typed in HExecuteSQLQuery, use: Remark: a query will not be initialized if: - the query name is already used in the current analysis (name of data file or name defined by one of the following functions: HDeclare, HDeclareExternal, HDescribeFile).
- a syntax error is found in the query.
Versions 16 and laterVersions 21 and later New in version 21 Remark: From version 19, HFSQL is the new name of HyperFileSQL. New in version 16Versions 21 and later New in version 21 Remark: From version 19, HFSQL is the new name of HyperFileSQL. Versions 21 and later New in version 21 Remark: From version 19, HFSQL is the new name of HyperFileSQL. Versions 17 and later New in version 17 Versions 18 and later New in version 18
QryCustomer is Data Source
// Initialize the "Customer" query
HExecuteSQLQuery(CustomerQry, "SELECT NAME FROM CUSTOMER")
TaskQry is Data Source
// Initialize the "TaskQry" query
// This query uses 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 an 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> [, <Mode>] , <Text of SQL query>)
<Result>: Boolean - True if the SQL query was initialized,
- False otherwise. HErrorInfo returns more details about the problem.
<Query>: 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.
<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 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.
| hModifyFile
(constant that can be combined with the other constants) | - On HFSQL data files: When modifying the result of the query (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be applied to the data files involved 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 an 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.
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.
|
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> [, <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>: 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 Connection used to run 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: | | 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.
| hModifyFile
(constant that can be combined with the other constants) | - On HFSQL data files: When modifying the result of the query (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be applied to the data files involved 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 an 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.
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.
|
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. 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 items of the query, 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 [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}")
Remark: The structure of query parameters is re-initialized whenever the query with parameters is run. 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 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. 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:
SQLQuery is string
DataSource is Data Source
b is Buffer = "Binary data including "+Charact(0)+" (binary zero)"
// or: b is Buffer = fLoadBuffer("<path>\photo.jpg")
SQLQuery = "INSERT INTO Product (Photo) " + ...
"VALUES (UNHEX('" + BufferToHexa(b, NoGrouping, NoLine)+"'))"
HExecuteSQLQuery(DataSource, hQueryDefault, SQLQuery)
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
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 |
|
|
|
| |
| |
| |
| |
| |
| |
| | |
|