PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

  • Reading data during the initialization
  • Text of SQL query
  • Running an SQL query with parameters
  • SQL injection: How to prevent it?
  • 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)
  • Browsing queries (HReadFirst)
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
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.
  • Versions 23 and later
    an SQL Query variable.
    New in version 23
    an SQL Query variable.
    an SQL Query variable.
Then, the query result can be browsed. 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 a HFSQL Classic, Mobile or Client/Server database can contain WLanguage functions. See Using a WLanguage function in an SQL query for more details.
Caution: To position on the first record of query result, use HReadFirst for example.
To free the resources of a query typed in HExecuteSQLQuery, use:
Note: 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.
Versions 16 and later
Java This function is now available for the Java applications.
Android This function is now available for the Android applications.
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
Versions 21 and later
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
New in version 21
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
New in version 16
Java This function is now available for the Java applications.
Android This function is now available for the Android applications.
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
Versions 21 and later
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
New in version 21
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Java This function is now available for the Java applications.
Android This function is now available for the Android applications.
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
Versions 21 and later
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
New in version 21
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Versions 17 and later
iPhone/iPad This function is now available for the iPhone/iPad applications.
New in version 17
iPhone/iPad This function is now available for the iPhone/iPad applications.
iPhone/iPad This function is now available for the iPhone/iPad applications.
Versions 18 and later
Android Widget This function is now available in Android Widget mode.
Universal Windows 10 App This function is now available in Windows Store apps mode.
New in version 18
Android Widget This function is now available in Android Widget mode.
Universal Windows 10 App This function is now available in Windows Store apps mode.
Android Widget This function is now available in Android Widget mode.
Universal Windows 10 App This function is now available in Windows Store apps mode.
CustomerQry is Data Source
// Initialize the "Customer" query
HExecuteSQLQuery(CustomerQry, "SELECT NAME FROM CUSTOMER")
TaskQry is Data Source
// Initialize the "TaskQry" query
// This query is using 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 a 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

Running an SQL query (without connection) Hide the details

<Result> = HExecuteSQLQuery(<Query Name> [, <Mode>] , <Text of SQL Query>)
<Result>: Boolean
  • True if the SQL query was initialized,
  • False otherwise. HErrorInfo returns more details about the problem.
<Query Name>: Character string (with or without quotes) or data source
  • Name of query that will be run. This name is used to handle the result of <Text of SQL Query> by programming. If a query with the same name is already declared, it is replaced by the new query.
  • Versions 23 and later
    Name of SQL query variable containing the SQL code of query. In this case, the <Text of Query in SQL> parameter must not be specified.
    PHP This type of variable is not available.
    New in version 23
    Name of SQL query variable containing the SQL code of query. In this case, the <Text of Query in SQL> parameter must not be specified.
    PHP This type of variable is not available.
    Name of SQL query variable containing the SQL code of query. In this case, the <Text of Query in SQL> parameter must not be specified.
    PHP This type of variable is not available.
<Mode>: Optional Integer constant
Option for initializing the query:
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 a HFSQL view.
  • the following operations are not allowed: search on the query result, query on query, view on query, query on view.
  • the search items specified when browsing 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 a 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 search items specified when browsing the result are taken into account.
  • the magnifier is available for the tables linked to a query.
  • hNbRec can be used on the queries.
AndroidAndroid Widget PHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
PHP This constant is not available.
Java Access by JDBC: This constant is not available.
hWithTransactionThe query is run 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).
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 query execution may be slowed down.
AndroidAndroid Widget iPhone/iPadWindows MobileJavaPHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
iPhone/iPadWindows MobileJavaPHP This constant is not available.
hModifyFile
(constant that can be combined with the other constants)
  • On HFSQL data files: When modifying the query result (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be applied to the data files taking part in the query.
    If this option is not specified, only the query result is modified.
    See Modifying the query content for more details.
  • 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 PHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
PHP This constant is not available.
Java Access by JDBC: This constant is not available.
hQueryBreakableThe query initialization can be interrupted by the ESC key.
AndroidAndroid Widget Universal Windows 10 AppPHP 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 used on the client computer.
hQueryDefault
(default value)
The query is initialized without interruption.
hQueryWithoutCorrection
OLE DBNative Accesses No check is performed by the HFSQL engine. This option must be used if the query contains commands specific to a type of connection (Oracle, SQL Server, ...).
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 search item should be specified in the following functions: HLast, HReadLast, HReadFirst, HFirst, HSeekLast, HSeekFirst.
hQueryWithoutHFCorrection
HFSQL 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.
hNoBind
Native Accesses SQL Server, Oracle, Sybase: Used to run a query without enabling the bind.
Versions 21 and later
hNoHourglass
New in version 21
hNoHourglass
hNoHourglass
When reading the query result, this read operation may be locked and a hourglass appears by default. This constant is used not to display the hourglass in this case.
AndroidAndroid Widget PHP This constant is not available.
hSQLUnicode
Windows Mobile Used to specify that the query text must be sent in Unicode format to the server.
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 Accesses This constant is ignored.

AndroidAndroid Widget PHP This constant is not available.

Java Access by JDBC: This constant is not available.
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 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 PHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
Windows MobilePHP 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.
Note: the query execution may be slowed down.
HFSQL Client/Server The integrity management is disabled if the hCheckIntegrity constant is not specified. An error occurs if the user has no rights to disable the integrity management (HModifyDatabaseRights) and if the integrity management must be performed.
AndroidAndroid Widget Windows MobilePHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
Windows MobilePHP This constant is not available.
Java Access by JDBC: This constant is not available.
Versions 16 and later
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.
New in version 16
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.
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.
<Text of SQL Query>: Character string (with quotes)
Text of SQL query to run. This text can correspond to a character string in ANSI format or in Unicode format.
Versions 16 and later
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
New in version 16
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
PHP This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
Versions 23 and later
This parameter is not available if <Query Name> corresponds to an SQL Query variable.
New in version 23
This parameter is not available if <Query Name> corresponds to an SQL Query variable.
This parameter is not available if <Query Name> corresponds to an SQL Query variable.

Running an SQL query via a connection Hide the details

<Result> = HExecuteSQLQuery(<Query Name> [, <Connection> [, <Mode>]] , <Text of SQL Query>)
<Result>: Boolean
  • True if the SQL query was initialized,
  • False otherwise. HErrorInfo returns more details about the problem.
<Query Name>: Character string (with or without quotes) or data source
  • Name of query that will be run. This name is used to handle the result of <Text of SQL Query> by programming. If a query with the same name is already declared, it is replaced by the new query.
  • Versions 23 and later
    Name of SQL query variable containing the SQL code of query. In this case, the <Text of Query in SQL> parameter must not be specified.
    PHP This type of variable is not available.
    New in version 23
    Name of SQL query variable containing the SQL code of query. In this case, the <Text of Query in SQL> parameter must not be specified.
    PHP This type of variable is not available.
    Name of SQL query variable containing the SQL code of query. In this case, the <Text of Query in SQL> parameter must not be specified.
    PHP This type of variable is not available.
<Connection>: Optional character string or connection variable
Name of connection used to run the query. This connection was defined in the data model editor, by programming with HDescribeConnection or HOpenConnection, or by specifying the properties of connection variable. <Result> is set to False if this parameter does not correspond to an existing connection.
<Mode>: Optional Integer constant
Option for initializing the query:
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 a HFSQL view.
  • the following operations are not allowed: search on the query result, query on query, view on query, query on view.
  • the search items specified when browsing 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 a 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 search items specified when browsing the result are taken into account.
  • the magnifier is available for the tables linked to a query.
  • hNbRec can be used on the queries.
AndroidAndroid Widget PHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
PHP This constant is not available.
Java Access by JDBC: This constant is not available.
hWithTransactionThe query is run 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).
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 query execution may be slowed down.
AndroidAndroid Widget iPhone/iPadWindows MobileJavaPHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
iPhone/iPadWindows MobileJavaPHP This constant is not available.
hModifyFile
(constant that can be combined with the other constants)
  • On HFSQL data files: When modifying the query result (HAdd, HWrite, HModify, HCross, HDelete), these modifications will be applied to the data files taking part in the query.
    If this option is not specified, only the query result is modified.
    See Modifying the query content for more details.
  • 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 PHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
PHP This constant is not available.
Java Access by JDBC: This constant is not available.
hQueryBreakableThe query initialization can be interrupted by the ESC key.
AndroidAndroid Widget Universal Windows 10 AppPHP 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 used on the client computer.
hQueryDefault
(default value)
The query is initialized without interruption.
hQueryWithoutCorrection
OLE DBNative Accesses No check is performed by the HFSQL engine. This option must be used if the query contains commands specific to a type of connection (Oracle, SQL Server, ...).
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 search item should be specified in the following functions: HLast, HReadLast, HReadFirst, HFirst, HSeekLast, HSeekFirst.
hQueryWithoutHFCorrection
HFSQL 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.
hNoBind
Native Accesses SQL Server, Oracle, Sybase: Used to run a query without enabling the bind.
Versions 21 and later
hNoHourglass
New in version 21
hNoHourglass
hNoHourglass
When reading the query result, this read operation may be locked and a hourglass appears by default. This constant is used not to display the hourglass in this case.
AndroidAndroid Widget PHP This constant is not available.
hSQLUnicode
Windows Mobile Used to specify that the query text must be sent in Unicode format to the server.
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 Accesses This constant is ignored.

AndroidAndroid Widget PHP This constant is not available.

Java Access by JDBC: This constant is not available.
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 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 PHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
Windows MobilePHP 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.
Note: the query execution may be slowed down.
HFSQL Client/Server The integrity management is disabled if the hCheckIntegrity constant is not specified. An error occurs if the user has no rights to disable the integrity management (HModifyDatabaseRights) and if the integrity management must be performed.
AndroidAndroid Widget Windows MobilePHP This constant is not available.
AndroidAndroid Widget This constant is available for the HFSQL databases only. This constant is not available for the SQLite databases.
Windows MobilePHP This constant is not available.
Java Access by JDBC: This constant is not available.
Versions 16 and later
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.
New in version 16
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.
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.
<Text of SQL Query>: Character string (with quotes)
Text of SQL query to run. This text can correspond to a character string in ANSI format or in Unicode format.
Versions 16 and later
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
New in version 16
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
AndroidAndroid Widget Java You cannot run queries whose SQL code contains WLanguage functions.
PHP This parameter can only be set to hQueryDefault. An error is generated if another constant is used.
Versions 23 and later
This parameter is not available if <Query Name> corresponds to an SQL Query variable.
New in version 23
This parameter is not available if <Query Name> corresponds to an SQL Query variable.
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) during the initialization of the query.
Note: ..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 Example). 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]

Running an SQL query with parameters

An SQL query can contain parameters. To run 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 customer 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. Run the SQL query with HExecuteSQLQuery. Example:
    HExecuteSQLQuery(MyQuery, "SELECT * FROM customer WHERE name={p_name}")
Note: The structure of query parameters is re-initialized whenever the query with parameters is run.

SQL injection: How to prevent it?

The SQL injection is a hacking technique ; it consists in injecting SQL code into the parameters of queries, forcing the execution of unwanted SQL code.
To avoid SQL injection when running 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
sdQuery is Data Source
HExecuteSQLQuery(sdQuery, "SELECT * FROM customer WHERE name = '" + EDT_Name + "'")
// Using parameters in the query
// -> secure code
sdQuery is Data Source
sdQuery.p_name = EDT_Name
HExecuteSQLQuery(sdQuery, "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 ans it eliminates several risks of hacking.
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.
Note: These joins are managed by the HFSQL engine.
WINDEVWEBDEV - Server codeReports and QueriesWindows MobileAjaxUser code (UMC)OLE DBNative 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
Automatically defining the connection associated with the data files found in the query.The connection to use must be specified in HExecuteSQLQuery.
Replacing all the proprietary PC SOFT symbols (example: ']=' starts with) by 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.
Format the floats (the decimal separator can be '.' or ',')No formatting is performed for the floats.
Depending on the database used, the alias names are replaced by the full names of the items in Where, Order by and Group by
For example, the JET engine (Access, dBase, ...) 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 Accesses Special case: Oracle and SQL Server: If the query to run contains a script with ":param" (Oracle) or "@param" (SQL Server), you must use the hQueryWithoutCorrection + hNoBind combination of 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 a SQL query, the conditions must be specified for each component of the key.
Do not attempt to directly assign a value to the composite key (indeed, the composite keys are stored as binary values).
Example: The composite key is made of LASTNAME and FIRSTNAME items (LASTNAMEFIRSTNAME item):
SELECT MyFile.MyItem, MyFile.MyItem1
FROM MyFile
WHERE FileName.LastName = "Smith" AND FileName.FirstName = "Fred"
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadWindows MobileUniversal Windows 10 AppPHPAjaxUser code (UMC)External languageHFSQLHFSQL Client/ServerHyper File 5.5

Queries on HFSQL data files

  • To optimize the execution time of query, use HOptimize on the different data files taking part in the query or HOptimizeQuery.
  • To optimize the process time of queries, use HStatCalculate or HIndex on all data files taking part in 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 = ""

    // Connection 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

    // Run the 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 declaration of the file
    HCancelDeclaration("CUSTOMER")

    // Closes the connection to the database
    HCloseConnection(cntDatabase)
  • Versions 18 and later
    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.
    New in version 18
    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.
    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 QueriesWindows MobilePHPAjaxUser code (UMC)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 SQL query.
  2. Declare the different parameters or variables of the query.
    The parameters are string parameters by default. You have the ability to specify their type by using ..Type.
    Caution: To specify the type of a query variable, an existing variable of requested type can also be assigned 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 data source corresponding 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 QueriesWindows MobilePHPUser code (UMC)Native Accesses

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

Note: This note only applies to native Oracle and SQL Server accesses.
To specify and retrieve values when running a query, you must:
  1. Declare a data source. This data source will contain the result of SQL query.
  2. Declare the different query variables (input and output variables).
    The variables are string variables by default. You have the ability to specify their type by using ..Type.
    Caution: To specify the type of a query variable, an existing variable of requested type can also be assigned 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)
Notes
  • 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).
    For SQL Server, the parameters are specified via the following notation: @ParamName.
    The same parameter can be used several times. In this case, the corresponding variable will be re-used.
  • To run a query without enabling the bind, use the hNoBind constant in HExecuteSQLQuery.
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadWindows MobileUniversal Windows 10 AppPHPAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBNative 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 a HFSQL database.
To perform an automatic integrity check, all you have to do is 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. An error occurs if the user has no rights to disable the integrity management (HModifyDatabaseRights) and if the integrity management must be performed.
To perform an automatic check for duplicates, all you have to do is 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.
Note: 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 the user has no rights to disable the management of duplicates (HModifyDatabaseRights) and if the management of duplicates must be performed.
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/iPadWindows MobileUniversal Windows 10 AppPHPAjaxUser code (UMC)External languageHFSQLOLE DBNative Accesses

Browsing queries (HReadFirst)

  • By default, HReadFirst re-runs the query to refresh the query result. We recommend that you use the hNoRefresh constant to avoid re-running the query.
  • Browsing a query run with the hQueryWithoutCorrection option:
    To browse the records in the order returned by the database, there is no need to specify a search item. Example:
    HReadFirst("MyQuery", hNoRefresh)

    If a search item is specified, the query result is entirely retrieved and indexed. The browse 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.
Components
WINDEVWEBDEV - Server codeReports and Queries wd230hf.dll
Windows Mobile wp230hf.dll
Java wd230java.jar
Linux wd230hf.so
Android wd230android.jar
Minimum required version
  • Version 9
This page is also available for…
Comments
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
Oct. 31 2016