PC SOFT

WINDEV的在线文档

  • Reading the data during the initialization
  • Text of the SQL query
  • Joins
  • Why should I use the hQueryWithoutCorrection constant?
  • Condition on a composite key in a 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 (H执行SQL查询 or execution of SQL queries created in the query editor)
  • Browsing the queries (H读第一个)
产品
WinDevWebDev-服务器代码WebDev-浏览器代码WinDev Mobile报表和查询
平台
WindowsLinuxWindows MobileWindows PhoneWindows Store appsAndroidAndroid Widget iPhone/iPad
语言
JavaPHPAjax用户代码(MCU)外部语言
数据库
HFSQLHFSQL Client/Server存储过程HF 5.5OLE DBODBC本地访问

Initializes a query written in SQL language and declares this query to the HFSQL engine. Then, the result of the query 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 a SQL query for more details.

Caution: To position on the first record of the query result, use H读第一个 for example.

To free the resources of this query, use:

Note: a query will not be initialized if:
16版本和更高版本
WinDev This function is now available for the Java applications.
16新版本
WinDev This function is now available for the Java applications.
WinDev This function is now available for the Java applications.

18版本和更高版本
WinDevWindows Store apps This function is now available in Windows Store apps mode.
18新版本
WinDevWindows Store apps This function is now available in Windows Store apps mode.
WinDevWindows Store apps This function is now available in Windows Store apps mode.
例子
CustomerQry数据源型
// Initialize the "Customer" query
H执行SQL查询CustomerQry"SELECT NAME FROM CUSTOMER"

TaskQry数据源型
// Initialize the "TaskQry" query
// This query is using a WLanguage function
// This query selects the tasks exceeding 5 days
H执行SQL查询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数据源型

sMyQry字符串型=【
SELECT * FROM
ZIPCODES
%1


sMyQry字符串创建sMyQry"LIMIT 1, 10"
如果不H执行SQL查询QRYh查询.默认sMyQry),那么
错误H错误信息())
否则
信息"The query contains "+H记录数量QRY)+" records."
结束

语法

Running a SQL query (without connection) 隐藏详细信息

<Result> = H执行SQL查询(<Query Name> [, <Mode>] , <Text of SQL Query>)
<Result>: Boolean
  • if the SQL query was initialized,
  • otherwise. H错误信息 returns more details about the problem.
<Query Name>: Character string (with or without quotes) or data source
Name of the 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.
<Mode>: Optional Integer constant
Option for initializing the query:
h.使用过滤器
(constant that can be combined with the other constants)
If this parameter is specified:
  • the result of the query corresponds to a selection of filter records if the HFSQL engine allows it. Otherwise, the result of the query corresponds to a HFSQL view.
  • the following operations are not allowed: search on the result of the query, query based on query, view based on query, query based on view.
  • the search items specified when browsing the result are ignored.
  • the magnifier is not available for the tables linked to a query.
  • H记录数量 cannot be used on the queries.
    Caution: if this option is specified, the h.修改文件 constant is automatically used.
If this parameter is not specified (by default):
  • the result of the query corresponds to a HFSQL view.
  • the following operations can be performed on the queries: search on the result of the query, query based on query, view based on query, query based 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.
  • H记录数量 can be used on the queries.

h.使用事务The 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 execution of the query).

Note: the execution of the query may be slowed down.

h.修改文件
(constant that can be combined with the other constants)
  • On HFSQL data files: When modifying the result of the query (H添加, H写记录, H修改, H划线, H删除), these modifications will be applied to the data files taking part in the query.
    If this option is not specified, only the result of the query is modified.
    See Modifying the content of a query for more details.
  • On data files in a format other than HFSQL, the h.修改文件 constant is useless: the data files involved in the query are automatically modified when the result of the query is modified.

h查询.可中断The initialization of the query can be interrupted by the ESC key.

Windows Store apps This constant is not available.

存储过程 This constant is ignored.

h查询.默认
(default value)
The query is initialized without interruption.
h查询.无修正
OLE DB本地访问 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:

h查询.无HF修正
HFSQL The format of the data file (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.

Windows Store apps This constant is not available.
h.无绑定
本地访问 SQL Server, Oracle, Sybase: Used to run a query without enabling the bind.
h.SQLUnicode
h.延时For the UPDATE and DELETE queries performed on an important number of records and being quite long, this constant is used to give control back to the other applications that use the data files.
In this case:
  • the query runs a little slower,
  • the file server is not saturated,
  • the other users of the data files can still access them without being locked.
HFSQL Client/ServerOLE DB本地访问 This constant is ignored.


h检查.重复Enables the management of duplicates during the execution of the query. The query is not run if a duplicate error occurs.
This constant forces the h.使用事务 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 the query execution, regardless of the query result. If the application is stopped during the execution of the query (power outage for example), the transaction will be canceled when the data files of the query are used for the next time.
Note: the execution of the query may be slowed down.

HFSQL Client/Server The management of duplicates is disabled if the h检查.重复 constant is not specified. An error occurs if the user has no rights to disable the management of duplicates (H修改数据库权限) and if the management of duplicates must be performed.

h检查.完整性Enables the management of integrity during the execution of the query. 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 the query execution, regardless of the query result. If the application is stopped during the execution of the query (power outage for example), the transaction will be canceled when the data files of the query are used for the next time.
Note: the execution of the query may be slowed down.

HFSQL Client/Server The integrity management is disabled if the h检查.完整性 constant is not specified. An error occurs if the user has no rights to disable the integrity management (H修改数据库权限) and if the integrity management must be performed.

<Text of SQL Query>: Character string (with quotes)
Text of the SQL query to run. This text can correspond to a character string in ANSI format or in Unicode format.

Running a SQL query via a connection 隐藏详细信息

<Result> = H执行SQL查询(<Query Name> [, <Connection> [, <Mode>]] , <Text of SQL Query>)
<Result>: Boolean
  • if the SQL query was initialized,
  • otherwise. H错误信息 returns more details about the problem.
<Query Name>: Character string (with or without quotes) or data source
Name of the 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.
<Connection>: Optional character string or connection variable
Name of the connection used to run the query. This connection was defined in the data model editor, or by programming with H描述连接 or H打开连接, or by specifying the properties of the 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:
h.使用过滤器
(constant that can be combined with the other constants)
If this parameter is specified:
  • the result of the query corresponds to a selection of filter records if the HFSQL engine allows it. Otherwise, the result of the query corresponds to a HFSQL view
  • the following operations are not allowed: search on the result of the query, query based on query, view based on query, query based on view.
  • the search items specified when browsing the result are ignored.
  • the magnifier is not available for the tables linked to a query.
  • H记录数量 cannot be used on the queries.
    Caution: if this option is specified, the h.修改文件 constant is automatically used.
If this parameter is not specified (by default):
  • the result of the query corresponds to a HFSQL view
  • the following operations can be performed on the queries: search on the result of the query, query based on query, view based on query, query based 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.
  • H记录数量 can be used on the queries.

h.使用事务The 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 execution of the query).
Note: the execution of the query may be slowed down.

h.修改文件
(constant that can be combined with the other constants)
  • On HFSQL data files: When modifying the result of the query (H添加, H写记录, H修改, H划线, H删除), these modifications will be applied to the data files taking part in the query.
    If this option is not specified, only the result of the query is modified.
    See Modifying the content of a query for more details.
  • On data files in a format other than HFSQL, the h.修改文件 constant is useless: the data files involved in the query are automatically modified when the result of the query is modified.

h查询.可中断The initialization of the query can be interrupted by the ESC key.

Windows Store apps This constant is not available.

存储过程 This constant is ignored.

h查询.默认
(default value)
The query is initialized without interruption.
h查询.无修正
OLE DB本地访问 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:

h查询.无HF修正
HFSQL The format of the data file (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.

Windows Store apps This constant is not available.
h.无绑定
本地访问 SQL Server, Oracle, Sybase: Used to run a query without enabling the bind.
h.延时For the UPDATE and DELETE queries performed on an important number of records and being quite long, this constant is used to give control back to the other applications that use the data files.
In this case:
  • the query runs a little slower,
  • the file server is not saturated,
  • the other users of the data files can still access them without being locked.
HFSQL Client/ServerOLE DB本地访问 This constant is ignored.

h检查.重复Enables the management of duplicates during the execution of the query. The query is not run if a duplicate error occurs.
This constant forces the h.使用事务 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 the query execution, regardless of the query result. If the application is stopped during the execution of the query (power outage for example), the transaction will be canceled when the data files of the query are used for the next time.
Note: the execution of the query may be slowed down.
HFSQL Client/Server The management of duplicates is disabled if the h检查.重复 constant is not specified. An error occurs if the user has no rights to disable the management of duplicates (H修改数据库权限) and if the management of duplicates must be performed.

h检查.完整性Enables the management of integrity during the execution of the query. The query is not run if an integrity error occurs.
This constant forces the h.使用事务 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 the query execution, regardless of the query result. If the application is stopped during the execution of the query (power outage for example), the transaction will be canceled when the data files of the query are used for the next time.
Note: the execution of the query may be slowed down.
HFSQL Client/Server The integrity management is disabled if the h检查.完整性 constant is not specified. An error occurs if the user has no rights to disable the management of integrity (H修改数据库权限) and if the management of integrity must be performed.

<Text of SQL Query>: Character string (with quotes)
Text of the SQL query to run. This text can correspond to a character string in ANSI format or in Unicode format.

注意

Reading the data during the initialization

Depending on the query (sorts, groups, ...), the data can be read (or not) during the initialization of the query.

To access the items of the query, 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:

WinDevWindows Store apps用户代码(MCU)外部语言HFSQLHFSQL Client/ServerHF 5.5

Text of the 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 text of the query. 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]

WinDevWindows Store apps用户代码(MCU)HFSQLHFSQL Client/ServerHF 5.5本地访问

Joins

The joins such as (A join B on x=y) join C on y=z ... are not automatically managed by the native accesses: in this case, the h查询.无修正 constant must be used to manage this type of join.

Note: These joins are managed by the HFSQL engine.

WinDev用户代码(MCU)OLE DB本地访问

Why should I use the hQueryWithoutCorrection constant?

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 H执行SQL查询.
Replacing all the proprietary PC SOFT symbols (example: ']=' 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 WinDev/WebDev dates are in 'YYYYMMDD' format 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 the items must be used in the query code for Where, Order by and Group by.

本地访问 Special case: Oracle and SQL Server: If the query to run contains a script with ":param" (Oracle) or "@param" (SQL Server), you must use the h查询.无修正 + h.无绑定 combination of constants in order for the query not to be interpreted.

Condition on a composite key in a 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"

WinDevWindows Store apps用户代码(MCU)外部语言HFSQLHFSQL Client/ServerHF 5.5

Queries on HFSQL data files

  • To optimize the execution time of the query, use H优化 on the different data files taking part in the query or H优化查询.
  • To optimize the process time of the queries, use H统计 or H重建索引 on all the 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 H导入数据库表. Otherwise, an error such as Unable to initialize the query. Unknown FILENAME will occur. Example:
// Parameters of the connection
cntDatabase连接型
cntDatabase..提供者=h访问.HFClientServer
cntDatabase..服务器="HYPERFILESQLSERVER:4900"
cntDatabase..数据库="Database"
cntDatabase..用户="admin"
cntDatabase..Password""

// Connection to the database
如果H打开连接cntDatabase)=,那么
错误H错误信息())
返回
结束

// Dynamic declaration of the files used in the query
如果H导入数据库表"CUSTOMER.FIC""CUSTOMER"cntDatabase)=,那么
错误H错误信息())
返回
结束

// Run the query
dsSQLQuery数据源型
如果不H执行SQL查询dsSQLQuerycntDatabaseh查询.默认,【
SELECT *
FROM CUSTOMER
】),那么
错误"Runtime problem"
否则
信息"ok"
结束

// Cancels the dynamic declaration of the file
H取消声明"CUSTOMER"

// Closes the connection to the database
H关闭连接cntDatabase
  • 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.
    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.

WinDev用户代码(MCU)本地访问

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 have the ability to specify their type by using ..类型.
    Caution: To specify the type of a query variable, an existing variable of the requested type can also be assigned to this variable.
  3. Prepare the query with H准备SQL查询.
  4. Specify the value of the different parameters to take into account and run the query with H执行SQL查询. Only the name of the 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数据源型
// Declare one of the query parameters
// This parameter is an integer
Insert1.age0
i整数型
// Prepare the query for multiple executions
H准备SQL查询Insert1MyConnection,...
h查询.无修正,...
"INSERT INTO PERSONE VALUES (:lastname,:firstname,:age )"
// Loop for running the query
// Only some parameters are modified
对于i1直到10
Insert1.lastname"LastName"+i
Insert1.firstname"FirstName"+i
Insert1.agei
H执行SQL查询Insert1
结束

WinDev用户代码(MCU)本地访问

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

Note: This note only applies to the 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 the SQL query.
  2. Declare the different variables of the query (input and output variables).
    The variables are string variables by default. You have the ability to specify their type by using ..类型.
    Caution: To specify the type of a query variable, an existing variable of the requested type can also be assigned to this variable.
  3. Run the query with H执行SQL查询.
// Example for Oracle
// Declare the data source associated with the query
MyProc数据源型
// Declare the variables
MyProc.n3
MyProc.str"Example"
// Run the query and retrieve the result
H执行SQL查询MyProcMyConnectionh查询.无修正,...
"begin:Res:=sp_cut(:n,:str);end;"
// Display the result
信息MyProc.Res

Notes
  • H执行SQL查询 must be used with:
    • the name of the connection,
    • the h查询.无修正 constant.
  • The variables declared in the query must be identical to the ones used in the code of the 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 h.无绑定 constant in H执行SQL查询.

WinDevWindows Store apps用户代码(MCU)HFSQLHFSQL Client/ServerHF 5.5OLE DB本地访问

SQL queries (H执行SQL查询 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 h检查.完整性 constant. This constant is used to enable the management of integrity during the execution of the query. 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. If the application is stopped during the execution of the query (power outage for example), the transaction will be canceled when the data files of the query are used for the next time.

HFSQL Client/Server The integrity management is disabled if the h检查.完整性 constant is not specified. An error occurs if the user has no rights to disable the integrity management (H修改数据库权限) and if the integrity management must be performed.

To perform an automatic check for duplicates, all you have to do is specify the h检查.重复 constant. This constant is used to enable the management of duplicates during the execution of the query. 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. If the application is stopped during the execution of the query (power outage for example), the transaction will be canceled when the data files of the query are used for the next time.

Note: If a duplicates error occurs when running an UPDATE query, H执行查询 returns False and the process continues. The corresponding error is returned by H错误信息.

Note: The default automatic assistance is not called for the duplicate errors. A simple duplicate error is generated. If a help was redefined by H自定义错误处理, then this custom help is called.

HFSQL Client/Server The management of duplicates is disabled if the h检查.重复 constant is not specified. An error occurs if the user has no rights to disable the management of duplicates (H修改数据库权限) 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.

WinDevWindows Store apps用户代码(MCU)外部语言HFSQLOLE DB本地访问

Browsing the queries (H读第一个)

  • By default, H读第一个 re-runs the query in order to refresh the result of the query. We recommend that you use the h.不刷新 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:
H读第一个"MyQuery"h.不刷新
If a search item is specified, the result of the query 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:

H读第一个"MyQuery""MyItem"h.不刷新
The created index is used to perform searches on the result of the query.

组件
WinDev wd200hf.dll
Linux wd200hf.so