ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

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
WindowsLinuxJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac Catalyst
Others
Stored procedures
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 feature: 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.
Attention: 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:
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.
AndroidAndroid Widget Java You cannot execute queries with SQL code containing WLanguage functions.
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> 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.
    PHP This type of variable is not available.
<Mode>: Optional Integer constant
Option for initializing the query:
hCheckDuplicatesEnables 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.
HFSQL Client/Server 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java JDBC access: This constant is not available.
hCheckIntegrityEnables 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.
HFSQL Client/Server 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).
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java JDBC access: This constant is not available.
hNoBind
Native Connectors (Native Accesses) SQL Server, Oracle, Sybase: Execute a query without bind enabled.
hNoHourglassWhen 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.
AndroidAndroid Widget PHP This constant is not available.
hQueryBreakableThe query initialization can be interrupted by the ESC key.
AndroidAndroid Widget PHP This constant is not available.
WEBDEV - Server codeStored procedures This constant is ignored.
Java JDBC access: This constant is not available.
HFSQL Client/Server The Esc key must be pressed on the client computer.
hQueryDefault
(default value)
The query is initialized without interruption.
hQueryWithoutCorrection
OLE DBNative Connectors (Native Accesses) 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
HFSQL Classic 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.
hSQLUnicodeUsed 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java JDBC access: This constant is not available.
hWithTransactionOnly 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
JavaPHP This constant is not available.
hYieldFor 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.
HFSQL Client/ServerOLE DBNative Connectors (Native Accesses) This constant is ignored.

AndroidAndroid Widget PHP This constant is not available.

Java JDBC access: This constant is not available.
Java Access through JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
AndroidAndroid Widget 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.
AndroidAndroid Widget Java You cannot execute queries with SQL code containing WLanguage functions.
PHP 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.
    PHP This type of variable is not available.
<Connection>: Optional character string or Connection variable
Connection used to execute the query. This connection corresponds to: <Result> is set to False if this parameter does not correspond to an existing connection.
<Mode>: Optional Integer constant
Option for initializing the query:
hCheckDuplicatesEnables 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.
HFSQL Client/Server 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java JDBC access: This constant is not available.
hCheckIntegrityEnables 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.
HFSQL Client/Server 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).
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java JDBC access: This constant is not available.
hNoBind
Native Connectors (Native Accesses) SQL Server, Oracle, Sybase: Execute a query without bind enabled.
hNoHourglassWhen 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.
AndroidAndroid Widget PHP This constant is not available.
hQueryBreakableThe query initialization can be interrupted by the ESC key.
AndroidAndroid Widget PHP This constant is not available.
WEBDEV - Server codeStored procedures This constant is ignored.
Java JDBC access: This constant is not available.
HFSQL Client/Server The Esc key must be pressed on the client computer.
hQueryDefault
(default value)
The query is initialized without interruption.
hQueryWithoutCorrection
OLE DBNative Connectors (Native Accesses) 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
HFSQL Classic 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.
hSQLUnicodeUsed 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
PHP This constant is not available.
Java JDBC access: This constant is not available.
hWithTransactionOnly 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.
AndroidAndroid Widget This constant is available for HFSQL databases only. This constant is not available for SQLite databases.
JavaPHP This constant is not available.
hYieldFor 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.
HFSQL Client/ServerOLE DBNative Connectors (Native Accesses) This constant is ignored.

AndroidAndroid Widget PHP This constant is not available.

Java JDBC access: This constant is not available.
Java Access through JDBC: This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
AndroidAndroid Widget 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.
AndroidAndroid Widget Java You cannot execute queries with SQL code containing WLanguage functions.
PHP 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:

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:
  1. 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}"
  2. Define a variable of type Data source. The name of this variable must correspond to the <Query name> parameter of HExecuteSQLQuery.
    Example:
    MyQuery is Data Source
  3. 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"
  4. Execute the SQL query with HExecuteSQLQuery. Example:
    HExecuteSQLQuery(MyQuery, "SELECT * FROM customer WHERE name={p_name}")
Note: Each time the parameterized query is run, the query parameter structure is reset.
AndroidAndroid Widget 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:
// 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.
AndroidAndroid Widget 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 a Buffer = fLoadBuffer("<chemin>\photo.jpg")

SQL Query = "INSERT INTO Product (Photo) " + ... 
		"VALUES (UNHEX('" + BufferToHexa(b, NoGrouping, NoLine)+"'))" 
HExecuteSQLQuery(data source, hQueryDefault, SQL Query)
Native Connectors (Native Accesses)

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 must be used to handle this type of join.
Remarque: These joins are managed by the HFSQL engine.
WINDEVWEBDEV - Server codeReports and QueriesUser code (UMC)AjaxOLE DBNative Connectors (Native Accesses)

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 specifiedhQueryWithoutCorrection 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.

Native Connectors (Native Accesses) 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 composed 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"
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadUser code (UMC)External languagePHPAjaxHFSQL ClassicHFSQL Client/ServerHyper File 5.5

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
    cnxBase.Provider = hAccessHFClientServer
    cnxBase.Server = "SERVEURHYPERFILESQL:4900"
    cnxBase.DatabaseBase = "Base".
    cnxBase.User = "admin"
    cnxBase.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.
WINDEVWEBDEV - Server codeReports and QueriesUser code (UMC)PHPAjaxNative Connectors (Native Accesses)

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:
  1. Declare a data source. This data source will contain the result of the SQL query.
  2. 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.
    Attention: To specify the type of a variable in the query, it is also possible to assign this variable to an existing variable of the required type.
  3. Prepare the query with HPrepareSQLQuery.
  4. 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
WINDEVWEBDEV - Server codeReports and QueriesUser code (UMC)PHPNative Connectors (Native Accesses)

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:
  1. Declare a data source. This data source will contain the result of the SQL query.
  2. 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.
    Attention: To specify the type of a variable in the query, it is also possible to assign this variable to an existing variable of the required type.
  3. 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. Warning: The ":" character must be followed by at least one letter (the: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.
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadUser code (UMC)PHPAjaxHFSQL ClassicHFSQL Client/ServerHyper File 5.5OLE DBNative Connectors (Native Accesses)

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.
HFSQL Client/Server 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, function HExecuteQuery 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.
HFSQL Client/Server 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.
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadUser code (UMC)External languagePHPAjaxHFSQL ClassicOLE DBNative Connectors (Native Accesses)

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: wd300hf.dll
Minimum version required
  • Version 9
This page is also available for…
Comments
HExecuteSQLQuery com Indirection
Dica incrível de uso de SQL

HExecuteSQLQuery("dsquery",hQueryDefault,"select * from tabcliente")

FOR EACH "dsquery"
Trace({"dsquery.tabcliente_nome"})
END


By Paulo Viana
Boller
28 Feb. 2023
Exemplo com passagem de diversos parametros
dsQuery_01 is Data Source
sdQuery_01.p_name1 = EDT_Name1
sdQuery_01.p_name2 = EDT_Name2
sdQuery_01.p_name3 = EDT_Name3
sdQuery_01.p_name4 = EDT_Name4
if HExecuteSQLQuery(dsQuery_01, "SELECT * FROM customer WHERE name={p_name1} or name={p_name2} or name={p_name3} or name={p_name4} ") = true
for each dsQuery_01
...
end
if error() or Herror()
info(HerrorInfo()+Errorinfo())
end

end
Boller
07 Feb. 2023
Example
PROCEDURE UI_CargaTablegrid(Codigo)
SqlScript is SQL Query =
[
SELECT F028_FATOR_CONVERSAO.F003_PROD_ID,
F028_FATOR_CONVERSAO.F028_QTDE_ORIGEM,
F028_FATOR_CONVERSAO.F028_UNIDADE_ORIGEM
FROM F028_FATOR_CONVERSAO
WHERE
F028_FATOR_CONVERSAO.F003_PROD_ID = {ParamF003_PROD_ID}
]
SqlScript.ParamF003_PROD_ID = Codigo
IF HExecuteSQLQuery(SqlScript) = True THEN
FOR EACH SqlScript
TableAdd(TABLEGRID_F028_FATOR_CONVERSAO,SqlScript.F028_FATOR_CONVERSAO_ID +TAB+
SqlScript.F003_PROD_ID +TAB+
SqlScript.F028_QTDE_ORIGEM)
END
END
TableDisplay(TABLEGRID_F028_FATOR_CONVERSAO,taInit)
BOLLER
27 Dec. 2019
Uso de SysDate and Dblink
To use sysdate and dblink with another database it is important to inform in the parameters: hQueryWithoutCorrection

Para usar o sysdate e fazer um dblink com outro banco de dados é importante informar nos paramentros: hQueryWithoutCorrection

Pour utiliser sysdate et dblink avec une autre base de données, il est important de renseigner les paramètres: hQueryWithoutCorrection
BOLLER
27 Sep. 2019
Exemplo HexecuteSqlQuery com WHILE HOut () = False
numero_corrida é int = 0

MyProcedureUltimaCorrida é fonte de dados

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

HReadFirst (MyProcedureUltimaCorrida, num_corrida)

ENQUANTO Hout () = False

ok = HReadNext (MyProcedureUltimaCorrida, num_corrida)

numero_corrida = MyProcedureUltimaCorrida.num_corrida

END

ELSE

Erro (HErrorInfo ())

END

info(numero_corrida)
BOLLER
29 Mar. 2019
Run Procedure MySQL
n_value_demo_number is int = 1
s_value_demo_string is string = "abc"
ds_result is Data Source
b_result is boolean
s_sql is string = "CALL pr_test ("+n_value_demo_number+","+"'"+s_value_demo_string+"')"
b_resultado = HExecuteSQLQuery(ds_result,CONNECTION_NAME_HERE,hQueryDefault+hQueryWithoutCorrection,s_sql)
IF b_resultado = False THEN
error("Error Running Procedure ",herrorinfo())
END
Willian Fernando
31 Oct. 2016

Last update: 11/23/2024

Send a report | Local help