ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Managing HFSQL Client/Server
  • Overview
  • Definition
  • When should I use an SQL view?
  • Creating an SQL view
  • Overview
  • Creation from the data model editor
  • Creation by using the SQL code
  • Using an SQL view
  • Overview
  • Rights on a view
  • Modifying the content of an SQL view
  • WLanguage functions used to handle an SQL view
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview

Definition

An SQL view is a virtual data source defined via an SQL query. It is created on the HFSQL server and it can be used by the applications that access the database.
The application can use or handle an SQL view by performing a query on the SQL view. When the query is run, the SQL view on which the query is based is automatically re-run. The data used is always updated.

When should I use an SQL view?

Using an SQL view presents several benefits:
  • Independence with respect to the physical structure of the data.
  • Security.
Let's take a closer look at these benefits:
  • Independence with respect to the physical structure of the data:
    The SQL view corresponds to an abstraction level between the data structure and the data use. If the physical structure of data evolves, the SQL queries of the application do not change. Only the SQL code of view must be adapted to the new structure.
  • Security and rights
    An SQL view can also be used for confidentiality purposes. Some items can be hidden in the view.
    Example: A Personnel file contains the last name, first name, address, personal telephone number and the office number. This file can only be accessed by authorized people.
    You can create an SQL view of this file containing only the last name and office number. Since the creator of the view has rights to the Personnel file, the content of the view will be accessible by users who are not authorized to view the file. However, the confidential information will not be accessible.
Creating an SQL view

Overview

Several methods can be used to create an SQL view:
  • Creating the SQL view from the data model editor. In this case, to use the view, the data must be updated on the server. This update can be performed:
    • via the automatic data modification (performed when installing the application for example).
    • via HUpdateView.
  • Creating the SQL view using the "CREATE VIEW" SQL command directly in the application.

Creation from the data model editor

To create an SQL view from the data model editor:
  1. Start the view creation wizard:
    • In the ribbon, on the "Analysis" tab, in the "Creation" group, expand "New" and select "View".
    • On the "Analysis" pane, select the "Views" folder then select the "New view" option of context menu.
  2. In the wizard, select "Create a view" then go to the next screen (arrow at the bottom of the wizard).
  3. Specify the name of the view to create then select the edit mode of base query for the view:
    • Wizard. In this case, the query of the view is created via the wizard for query creation. The query is displayed graphically. The query can be modified at any time via the wizard.
    • SQL code. In this case, you directly enter the SQL code for selecting the elements selected by the view.
  4. The SQL view is created. By default, it is not associated with a connection. To associate the view with a connection defined in the data model editor:
    • On the "Analysis" pane, select the SQL view that was just created.
    • In the context menu (right mouse click), select "Description of the view".
    • In the window that is displayed, you can modify the name of the SQL view and specify the connection associated with the SQL view.
    • Validate.
  5. Generate the analysis to use the SQL view in the code of the application: on the "Analysis" tab, in the "Analysis" group, expand "Generation" and select "Generation".
Remark: In a creation query of a SQL view, some options are not available:
  • The SQL views are not sorted.
  • The SQL views cannot contain parameters.

Creation by using the SQL code

To create an SQL view using SQL code, all you have to do is use the CREATE VIEW command in the SQL code of the query. For example:
Qry_View is Data Source
 
 
// Create view
MySQLViewCode is string
MySQLViewCode = [
Create view Customer_View
AS SELECT * FROM Customer
]
HExecuteSQLQuery(Qry_View, HFSQLConnection, MySQLViewCode)
Using an SQL view

Overview

To use an SQL view, all you have to do is create queries based on the SQL view. These queries can be created:
  • via the query editor.
    If the SQL view is created in the data model editor, the view appears in the list of elements available for creating the query.
  • via SQL code.
Example of SQL code:
Qry_View is Data Source
 
// Create view
MySQLViewCode is string
MySQLViewCode = [
Create view Customer_View
AS SELECT * FROM Customer
]
HExecuteSQLQuery(Qry_View, HFSQLConnection, MySQLViewCode)
MyQry is Data Source
MyQryCode = "SELECT Name FROM View_Customer"
HExecuteSQLQuery(MyQry, HFSQLConnection, MyQryCode)
Remark: The HFSQLConnection parameter must be specified if the view was not created in the data model editor or if the view defined in the data model editor was not associated with a connection.
Like a standard query, the result of the query based on the SQL view can be:
  • browsed by the HFSQL functions (FOR EACH, HRead*, ...).
  • associated with a control (Table control, List Box control, ...).

Rights on a view

The access to the data sources referenced by the view is performed with the rights of the view creator.
The user of the view has rights on the view. These rights can be configured:
  • via the HFSQL Control Center (not available in this version).
  • via HModifyViewRights.
Remarks:

Modifying the content of an SQL view

The content of the source files for the SQL view can be modified via the SQL commands:
  • INSERT INTO
  • UPDATE
To use these commands, you must comply with the following rules:
  • The SQL view must be based on a single data file.
  • The SQL view must no include any Unions, "Group By", aggregate, "Having" or "Distinct" commands.
  • For an UPDATE query, only the simple items can be modified (no calculated items).
  • For an INSERT query:
    • only the simple items can be added (no calculated items).
    • the view must export all the file items that must have a value (especially the unique keys other than the automatic identifiers).
WLanguage functions used to handle an SQL view
The following WLanguage functions are used to handle the SQL views:
HInfoDatabaseRightsAllows you to find out the rights granted to a user or to a group on a database.
HInfoViewRightsAllows you to find out the rights granted to a user or to a group on an SQL view or on a materialized view.
HListFileReturns the list of files:
  • found in the current analysis or in a specific analysis recognized by the HFSQL engine. The files defined by HDeclare, HDeclareExternal and HDescribeFile are taken into account.
  • available on a connection via a native access or via an OLE DB driver.
  • for a group of files defined in the data model editor.
HModifyDatabaseRightsModifies the rights granted to a user or to a group for a HFSQL Client/Server database.
HModifyViewRightsModifies the rights granted to a user or to a group on a SQL view or on a materialized view.
HUpdateViewCreates or updates the description of an SQL view or materialized view from the analysis to an HFSQL server.
Minimum version required
  • Version 20
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 03/09/2023

Send a report | Local help