ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

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
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
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:
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.
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.
Remark: the query execution may be 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 Access by JDBC: 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.
Remark: the query execution may be 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 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.
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 Access by JDBC: This constant is not available.
hNoBind
Native Connectors (Native Accesses) SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
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.
Universal Windows 10 AppAndroidAndroid Widget PHP This constant is not available.
WEBDEV - Server codeStored procedures This constant is ignored.
Java Access by JDBC: 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.).
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
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.
Universal Windows 10 App This constant is not available.
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 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.
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 Access by JDBC: 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. 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.
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 Access by JDBC: This constant is not available.
Java Access by 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.
Remark: the query execution may be 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 Access by JDBC: 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.
Remark: the query execution may be 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 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.
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 Access by JDBC: This constant is not available.
hNoBind
Native Connectors (Native Accesses) SQL Server, Oracle, Sybase: Used to execute a query without enabling the bind option.
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.
Universal Windows 10 AppAndroidAndroid Widget PHP This constant is not available.
WEBDEV - Server codeStored procedures This constant is ignored.
Java Access by JDBC: 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.).
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
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.
Universal Windows 10 App This constant is not available.
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 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.
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 Access by JDBC: 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. 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.
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 Access by JDBC: This constant is not available.
Java Access by 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.
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:

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:
  1. 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}"
  2. Define a Data source variable. 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}")
Remark: The structure of the query parameters is reset each time the query is executed.
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 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.
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 Buffer = fLoadBuffer("<path>\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

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

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), 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"
WINDEVWEBDEV - Server codeReports and QueriesUniversal Windows 10 AppiPhone/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
    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.
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.
    Caution: To specify the type of a query variable, you can also assign an existing variable of the desired type to this variable.
  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)

Remark: This note applies only to Native Oracle and SQL Server 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.
    Caution: To specify the type of a query variable, you can also assign an existing variable of the desired type to this variable.
  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. 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.
WINDEVWEBDEV - Server codeReports and QueriesUniversal Windows 10 AppiPhone/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 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.
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 QueriesUniversal Windows 10 AppiPhone/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: 06/25/2024

Send a report | Local help