HExecuteSQLQuery (Function)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Help / WLanguage / Managing databases / HFSQL / HFSQL functions

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)

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

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.

Feature specific to HFSQL: The SQL queries run on an HFSQL Classic, Mobile or Client/Server database can contain WLanguage functions. For more details, see Using a WLanguage function in an SQL query.

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:

the HCancelDeclaration function.
the HFreeQuery function.

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.

You cannot execute queries with SQL code containing WLanguage functions.

Example

See additional examples

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.
The management of duplicates is disabled if the hCheckDuplicates constant is not specified. An error occurs if the user has no rights to disable the management of duplicates (HModifyDatabaseRights) and if the management of duplicates must be performed.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
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.
The integrity management is disabled if the hCheckIntegrity constant is not specified. However, an error occurs if an integrity management operation must be performed, but the user does not have the rights to disable integrity management (HModifyDatabaseRights).
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
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.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
hNoBind
SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
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.
This constant is not available.
hQueryBreakable The query initialization can be interrupted by the ESC key.
This constant is not available.
This constant is ignored.
Access by JDBC: This constant is not available.
The Esc key must be pressed on the client computer.
hQueryDefault
(default value) The query is initialized without interruption.
hQueryWithoutCorrection
No check is performed by the HFSQL engine. This option must be used if the query contains commands specific to a connection type (Oracle, SQL Server, etc.).
Caution: if this constant is used:
the connection name must be specified (<Connection> parameter).
the following function cannot be used on the query: HFilter.
you cannot cancel a condition by assigning it to NULL.
no browse item should be specified in the following functions: HLast, HReadLast, HReadFirst, HFirst, HSeekLast, HSeekFirst.
hQueryWithoutHFCorrection
The file format (filled with space characters or not) is not checked by the HFSQL engine. To be used if the query handles both HFSQL data files in a format that completes items with space characters and HFSQL data files in a format that does not complete items with space characters.
This constant is not available.
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.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
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.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
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.
This constant is ignored.

This constant is not available.

Access by JDBC: This constant is not available.
Access by JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
This parameter can only be set to hQueryDefault. An error is generated if another constant is used.

<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.
You cannot execute queries with SQL code containing WLanguage functions.
This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
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:
a connection defined in the data model editor or through programming with HDescribeConnection or HOpenConnection.
a variable of type Connection.
<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.
The management of duplicates is disabled if the hCheckDuplicates constant is not specified. An error occurs if the user has no rights to disable the management of duplicates (HModifyDatabaseRights) and if the management of duplicates must be performed.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
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.
The integrity management is disabled if the hCheckIntegrity constant is not specified. However, an error occurs if an integrity management operation must be performed, but the user does not have the rights to disable integrity management (HModifyDatabaseRights).
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
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.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
hNoBind
SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
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.
This constant is not available.
hQueryBreakable The query initialization can be interrupted by the ESC key.
This constant is not available.
This constant is ignored.
Access by JDBC: This constant is not available.
The Esc key must be pressed on the client computer.
hQueryDefault
(default value) The query is initialized without interruption.
hQueryWithoutCorrection
No check is performed by the HFSQL engine. This option must be used if the query contains commands specific to a connection type (Oracle, SQL Server, etc.).
Caution: if this constant is used:
the connection name must be specified (<Connection> parameter).
the following function cannot be used on the query: HFilter.
you cannot cancel a condition by assigning it to NULL.
no browse item should be specified in the following functions: HLast, HReadLast, HReadFirst, HFirst, HSeekLast, HSeekFirst.
hQueryWithoutHFCorrection
The file format (filled with space characters or not) is not checked by the HFSQL engine. To be used if the query handles both HFSQL data files in a format that completes items with space characters and HFSQL data files in a format that does not complete items with space characters.
This constant is not available.
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.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
Access by JDBC: This constant is not available.
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.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
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.
This constant is ignored.

This constant is not available.

Access by JDBC: This constant is not available.
Access by JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
This parameter can only be set to hQueryDefault. An error is generated if another constant is used.

<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.
You cannot execute queries with SQL code containing WLanguage functions.
This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
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:

the HCancelDeclaration function.
the HFreeQuery function.

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:
MyQuery is Data Source
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:
MyQuery.p_name = "Doe"
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.

Queries with parameters are not available. However, 'SQL query' variables are available. This type of variable can correspond to a query with parameters. For more details, see SQL query variable.

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.

Queries with parameters are not available. However, 'SQL query' variables are available. This type of variable can correspond to a query with parameters. For more details, see SQL query variable.

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)

Joins

The joins such as (A join B on x=y) join C on y=z ... are not automatically supported by Native Accesses: in this case, the hQueryWithoutCorrection constant must be used to manage this type of join.

Remark: These joins are managed by the HFSQL engine.

Why should the hQueryWithoutCorrection constant be used?

By default, WINDEV and WEBDEV interpret the SQL queries:

built via a Native Access,
built on OLEDB and on ODBC via the OLE DB provider.

In order for the query not to be interpreted, use the hQueryWithoutCorrection constant.

hQueryWithoutCorrection is not specified	hQueryWithoutCorrection is specified

The connection associated with the data files in the query is defined automatically.	The connection to use must be specified in HExecuteSQLQuery.
All PC SOFT proprietary signs are replaced (e.g.: ']=' starts with) with their equivalent in standard SQL.	No replacement is performed. The standard SQL symbols must be used.
Format the dates and times according to the format used by the database. For example, the dates are in 'YYYYMMDD' format in WINDEV and WEBDEV while in Access, the dates are in #YYYYDDMM# or #YYYYMMDD# format depending on the system language.	No formatting is performed. The format recognized by the database must be used.
Floats are formatted (the decimal separator can be '.' or ',')	No formatting is performed for the floats.
Depending on the database used, the alias names are replaced with the full names of the items in Where, Order by and Group by For example, the JET engine (Access, dBase, etc.) accepts no alias name in the Where clause of a query	No replacement is performed. The full names of items must be used in the query code for Where, Order by and Group by.

Special case: Oracle and SQL Server: If the query to be executed contains a script with ":param" (Oracle) or "@param" (SQL Server), you must use the hQueryWithoutCorrection + hNoBind constants in order for the query not to be interpreted.

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"

Queries on HFSQL data files

To optimize the query execution time, use HOptimize or HOptimizeQuery on the different data files included in the query.
To optimize query processing time, use HStatCalculate or HIndex on all data files included the query.
When running an INSERT query on HFSQL data files, the following syntax allows you to find out the automatic identifier assigned to the new record: <File name>.<Name of automatic identifier Item>.
If the data files used by the query are not found in the analysis, these files must be dynamically declared by HDeclareExternal. Otherwise, an error such as Unable to initialize the query. Unknown FILENAME will occur.
Example:
// Connection parameters
cntDatabase is Connection
cntDatabase.Provider = hAccessHFClientServer
cntDatabase.Server = "HYPERFILESQLSERVER:4900"
cntDatabase.Database = "Database"
cntDatabase.User = "admin"
cntDatabase.Password = ""

// Connect to the database
IF HOpenConnection(cntDatabase) = False THEN
Error(HErrorInfo())
RETURN
END

// Dynamic declaration of the files used in the query
IF HDeclareExternal("CUSTOMER.FIC", "CUSTOMER", cntDatabase) = False THEN
Error(HErrorInfo())
RETURN
END

// Execute query
dsSQLQuery is Data Source
IF NOT HExecuteSQLQuery(dsSQLQuery, cntDatabase, hQueryDefault,[
SELECT *
FROM CUSTOMER
]) THEN
Error("Runtime problem")
ELSE
Info("ok")
END

// Cancels the dynamic file declaration
HCancelDeclaration("CUSTOMER")

// Closes the connection to the database
HCloseConnection(cntDatabase)

When running an INSERT/UPDATE/DELETE query on HFSQL data files, the number of added, modified or deleted records is returned by the H.NbRecModificationQuery variable.

Optimization for running the same query several times (native Oracle and SQL Server accesses only)

To optimize the execution of a query run several times, you can:

Declare a data source. This data source will contain the result of the SQL query.
Declare the different parameters or variables of the query.
The parameters are string parameters by default. You can specify their type with the Type property.
Caution: To specify the type of a query variable, you can also assign an existing variable of the desired type to this 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 the data source that corresponds to the query must be specified.

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

Example:

// Run the same query in Oracle
// Declare a data source
// This data source corresponds to the query.
Insert1 is Data Source
// Declare one of the query parameters
// This parameter is an integer
Insert1.age = 0
i is int
// Prepare the query for multiple executions
HPrepareSQLQuery(Insert1, MyConnection, ...
hQueryWithoutCorrection, ...
"INSERT INTO PERSONE 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

Specifying and retrieving values when running a query (stored procedures)

Remark: This note applies only to Native Oracle and SQL Server Connectors.

To specify and retrieve values when running a query, you must:

Declare a data source. This data source will contain the result of the SQL query.
Declare the different query variables (input and output variables).
The variables are string variables by default. You can specify their type with the Type property.
Caution: To specify the type of a query variable, you can also assign an existing variable of the desired type to this variable.
Run the query with HExecuteSQLQuery.

// Example for Oracle
// Declare the data source associated with the query
MyProc is Data Source
// Declare the variables
MyProc.n = 3
MyProc.str = "Example"
// Run the query and retrieve the result
HExecuteSQLQuery(MyProc, MyConnection, hQueryWithoutCorrection, ...
"begin:Res:=sp_cut(:n,:str);end;")
// Display the result
Info(MyProc.Res)

Remarks

HExecuteSQLQuery must be used with:
- the connection name,
- the hQueryWithoutCorrection constant.
The variables declared in the query must be identical to the ones used in the code of stored procedure. 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 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.
To execute a query without enabling the bind option, use the hNoBind constant in HExecuteSQLQuery.

SQL queries (HExecuteSQLQuery or execution of SQL queries created in the query editor)

When using the DELETE, INSERT or UPDATE SQL statements in SQL queries, no integrity check and no duplicate check are performed by default on an HFSQL database.

To perform an automatic integrity check, simply specify the hCheckIntegrity constant. This constant is used to enable the management of integrity during the query execution. The query is not run if an integrity error occurs. Indeed, a transaction is started, the records are locked in write mode 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.

The integrity management is disabled if the hCheckIntegrity constant is not specified. However, an error occurs if an integrity management operation must be performed, but the user does not have the rights to disable integrity management (HModifyDatabaseRights).

To perform an automatic check for duplicates, simply specify the hCheckDuplicates constant. This constant is used to enable the management of duplicates during the query execution. The query is not run if a duplicate error occurs. Indeed, a transaction is started, the records are locked in write mode 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.

Note: If a duplicates error occurs when running an UPDATE query, HExecuteQuery returns False and the process continues. The corresponding error is returned by HErrorInfo.

Remark: The default automatic assistance is not called for the duplicate errors. A simple duplicate error is generated. If an assistance was redefined by HOnError, then this custom assistance is called.

The management of duplicates is disabled if the hCheckDuplicates constant is not specified. An error occurs if a duplicate management operation must be performed, but the user has no rights to disable the management of duplicates (HModifyDatabaseRights).

The UPDATE, DELETE and INSERT queries only exist during their execution. No WLanguage function can be used on this type of query after its execution.

Looping through queries (HReadFirst)

By default, HReadFirst re-runs the query to refresh the result. It is recommended to use the hNoRefresh constant to avoid re-running the query.
Looping through a query executed with the hQueryWithoutCorrection option:
To browse the records in the order returned by the database, there is no need to specify a browse item. Example:
HReadFirst(MyQuery, hNoRefresh)

If a browse item is specified, the query result is entirely retrieved and indexed. The iteration is performed on the specified item. The initial sort of the query (specified by ORDER BY) is ignored. The created index (in HFSQL format) is sensitive to the case, to the punctuation, to the accented characters and in ascending order.
Example:
HReadFirst("MyQuery", "MyItem", hNoRefresh)

The created index is used to perform searches on the query result.

Business / UI classification: Business Logic

Component: wd280hf.dll