PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US

  • Reading data during the initialization
  • Adding records and using the hModifyFile constant
  • Why should the hQueryWithoutCorrection constant be used
  • Passing parameters to the query
  • HExecuteQuery and access by JDBC
  • Freeing the resources of the query
  • Optimizing the queries on HFSQL data files
  • Running a query and Table control based on this query
  • Running a query and report based on this query
  • SQL queries (HExecuteSQLQuery or execution of SQL queries created in the query editor)
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
Declares a query to the HFSQL engine and runs this query. This query can correspond to:
  • a query created in the query editor.
  • 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 recalculated.
Caution: To position on the first record of query result, use HReadFirst for example.
To free the resources of a query created in the query editor, 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.
PHP
  • A MySQL server version 4.1 (or later) is required to use the queries in the PHP/MySQL access.
  • Versions 23 and later
    The SQL Query variables are not available.
    New in version 23
    The SQL Query variables are not available.
    The SQL Query variables are not available.
Versions 15 and later
Android This function is now available for the Android applications.
New in version 15
Android This function is now available for the Android 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 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.
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.
// Initialize the "QRY_Customer_84" query
HExecuteQuery(QRY_Customer_84)
// Query on Oracle file: Customer uses the "MyConnection" connection
// QRY_MyQuery = "Select * from customers where CustomerID =?"
HExecuteQuery(QRY_MyQuery, hQueryDefault, 5)
// Runs and displays the data of a query not found in the project
// "QRY_List_Products.WDR" is a query created by "Reports & Queries"
// Declare a data source with the same logical name as the query.
sQueryFile is string
sQueryFile = fReportsAndQueriesDir() + ["\"] + "QRY_List_Products.WDR"
QRY_List_Products is Data Source

// Run the query for a family of products:
// FAMILY_CODE is a parameter of the query.
HExecuteQuery(sQueryFile, hQueryDefault, FAMILY_CODE)

// Browse the query
FOR EACH QRY_List_Products
TableAddLine(TABLE_PRODUCT, sQueryFile.PRODUCT_NAME, ...
sQueryFile.DESIGNATION, sQueryFile.PriceBT)
END
Syntax

Running a query (without connection) Hide the details

<Result> = HExecuteQuery(<Query Name> [, <Mode> [, <Parameter 1> [, ... [, <Parameter N>]]]])
<Result>: boolean
  • True if the query was initialized,
  • False otherwise. HError returns more details about the problem.
<Query Name>: Character string (with or without quotes)
Name of query to run:
  • Name and full path of query (".WDR" file) to initialize.
    AndroidAndroid Widget JavaPHP Not available.
  • Logical name of query to initialize.
    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.
    PHP This type of variable is not available.
    New in version 23
    Name of SQL Query variable.
    PHP This type of variable is not available.
    Name of SQL Query variable.
    PHP This type of variable is not available.
<Mode>: Optional Integer constant
Option for initializing the query:
hWithFilter
(option 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: the hModifyFile constant is automatically used if this option is specified.
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 the query execution, regardless of the query result.
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
(option 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
AndroidAndroid Widget JavaOLE 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).
  • HFilter cannot be called on the query used.
  • 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.
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.
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.
PHP This parameter can only correspond to the hQueryDefault constant. An error is generated if another constant is used.
AndroidAndroid Widget Java Android and Java (access by JDBC): This parameter can only correspond to the hQueryDefault or hQueryWithoutCorrection constant. If another constant is used, it will be ignored.
<Parameter 1> ... <Parameter N>:
Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma.
Notes:
  • Passing parameters in HExecuteQuery is kept for backward compatibility. We advise you to pass parameters via the following notation: <QueryName>.<ParameterName> = <Value>.
  • To specify the query parameters, the <Mode> parameter must be specified.
  • The value of each parameter can be specified or not.
  • You have the ability to specify a list of values or a list of parameters.
See Passing parameters for more details.
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 a query via a connection Hide the details

<Result> = HExecuteQuery(<Query Name> [, <Connection> [, <Mode> [, <Parameter 1> [, ... [, <Parameter N>]]]]])
<Result>: boolean
  • True if the query was initialized,
  • False otherwise. HError returns more details about the problem.
<Query Name>: Character string (with or without quotes)
Name of query to run:
  • Name and full path of query (".WDR" file) to initialize.
    AndroidAndroid Widget JavaPHP Not available.
  • Logical name of query to initialize.
    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.
    PHP This type of variable is not available.
    New in version 23
    Name of SQL Query variable.
    PHP This type of variable is not available.
    Name of SQL Query variable.
    PHP This type of variable is not available.
<Connection>: Optional character string (with or without quotes)
Name of connection, defined in the data model editor or by HDescribeConnection. The query will be run via this connection.
OLE DBNative Accesses If this parameter is not specified and if the query affects data files accessed by OLE DB or by a native access, the query is run via the connection used by the first data file found in the query. If the connection used by a data file is modified before the call to HExecuteQuery, the new connection will be used.
<Mode>: Optional Integer constant
Option for initializing the query:
hWithFilter
(option 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: the hModifyFile constant is automatically used if this option is specified.
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 the query execution, regardless of the query result.
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
(option 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
AndroidAndroid Widget JavaOLE 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).
  • HFilter cannot be called on the query used.
  • 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.
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.
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.
PHP This parameter can only correspond to the hQueryDefault constant. An error is generated if another constant is used.
AndroidAndroid Widget Java Android and Java (access by JDBC): This parameter can only correspond to the hQueryDefault or hQueryWithoutCorrection constant. If another constant is used, it will be ignored.
<Parameter 1> ... <Parameter N>:
Values of query parameters in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma.
Notes:
  • Passing parameters in HExecuteQuery is kept for backward compatibility. We advise you to pass parameters via the following notation: <QueryName>.<ParameterName> = <Value>.
  • To specify the query parameters, the <Mode> parameter must be specified.
  • The value of each parameter can be specified or not.
  • You have the ability to specify a list of values or a list of parameters.
See Passing parameters for more details.
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, etc.), the data can be read (or not) when initializing 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).
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadWindows MobileUniversal Windows 10 AppJavaAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBNative Accesses

Adding records and using the hModifyFile constant

HFSQL When records are added into a data file via a query, the query takes into account the records added into the data file regardless of the condition defined in the query. The number of records in this query is systematically modified.
Native Accesses When records are added into a data file via a query, the query takes into account the condition defined in the query. The number of records in this query is modified only if the records added into the data file correspond to this condition.
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 on OLE DB and on ODBC via the OLE DB provider. To prevent the query from being interpreted, use the hQueryWithoutCorrection constant.
The hQueryWithoutCorrection constant can be used if you directly enter the SQL code of your query in the query editor.
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.
Replace 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.
Formatting 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

Passing parameters to the query

Several methods can b e used to pass parameters to a query: 1. Passing parameters with the following notation: <QueryName>.<ParameterName> = <Value> (recommended) 2. Passing parameters in HExecuteQuery (kept for backward compatibility, compatible with PHP)
Let's present these two methods:
  1. Passing parameters with the following notation: <QueryName>.<ParameterName> = <Value> (recommended)
    To pass parameters to a query with parameters before running it, you also have the ability to use the following syntax:
    <Query Name>.<Name of Parameter1> = xxx
    <Query Name>.<Name of Parameter2> = xxx
    <Query Name>.<Name of Parameter3> = xxx
    HExecuteQuery(<Query Name>)
    IF ErrorOccurred = True THEN...

    Notes:
    • This syntax makes it easier to pass parameters. Indeed, if the parameters are directly passed to HExecuteQuery, the order of parameters must be complied with.
    • The structure of query parameters is re-initialized whenever the query with parameters is run.
    • PHP This method is not available.
  2. Passing parameters in HExecuteQuery (kept for backward compatibility)
    In this case, the values of query parameters must be specified in their initialization order (visible in the query editor). Each parameter must correspond to the expected type. The different parameters are separated by a comma. The value of each parameter can be specified or not.
    A parameter can be ignored by using the Null value. In this case, the selection conditions that is using this parameter will be ignored.

    You have the ability to specify a list of values or a list of parameters.
    If the selection condition for one of the query parameters is "is included in the list" or "is not included in the list", you must specify a list of values or parameters.
    To specify a list of values or a list of parameters, the different values or parameters must be separated by a semi-colon (';'), a carriage return ('RC') or a tabulation ('TAB').
    For example:
    HExecuteQuery(QueryName, hQueryDefault, "34000;34200;34400")
    or
    HExecuteQuery(QueryName, hQueryDefault, "34000 "+ RC + "34200" + RC + "34400")
    or
    HExecuteQuery(QueryName, hQueryDefault, "34000" + TAB + "34200" + TAB + "34400")

    Note: If one of the values or parameters contains a semi-colon, a carriage return or a tabulation, this value or parameter must be enclosed in quotes. For example:
    HExecuteQuery(QueryName,hQueryDefault, "'A;A';'A;B';'A;C'")

    Note: All the query parameters do not necessarily have to be specified. The query conditions that use unspecified parameters will be ignored.
    Example: Let's consider the query named "Customer_LastName_FirstName" whose SQL code is as follows:
    SELECT * FROM CUSTOMER WHERE LASTNAME = {Param1} AND FIRSTNAME = {Param2}
    • The 2 parameters are specified:
      HExecuteQuery(QRY_Customer_LastName_FirstName, hQueryDefault, "Smith", "John" )
      will run the query
      SELECT * FROM CUSTOMER WHERE LASTNAME = 'Smith' AND FIRSTNAME = 'John'
    • Only the last name is specified:
      HExecuteQuery(QRY_Customer_LastName_FirstName, hQueryDefault, "Smith" )
      will run the query
      SELECT * FROM CUSTOMER WHERE LASTNAME= 'Smith'
    • Only the first name is specified:
      sName is Variant = Null
      // Use the mandatory variant for the Null variable (not filled)

      HExecuteQuery(QRY_Customer_LastName_FirstName, hQueryDefault, sName, "Vince")

      OR

      HExecuteQuery(QRY_Customer_LastName_FirstName, hQueryDefault, Null, "Vince")
      will run the query
      SELECT * FROM CUSTOMER WHERE FIRSTNAME= 'John'
AndroidAndroid Widget Java All the query parameters must be specified (via HExecuteQuery or via <QueryName>.<ParameterName> = <Value>). The optional parameters are not supported (the NULL value cannot be assigned to a parameter in order to ignore it).
Java

HExecuteQuery and access by JDBC

If the physical name of a data file used in the query differs from its logical name, in the SQL code, the name of items must be prefixed by the physical name of data file and not by its logical name. You also have the ability not to prefix the name of items.

Freeing the resources of the query

To free the resources of a query, use:
If this operation is not performed, the query is still found in the current HFSQL context and it can be accessed from any process.
Notes:
  • The query resources are not automatically freed when closing the window that ran the query (via HExecuteQuery).
  • Versions 23 and later
    The SQL Query variables are automatically freed at the end of variable scope. See SQL queries in WLanguage for more details.
    New in version 23
    The SQL Query variables are automatically freed at the end of variable scope. See SQL queries in WLanguage for more details.
    The SQL Query variables are automatically freed at the end of variable scope. See SQL queries in WLanguage for more details.
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadWindows MobileAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5

Optimizing the 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.
WINDEVWEBDEV - Server codeReports and QueriesWindows MobileAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBNative Accesses

Running a query and Table control based on this query

WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadWindows MobileAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBNative Accesses

Running a query and report based on this query

When printing a report based on a query, the query is not re-run if it was already run. To force the query to be re-run (addition of records for example), all you have to do is use HExecuteQuery or iInitReportQuery.
WINDEVWEBDEV - Server codeReports and QueriesiPhone/iPadWindows MobileUniversal Windows 10 AppAjaxUser 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 SQL DELETE, INSERT or UPDATE statements in the SQL queries, no integrity check and no duplicate check are performed 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 duplicate error is detected during the execution of 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.
  • No cascading deletion is supported by the DELETE queries.
  • 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.
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
Exemplo Agrupar datas
Exemplo Agrupar datas

//-----
SELECT DISTINCT
nota_fiscal_manifestacao_mdfe.data_importacao_arquivo AS data_importacao_arquivo
FROM
nota_fiscal_manifestacao_mdfe
ORDER BY
data_importacao_arquivo DESC
//-----
HExecuteQuery(QRY_manifestacao_por_dia)
FOR EACH QRY_manifestacao_por_dia
TableAddLine(TABLE_local,QRY_manifestacao_por_dia.data_importacao_arquivo)
END

//Blog com video e exemplo
http://windevdesenvolvimento.blogspot.com.br/2016/07/curso-windev-tabela-031-tabela-agrupar.html
https://www.youtube.com/watch?v=S4oeN9G_Pos

De matos AMARILDO
Jul. 09 2016
Exemplo Ler Cnpj Existe - HexecuteQuery
//Query
SELECT
cliente.id_empresa_cliente AS cli_empresa_cliente
FROM
cliente
WHERE
cliente.cnpj_cpf={parametro_cnpj_cpf}
//Codigo
HExecuteQuery(QRY_cnpj_cpf_Existe,hQueryDefault,EDT_cnpj_cpf)
IF QRY_cnpj_cpf_Existe.cli_empresa_cliente>0 THEN
Info("Já existe Cliente"+CR+QRY_cnpj_cpf_Existe.cli_empresa_cliente)
ReturnToCapture(EDT_cnpj_cpf)
END

//Blog com Video e Exemplo
http://windevdesenvolvimento.blogspot.com.br/2016/04/windev-sql-18-sql-fazer-queue-ver-se.html



De matos AMARILDO
Apr. 04 2016