- Reading data during query initialization
- Adding records and using the hModifyFile constant
- Why should the hQueryWithoutCorrection constant be used
- Passing parameters to the query
- <Source>.ExecuteQuery and access via JDBC
- Freeing the resources of the query
- Optimizing the queries on HFSQL data files
- Running a query and Table control based on this query
- Executing a query and a report based on this query
- SQL queries (<Source>.ExecuteQuery or execution of SQL queries created in the query editor)
<Source>.ExecuteQuery (Function) In french: <Source>.ExécuteRequête
Not available with this kind of connection
Declares a query to the HFSQL engine and runs this query. This query can correspond to: - a query created in the query editor.
- 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 recalculated. Caution: To set the position on the first record of the query result, use <Source>.ReadFirst, for example. To free the resources of a query created in the query editor, use: Remark: A query will not be initialized if: // Initialize the "QRY_Customer_84" query QRY_Customer_84.ExecuteQuery() // Query on Oracle file: Customer uses the "MyConnection" connection // QRY_MyQuery = "Select * from customers where CustomerID =?" QRY_MyQuery.ExecuteQuery(hQueryDefault, 5) Syntax
Executing a query (without connection) Hide the details
<Result> = <Source>.ExecuteQuery([<Mode> [, <Parameter 1> [, <Parameter N>]]])
<Result>: boolean - True if the query was initialized,
- False otherwise. HErrorInfo returns more details about the problem.
<Source>: Type corresponding to the source Name of query to be executed: - Logical name of the query to be initialized.
If a query with the same name is already declared, it is replaced with the new query. - Name of a variable of type SQL query.
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 (option that can be combined with the other constants) | On HFSQL data files: When the result of the query is modified (<Source>.Add, <Source>.Write, <Source>.Modify, <Source>.Cross and <Source>.Delete), 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.
| 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. Remark: This constant can only be used with a selection query (SELECT).. Other query types (INSERT, UPDATE, DELETE, etc.) are not interruptible.. | hQueryDefault (default value) | The query is initialized without interruption. | hQueryWithoutCorrection | | hQueryWithoutHFCorrection | | hWithFilter (option 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.
- <Source>.NbRec cannot be used on the queries.
Caution: the hModifyFile constant is automatically used if this option is specified. 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.
- <Source>.NbRec 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 the query execution, regardless of the query result. If the query cannot end properly (locked records, power failure, etc.), 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.
|
<Parameter 1>: Type of parameter Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma. Remarks:- Passing parameters in <Source>.ExecuteQuery is kept for backward compatibility. It is recommended to pass parameters using the following notation: <Query name>.<Parameter name> = <Value>.
- To specify the query parameters, the <Mode> parameter must be specified.
- The value of each parameter can be specified or not.
- You can specify a list of values or a list of parameters.
For more details, see Passing parameters.
This parameter is not available if <Query name> corresponds to an SQL Query variable. <Parameter N>: Type of parameter Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma. Remarks:- Passing parameters in <Source>.ExecuteQuery is kept for backward compatibility. It is recommended to pass parameters using the following notation: <Query name>.<Parameter name> = <Value>.
- To specify the query parameters, the <Mode> parameter must be specified.
- The value of each parameter can be specified or not.
- You can specify a list of values or a list of parameters.
For more details, see Passing parameters.
This parameter is not available if <Query name> corresponds to an SQL Query variable. Remarks Reading data during query initialization Depending on the query (sorts, groups, etc.), the data can be read (or not) when 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). Passing parameters to the query Several methods can b e used to pass parameters to a query: - Passing parameters with the following notation: <Query name>.<Parameter name> = <Value> (recommended)
- Passing parameters in HExecuteQuery (kept for backward compatibility, compatible with PHP).
Let's present these two methods: - Passing parameters with the following notation: <Query name>.<Parameter name> = <Value> (recommended)
To pass parameters to a query with parameters before executing it, you can also use the following syntax:
<Nom de la requête>.<Nom du paramètre 1> = xxx <Nom de la requête>.<Nom du paramètre 2> = xxx <Nom de la requête>.<Nom du paramètre 3> = xxx <Nom de la requête>.ExécuteRequête() SI ErreurDétectée = Vrai ALORS ... Remarks:- This syntax makes it easier to pass parameters. Indeed, if the parameters are directly passed to <Source>.ExecuteQuery, the order of parameters must be complied with.
- The structure of the query parameters is reset each time the query is executed.
This method is not available.
- Passing parameters in HExecuteQuery (kept for backward compatibility)
In this case, the values of query parameters must be specified in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma. The value of each parameter can be specified or not. A parameter can be ignored by using the Null value. In this case, the selection conditions that uses this parameter will be ignored.
You can specify a list of values or a list of parameters. If the selection condition for one of the query parameters is "is included in the list" or "is not included in the list", you must specify a list of values or parameters. To specify a list of values or a list of parameters, the different values or parameters must be separated by a semi-colon (';'), a carriage return ('CR') or a tabulation ('TAB'). For example:
QueryName.ExecuteQuery(hQueryDefault, "34000;34200;34400") ouQueryName.ExecuteQuery(hQueryDefault, "34000 "+ CR + "34200" + CR + "34400") ouQueryName.ExecuteQuery(hQueryDefault, "34000" + TAB + "34200" + TAB + "34400") Remark: If one of the values or parameters contains a semi-colon, a carriage return or a tabulation, this value or parameter must be enclosed in quotes. For example:
QueryName.ExecuteQuery(hQueryDefault, "'A;A';'A;B';'A;C'") Remark: All the query parameters do not necessarily have to be specified. The query conditions that use unspecified parameters will be ignored. Example: Let's consider the query named "Customer_LastName_FirstName" whose SQL code is as follows:
SELECT * FROM CLIENT WHERE NOM = {Param1} AND PRENOM = {Param2} - The 2 parameters are specified:
QRY_Customers_LastName_FirstName.ExecuteQuery(hQueryDefault, "Smith", "John") will execute the querySELECT * FROM CLIENT WHERE NOM = 'Dupond' AND PRENOM = 'Jean' - Only the name is given:
QRY_Customers_LastName_FirstName.ExecuteQuery(hQueryDefault, "Smith") will execute the querySELECT * FROM CLIENT WHERE NOM = 'Dupond' - Only the first name is specified:
sLastName is Variant = Null // Use the mandatory variant for the Null variable (not filled) Â QRY_Customers_LastName_FirstName.ExecuteQuery(hQueryDefault, sName, "John") Â OR Â QRY_Customers_LastName_FirstName.ExecuteQuery(hQueryDefault, Null, "John") will execute the requestSELECT * FROM CLIENT WHERE PRENOM = 'Jean'
Freeing the resources of the query To free the resources of a query, use: If this operation is not performed, the query is still found in the current HFSQL context and it can be accessed from any process. Remarks: - The query resources are not automatically released when the window that executed the query is closed (via <Source>.ExecuteQuery).
- The SQL Query variables are automatically freed at the end of variable scope. For more details, see SQL queries in WLanguage.
This page is also available for…
|
|
|