ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / HFSQL functions
  • When to use HPrepareSQLQuery?
  • How to use HPrepareSQLQuery?
  • Condition on a composite key in an SQL query
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
HPrepareSQLQuery (Function)
In french: HPrépareRequêteSQL
Native Connectors (Native Accesses)Available only with this kind of connection
Initializes a query written in SQL language and declares this query to the database server in order to optimize the next executions of this query. This query is not run. Then, the query can be run with HExecuteSQLQuery. This function is available for queries with or without bind.
To free the resources of this query, use HCancelDeclaration.
We recommend that you use this function when the same query is run successively and when only some of the query parameters are modified at each execution.
This function is optional and it can only be used on the Client/Server databases (available for Oracle, Oracle Lite, Sybase and SQL Server). In SQL Server, Native Connector via OLE DB or ODBC may be required.
For the other databases accessed by a Native Connector, OLEDB or ODBC, HPrepareSQLQuery runs the query.
This function cannot be used on the HFSQL databases (HFSQL Classic or Client/Server) and on the xBase databases.
Example
// Run the same query in Oracle
// Declare a data source
// This data source corresponds to the query
Insert1 is Data Source
i is int
// Declare one of the query parameters
// This parameter is an integer
Insert1.Age = 0
// Prepare the query for multiple executions
HPrepareSQLQuery(Insert1, DatabaseConnection, ...
hQueryWithoutCorrection, "INSERT INTO PERSON VALUES (:lastname,:firstname,:age)")
// Loop for running the query
// Only some parameters are modified
FOR i = 1 TO 10
Insert1.LastName = "LastName" + i
Insert1.FirstName = "FirstName" + i
Insert1.Age = i
HExecuteSQLQuery(Insert1)
END
Syntax
<Result> = HPrepareSQLQuery(<Data source> , <Connection> , <Mode> , <SQL query text>)
<Result>: Boolean
  • True if no problem occurred,
  • False otherwise. HErrorInfo returns more details about the problem.
<Data source>: Data source
Name of the Data Source variable that corresponds to the request to initialize.
<Connection>: Character string or Connection variable
Name of 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>: Integer constant
hQueryWithoutCorrectionNative Connector only: No check is performed by the HFSQL engine on the query text.
<SQL query text>: Character string
Text of the SQL query to execute.
Remarks

When to use HPrepareSQLQuery?

In some cases, it may be interesting to run the same query several times while modifying one or more variables. For example, you may want to run an Insert query several times to add several records into a file.
Several solutions can be implemented:
  1. Execute the query directly (with HExecuteSQLQuery) as many times as necessary, and modify the desired variable(s) each time.
  2. Prepare the query to be executed (HPrepareSQLQuery) as well as the different variables to be modified. Then, execute the query as many times as necessary with HExecuteSQLQuery. This solution is much faster and optimizes the time it takes to loop through the result of a query (Select query).
Native Connectors (Native Accesses) SQL Server: Limit: HPrepareSQLQuery cannot be used to prepare a stored procedure that returns records. You must use HExecuteSQLQuery.

How to use HPrepareSQLQuery?

To prepare and execute a query multiple times:
  1. Declare a data source. This data source will contain the result of the SQL query.
  2. Declare the different variables of the query.
    The variables are string variables by default. You can specify their type by using the Type property with the variable.
  3. Prepare the query with HPrepareSQLQuery.
  4. Specify the value of the different parameters to take into account and run the query with HExecuteSQLQuery. Only the name of the data source that corresponds to the query must be specified.
This last step must be repeated as many times as necessary.
Remarks:
  • HPrepareSQLQuery must be used with:
    • the connection name,
    • the hQueryWithoutCorrection constant.
  • The declared variables must be identical to the ones used. Otherwise, a WLanguage error occurs.
  • In the call to the stored procedure, you must use the syntax specific to the database used, including for the syntax of parameters.
    Therefore, for Oracle, the parameters are specified with the :ParamName syntax. Caution: the ":" character must be followed by at least one letter (the syntax:1 is not allowed).
    In SQL Server, the parameters are specified via the following notation: @ParamName.
    The same parameter can be used several times. In this case, the corresponding variable will be reused.

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 AFile.AnItem, AFile.AnItem1
FROM AFile
WHERE AFile.LastName = "Clark" AND AFile.FirstName = "Vince"
Component: wd290hf.dll
Minimum version required
  • Version 9
This page is also available for…
Comments
Example HexecuteSqlQuery with WHILE HOut() = False
numero_corrida is int = 0

MyProcedureUltimaCorrida is Data Source

IF HExecuteSQLQuery(MyProcedureUltimaCorrida, ConnNativa, hQueryWithoutCorrection,"NG0002_Procedure_UltimaCorrida") THEN

HReadFirst(MyProcedureUltimaCorrida, num_corrida)

WHILE HOut() = False

ok = HReadNext(MyProcedureUltimaCorrida, num_corrida)

numero_corrida = MyProcedureUltimaCorrida.num_corrida

END

ELSE

Error(HErrorInfo())

END

Trace(numero_corrida)
BOLLER
29 Mar. 2019

Last update: 09/07/2023

Send a report | Local help