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 by HExecuteSQLQuery. This function is available for the queries with or without bind.

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, the native access via SQLnCli may be required.

For the other databases accessed by a Native Access, 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.

Note: From version 19, HFSQL is the new name of HyperFileSQL.

Example

// Run the same query in Oracle// Declare a data source// This data source corresponds to the queryInsert1 is DataSourcei is int// Declare one of the query parameters// This parameter is an integerInsert1.age = 0// Prepare the query for multiple executionsHPrepareSQLQuery(Insert1, DatabaseConnection, ...hQueryWithoutCorrection, "INSERT INTO PERSON VALUES (:lastname,:firstname,:age)")// Loop for running the query// Only some parameters are modifiedFOR i = 1 TO 10Insert1.lastname = "LastName" + iInsert1.firstname = "FirstName" + iInsert1.age = iHExecuteSQLQuery(Insert1)END

Name of connection used to run the query. This connection was defined in the data model editor, or by using HDescribeConnection or HOpenConnection. <Result> is set to False if this parameter does not correspond to an existing connection.

<Mode>: Integer constant

hQueryWithoutCorrection

Native access only: No check is performed by the HFSQL engine on the query text.

<Text of SQL query>: Character string (with quotes)

Text of SQL query to run.

Remarks

When should I 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:

Run the query directly (with HExecuteSQLQuery) as many times as necessary while modifying the requested variables.

Prepare the query to run (HPrepareSQLQuery) as well as the different variables to modify and run the query as many times as necessary with HExecuteSQLQuery. This second solution is much faster and it optimizes the time taken to browse the result of a query (for a Select query).

SQL Server: Limit: HPrepareSQLQuery cannot be used to prepare a stored procedure that returns records. You must use HExecuteSQLQuery.

How should I use HPrepareSQLQuery?

To prepare and run a query several times, you must:

Declare a data source. This data source will contain the result of SQL query.

Declare the different variables of the query. The variables are string variables by default. Their type can be specified by using ..Type on the variable.

Prepare the query with HPrepareSQLQuery.

Specify the value of different parameters to take into account and run the query with HExecuteSQLQuery. Only the name of data source corresponding to the query must be specified.

This last step must be repeated as many times as necessary.

Notes:

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). For 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 re-used.

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):