- 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)
- Looping through 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.
- an SQL Query variable.
Then, the query result can be read. 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.
CustomerQry 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
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.
- 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.
<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 and HDelete), these changes are propagated to the data files used 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 | | | 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 looping through 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 looping through 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.
|
<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.
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.
- 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 and HDelete), these changes are propagated to the data files used 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 | | | 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 looping through 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 looping through 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.
|
<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. 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 [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 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 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:
- Execute the SQL query with HExecuteSQLQuery. Example:
HExecuteSQLQuery(MyQuery, "SELECT * FROM customer WHERE name={p_name}")
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:
// Build the query by concatenation
// -> security failure
dsQuery is Data Source
HExecuteSQLQuery(sdQuery, "SELECT * FROM customer WHERE name = '" + EDT_Name + "'")
// Using parameters in the query
// -> secure code
dsQuery is Data Source
sdQuery.p_name = EDT_Name
HExecuteSQLQuery(dsQuery, "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…
|
|
|