HExecuteSQLQuery (Function)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / WLanguage / Managing databases / HFSQL / HFSQL functions

Reading data during the initialization
SQL query text
Executing an SQL query with parameters
SQL injection: How to protect yourself?
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.

HFSQL-specific functionality SQL queries executed on HFSQL Classic, Mobile or Client/Server databases can contain WLanguage functions. For more details, see Using a WLanguage function in an SQL query.

Please note To position yourself on the first record in the query result, use the HReadFirst function, for example.

To free the resources of a query typed in HExecuteSQLQuery, use:

the HCancelDeclaration function.
the HFreeQuery function.

Note: a query cannot be initialized if:

the query name is already used in the current analysis (data file name 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

ReqClient is Data Source
// Initialisation de la requête "Client"
HExecuteSQLQuery(ReqClient, "SELECT NOM FROM CLIENT")

ReqTache is Data Source
// Initialisation de la requête "ReqTache"
// Cette requête utilise une fonction WLangage
// Cette requête sélectionne les tâches de plus de 5 jours
HExecuteSQLQuery(ReqTache, ...
	"SELECT Tâche.Libellé FROM Tâche " +...
	"WHERE WL.DateDifférence(Tâche.DateDébut, Tâche.DateFin) > 5")

// Modification dynamique de la condition LIMIT d'une requête SQL

REQ is Data Source

sMaReq is string = [
SELECT * FROM
CODEPOSTAUX
%1
]

sMaReq = StringBuild(sMaReq, "LIMIT 1, 10")
IF NOT HExecuteSQLQuery(REQ, hQueryDefault, sMaReq) THEN
	Error(HErrorInfo())
ELSE
	Info("La requête contient " + HNbRec(REQ) + " enregistrements.")
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> programmatically. 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.
Note: the execution of the query can be significantly 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.
JDBC access: 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.
Note: the execution of the query can be significantly 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.
JDBC access: This constant is not available.
hModifyFile
(constant that can be combined with the other constants)
On HFSQL data files When modifying the query result (functions HAdd, HWrite, HModify, HCross, HDelete), these modifications will be transferred to the data files contributing to 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 non-HFSQL data filesfiles, the constant hModifiesFile is unnecessary: data files contributing to the query are automatically modified when the query result is modified.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
JDBC access: This constant is not available.
hNoBind
SQL Server, Oracle, Sybase: Execute a query without bind enabled.
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.
JDBC access: 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.).
Warning: if this constant is used:
the connection name must be specified (<Connection> parameter).
the following function cannot be used on the manipulated request: HFilter.
you cannot cancel a condition by assigning it to NULL.
it is advisable not to specify a browse item 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.
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 possible: search on the result of the query, 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.
Warning: if this option is specified, the use of the constant hModifyFile constant is used automatically.
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 result of the query, 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.
JDBC access: 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. For write requests, there is no data source: there is no need to call the HFreeQuery function.
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).
Note: the execution of the query can be significantly 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.

JDBC access: This constant is not available.
Access through 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> programmatically. 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.
Note: the execution of the query can be significantly 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.
JDBC access: 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.
Note: the execution of the query can be significantly 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.
JDBC access: This constant is not available.
hModifyFile
(constant that can be combined with the other constants)
On HFSQL data files When modifying the query result (functions HAdd, HWrite, HModify, HCross, HDelete), these modifications will be transferred to the data files contributing to 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 non-HFSQL data filesfiles, the constant hModifiesFile is unnecessary: data files contributing to the query are automatically modified when the query result is modified.
This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
This constant is not available.
JDBC access: This constant is not available.
hNoBind
SQL Server, Oracle, Sybase: Execute a query without bind enabled.
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.
JDBC access: 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.).
Warning: if this constant is used:
the connection name must be specified (<Connection> parameter).
the following function cannot be used on the manipulated request: HFilter.
you cannot cancel a condition by assigning it to NULL.
it is advisable not to specify a browse item 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.
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 possible: search on the result of the query, 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.
Warning: if this option is specified, the use of the constant hModifyFile constant is used automatically.
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 result of the query, 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.
JDBC access: 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. For write requests, there is no data source: there is no need to call the HFreeQuery function.
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).
Note: the execution of the query can be significantly 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.

JDBC access: This constant is not available.
Access through 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.

Note: The ExecutionCompleted property indicates whether the query has completed filling (and can therefore be read without blocking)..

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.

SQL query text

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 the SQL query, define the various parameters using the notation {Parameter name}. For example:
"SELECT * FROM client WHERE name={p_name}"
Define variable type Data source. The name of this variable must correspond to the <Query name> parameter of HExecuteSQLQuery.
Example:
MaRequête 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:
MaRequête.p_nom = "Dupont"

Execute the SQL query with HExecuteSQLQuery. Example:

HExecuteSQLQuery(MaRequête, "SELECT * FROM client WHERE nom={p_nom}")

Note Each time a parameterized query is run, the query parameter structure is re-initialized.

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 protect yourself?

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:

// Construction de la requête par concaténation
// -> faille de sécurité
sdReq is Data Source
HExecuteSQLQuery(sdReq, "SELECT * FROM client WHERE nom = '" + SAI_Nom + "'")

// Utilisation de paramètres dans la requête
// -> code sûr et sécurisé
sdReq is Data Source
sdReq.p_nom = SAI_Nom
HExecuteSQLQuery(sdReq, "SELECT * FROM client WHERE nom={p_nom}")

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:

RequeteSQL is string
SourceDeDonnees is Data Source

b is Buffer = "Données binaires incluant "+Charact(0)+" (zéro binaire)"
// ou : b est un Buffer = fChargeBuffer("<chemin>\photo.jpg")

RequeteSQL = "INSERT INTO Produit (Photo) " + ... 
		"VALUES (UNHEX('" + BufferToHexa(b, NoGrouping, NoLine)+"'))" 
HExecuteSQLQuery(SourceDeDonnees, hQueryDefault, RequeteSQL)

Joins

Joins of the type (A join B on x=y) join C on y=z ... are not handled automatically by Native Access: in this case, the constant hRequêteSansCorrection constant must be used to manage this type of join.

Remarque: 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.
Replacement of all PC SOFT proprietary signs (e.g. ']=' begins with) with their standard SQL equivalent.	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), use the constant combination hQueryWithoutCorrection + hSansBind constants to prevent the query from being 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 key made up of the NAME and FIRST NAME fields (NAMEFIRST NAME field):

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:

// Paramètres de la connexion
cnxBase is Connection
cnxBase.Provider = hAccessHFClientServer
cnxBase.Server = "SERVEURHYPERFILESQL:4900"
cnxBase.Database = "Base"
cnxBase.User = "admin"
cnxBase.MotDePasse = ""

// Connexion à la base de données
IF HOpenConnection(cnxBase) = False THEN
	Error(HErrorInfo())
	RETURN
END

// Déclaration dynamique du (ou des) fichier(s) utilisé(s) dans la requête
IF HDeclareExternal("CLIENT.FIC", "CLIENT", cnxBase) = False THEN
	Error(HErrorInfo())
	RETURN
END

// Exécution de la requête
sdRequeSQL is Data Source
IF NOT HExecuteSQLQuery(sdRequeSQL, cnxBase, hQueryDefault,[
SELECT * 
FROM CLIENT
]) THEN 
	Error("Problème d'exécution")
ELSE
	Info("ok")
END

// Annule la déclaration dynamique du fichier
HCancelDeclaration("CLIENT")

// Ferme la connexion à la base de données
HCloseConnection(cnxBase)

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.
Warning To specify the type of a query variable, it is also possible to assign this variable to an existing variable of the required type.
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:

// Exécution d'une même requête sous Oracle
// Déclaration d'une source de données
// Cette source de données correspond à la requête. 
Insert1 is Data Source
// Déclaration d'un des paramètres de la requête
// Le type de ce paramètre est un entier
Insert1.Age = 0
i is int
// Préparation de la requête pour de multiples exécutions
HPrepareSQLQuery(Insert1, MaConnexion, ...
	hQueryWithoutCorrection, ...
	"INSERT INTO PERSONE VALUES (:nom, :prenom, :age )")
// Boucle d'exécution de la requête
// Seuls quelques paramètres sont modifiés
FOR i = 1 TO 10
	Insert1.nom = "Nom" + i
	Insert1.prenom = "Prenom" + i
	Insert1.Age = i
	HExecuteSQLQuery(Insert1)
END

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

Note This note only applies to Oracle and SQL Server Native 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.
Warning To specify the type of a query variable, it is also possible to assign this variable to an existing variable of the required type.
Run the query with HExecuteSQLQuery.

// Exemple pour Oracle
// Déclaration de la source de données associée à la requête
MaProc is Data Source
// Déclaration des variables
MaProc.n = 3
MaProc.str = "Exemple"
// Exécution de la requête avec récupération du résultat
HExecuteSQLQuery(MaProc, MaConnexion, hQueryWithoutCorrection, ...
	"begin :Res :=sp_cut(:n,:str);end;")
// Affichage du résultat 
Info(MaProc.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. Warning: The ":" character must be followed by at least one letter (:1 syntax is forbidden).
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 duplicate error is detected when executing an UPDATE query, the HExecuteQuery function returns False and processing continues. The corresponding error is returned by HErrorInfo.

Remark: Default automation support is not called for 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(MaRequête, 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("MaRequête", "MaRubrique", hNoRefresh)
```
The created index is used to perform searches on the query result.

Business / UI classification: Business Logic

Component: wd300hf.dll