- Overview
- SELECT
- SELECT
- Special case: multi-file SELECT
- Special cases: SELECT without FROM or SELECT using a virtual table
- Running a stored procedure
- INSERT
- INSERT
- INSERT with selection
- UPDATE
- DELETE
- CREATE TABLE
- Available types
- CREATE TABLE AS
- DROP TABLE
- ALTER TABLE
- CREATE INDEX
- DROP INDEX
- GRANT
- REVOKE
- CREATE VIEW
- DROP VIEW
- CREATE MATERIALIZED VIEW
- DROP MATERIALIZED VIEW
- REFRESH MATERIALIZED VIEW
- OPTIMIZE TABLE
The main SQL statements allowed in the SQL queries managed by WINDEV and WEBDEV are as follows: For more details, see the SQL documentation. Remarks: - These statements can be used:
- in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
- in the SQL code of queries run by HExecuteSQLQuery.
- The " character can be used as an identifier delimiter in SQL queries as specified by the SQL92 standard.
 Reports and Queries cannot be used to create queries in SQL using the INSERT, UPDATE or DELETE commands. SELECT The SELECT statement is used to find records in one or more files. Format: SELECT [ALL/DISTINCT] NomDesRubriques AS NomDesRubriquesUtilisé
FROM NomDesFichiers AS NomDesFichiersUtilisé [FORCE INDEX NomCléAUtiliser]
[WHERE JointuresFichiers [AND] ConditionsSélection]
[GROUP BY NomDesRubriques [HAVING Condition]]
[ORDER BY NomDesRubriques] Example: The following SQL code displays the total sum of orders per customer living in the Côte d'Or region.. The result will be grouped by customer name, by postal code and by city. Only the customers with a total amount of orders exceeding 3000 Dollars will be considered. The result will be sorted by total order amount in ascending order: SELECT CLIENT.NomClient AS NomClient,
CLIENT.CodePostal AS CodePostal,
CLIENT.Ville AS Ville,
SUM(COMMANDE.TotalTTC) AS TotalTTC
FROM CLIENT AS CLI, COMMANDE AS COM
WHERE CLI.NumClient = COM.NumClient
AND CodePostal LIKE '21%'
GROUP BY NomClient, CodePostal, Ville
HAVING TotalTTC > 3000
ORDER BY TotalTTC ASC Remarks: - The DISTINCT keyword deletes the duplicate records from the query result.
- For more details on selection conditions, see Selection conditions in an SQL query.
- A Select query can be created in the query editor. For more details, see Creating a select query.
- In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:
SELECT NomClient, Age>18 AS Majeur FROM Client
- The SELECT statement can be used as a scalar expression, which means that the SELECT statement can be used instead a value or a column in the statements such as INSERT, UPDATE, SELECT, etc.
For example:
INSERT INTO TABLE (nom, valeur) VALUES ( 'Rome a Rick', (SELECT MAX(nombre) FROM table2) )
UPDATE client SET cattc = (SELECT SUM(facture.total_ttc)
FROM facture WHERE facture.reference=client.reference)
SELECT NomDesRubriques AS NomDesRubriquesUtilisé,
(SELECT COUNT(*) FROM Table2 WHERE Rub=NomDesRubriquesUtilisé)
FROM Table1
- By default, the best route key found is used to browse the files taken into account in the query.. The FORCE INDEX keyword is used to force the route key used.. It is therefore possible to use a specific key as a compound key. For example:
SELECT * FROM CLIENT FORCE INDEX NomPrenom
WHERE prenom = 'Julie' AND nom = 'MARTIN'
Special case: multi-file SELECT The SELECT statement can also be used to select in a multi-file query all the columns of a data file via the following syntax: SELECT Ligne_Commande.*, Commande.NumCommande, ...
FROM Commande, Ligne_Commande This syntax is very useful for joins and avoids entering the name of each item, which is a long and tedious operation. Special cases: SELECT without FROM or SELECT using a virtual table The SELECT statement can also be used to run queries that do not apply to tables: - by using the syntax without "FROM" clause.
Example:
- by using the virtual DUAL table. This virtual table contains a single item (named "dummy") and a single record.
Example:
SELECT CURRENT_DATE FROM DUAL
Running a stored procedure The SELECT statement can also be used to run stored procedures directly. The following syntax is used to run the stored procedure: SELECT NomDeLaProcédureStockée(ParamètreNumérique,'ParamètreChaîne',....) [FROM DUAL] Remarks: - The stored procedure must be a stored function: it must always return a value.. Otherwise, a runtime error will occur.
- If the name of the stored procedure is identical to the name of a function or procedure found in the project, its name must be prefixed by the name of the set of stored procedures.
- The result of a stored procedure can also be one of the parameters of a SELECT query.
INSERT The INSERT statement is used to add: - one record to a data file.
- multiple records to a data file.
It uses the following format: INSERT INTO NomDuFichier (NomDesRubriques)
VALUES (ValeurRubriques ajout 1), (ValeurRubriques ajout 2), ... , (ValeurRubriques ajout N)
RETURNING ValeurRetour The RETURNING clause is used to return a result for each record processed by the query.. Examples: The following SQL codes can be used to insert: - a new customer in the CUSTOMER file (the complete customer information is provided).
INSERT INTO CLIENT
VALUES (77, 'Martin', 'Julie', '1 rue Giono', '69000', 'Lyon') - a new customer in the CUSTOMER file: only the customer's number, surname and first name are supplied:
INSERT INTO CLIENT (NumClient, NomClient, PrénomClient)
VALUES (77, 'Martin', 'Julie') - 3 products in a PRODUCT file:
INSERT INTO PRODUIT
VALUES ('Ref01', 'Eau minérale'), ('Ref02', 'Beurre'), ('Ref03', 'Eponge') - a new customer in the CLIENT file: the identifier of the new customer is returned.
INSERT INTO CLIENTS (Nom, Prenom) VALUES ('Dupont', 'Paul') RETURNING idClients
Remarks: - An Insert query can be created in the query editor. For more details, see Creating an insert query.
- To get the value of the automatic identifier in an INSERT query on HFSQL files (query run by HExecuteSQLQuery, HExecuteQuery or SQLExec), use the SQL.IDAuto variable.
- The INSERT command cannot be used to copy data from one HFSQL file to another.
- The H.NbRecModificationQuery variable is used to find out the number of inserted records.
INSERT with selection The INSERT statement can also be used to insert a selection of records made by a SELECT query. Its use format becomes: INSERT INTO NomDuFichier [(NomDesRubriques)] SELECT ... : - The number of items to insert must be identical to the number of values returned by the SELECT query.
- All the files items are taken into account if no item is specified in the INSERT statement.
- This type of query cannot be created in the query editor.
The UPDATE statement is used to update the records in a file. It uses the following format: UPDATE NomDuFichier
SET NomDeLaRubrique = Expression
[WHERE Conditions]
RETURNING ValeurRetour The RETURNING clause is used to return a result for each record processed by the query..
Examples:
- The following SQL code is used to modify the title and the name of the customer named Montgomery:
UPDATE CLIENT
SET Civilité = 'Mme',
NomClient = 'Darwin'
WHERE NomClient = 'Montgomery' - The following SQL code modifies the client name in the Customer file. For records with the same ID, the name of the client in the Customer file is replaced with the name in the Customer_Svg file:
UPDATE CLIENT, CLIENT_SVG
SET CLIENT.Nom = CLIENT_SVG.Nom
WHERE CLIENT.ID = CLIENT_SVG.ID - The following SQL code is used to modify products and returns the list of records corresponding to the modified products..
UPDATE PRODUIT SET FlagActif = 0 WHERE idFour = 10 RETURNING *
Notes about the use of the UPDATE statement in WINDEV: - The WHERE clause accepts sub-selections (by SELECT), provided that the sub-selection relates to another file.
- The SET expression accepts no sub-selection (no SELECT command just after SET). Similarly, no join can be performed in the UPDATE section of the query.
The following query is accepted:
UPDATE ARTICLE SET PrixVente = 1000
WHERE RefArticle IN (SELECT COMMANDE.RefArticle FROM COMMANDE)
The following query is not accepted:
UPDATE Article
SET PrixVente = (SELECT AVG(COMMANDE.PrixUnit) FROM COMMANDE
WHERE COMMANDE.RefArticle = ARTICLE.RefArticle
GROUP BY COMMANDE.RefArticle)
WHERE RefArticle IN (SELECT COMMANDE.RefArticle FROM COMMANDE)
Similarly, the following query is not accepted:
UPDATE Article INNER JOIN PRODUIT ON (Article.IdArticle = Produit.IdProduit)
SET Article.PrixVente = Produit.PrixVente
WHERE RefArticle IN (SELECT COMMANDE.RefArticle FROM COMMANDE) - An Update query can be directly created in the query editor. For more details, see Creating an update query.
- The quote is used as delimiter for the strings in the SQL queries. If the value to write must contain a quote, this value must be preceded by the backslash character ('\'). For example:
UPDATE CLIENT
SET Adresse = 'Rue de l\'écluse'
WHERE NomClient = 'Montgomery' - The H.NbRecModificationQuery variable is used to find out the number of updated records.
The DELETE statement is used to delete records from a file. It uses the following format: DELETE FROM NomDuFichier
WHERE Conditions
RETURNING ValeurRetour The RETURNING clause is used to return a result for each record processed by the query.. Examples: - The following SQL code is used to delete the customers born between 01/01/1971 and 01/01/1975:
DELETE FROM CLIENT
WHERE CLIENT.DateNaissance BETWEEN '19710101' AND '19750101' - The following SQL code is used to return the identifier of deleted customers:
DELETE FROM CLIENT
WHERE CODEPOSTAL = '75000'
RETURNING CLIENT.IDCLIENT AS ID
Remark: A deletion request can be created directly with the request editor. For more details, see Creating a delete query. The CREATE TABLE statement is used to describe and create a table. It uses the following format: CREATE TABLE NomTable
(
Description de la rubrique1,
Description de la rubrique2,
...
Description Index1,
Description Index2,
...
) In this syntax: - the "Description of item" parameters correspond to:
NomRubrique type [DEFAULT 'valeur' | DEFAULT NULL] [NOT NULL]
[UNIQUE] [PRIMARY KEY] [AUTO_INCREMENT] [COMMENT 'libellé'] [COLLATE 'langue'] - ItemName: name to give to the item.
- Type: Type of heading (chosen from the available types shown below).
- DEFAULT: sets the default value for the item.
- NOT NULL: prohibits assigning the value NULL to the field.
- UNIQUE: defines a heading with or without duplicates.
- PRIMARY KEY: defines a primary key.
- AUTO_INCREMENT: defines an automatic identifier for integer fields.
- COMMENT: defines the heading label.
- COLLATE: defines the sort item language (e.g. 'FR_BE').
- the "Description of index" parameters correspond to:
INDEX NomIndex ( rub1 [ASC | DESC] [CI] [AI] [PI], rub2 ... ) [UNIQUE] - ASC and DESC: define sorting direction.
- CI: indicates that the box is ignored.
- AI: indicates that accents are ignored.
- PI: indicates that punctuation is ignored.
Note: This type of query cannot be created using the query editor wizard. Available types The available types are: | | | CHARACTER | Character string | | CHARACTER(Size) | String of size | | VARCHAR(Size) | String of size | | CHARACTER VARYING(Size) | String of size | | CHAR VARYING(Size) | String of size | | NVARCHAR(Size) | Unicode string on size | | VARCHAR(Size) BINARY | Binary string of size | | BINARY(Size) | Binary string of size | | VARBINARY(Size) | Binary string of size | | BLOB | Binary memo | | CLOB | Text memo | | TEXT | Text memo | | NCLOB | Unicode memo | | NTEXT | Unicode memo | | NUMBER(Precision) | entier | | NUMBER(Precision, scale) | entier | | DECIMAL(Precision) | Real | | DECIMAL(Precision, scale) | Real | | TINYINT UNSIGNED | 1-byte unsigned integer | | SMALLINT UNSIGNED | 2-byte unsigned integer | | INTEGER UNSIGNED | 4-byte unsigned integer | | BIGINT UNSIGNED | 8-byte unsigned integer | | TINYINT | 1-byte signed integer | | SMALLINT | 2-byte signed integer | | INTEGER | 4-byte signed integer | | BIGINT | 8-byte signed integer | | FLOAT | 4-byte real | | REAL | 8-byte real | | DOUBLE PRECISION | 8-byte real | | MONEY | Currency | | DATE | DATE | | DATETIME | Date time | | TIME | Time | | BOOLEAN | Boolean | | SECUREPASSWORD | Secure password |
The CREATE TABLE AS statement describes a table and creates it from the result of a select query. The table columns have the names and types of data associated with the columns after the SELECT query has been run.It uses the following format: CREATE TABLE NomTable AS RequêteSélection [WITH NO DATA] In this syntax: - SelectQuery is a SELECT query. The query is run and the table is created from the result of this Select query.
- The "WITH NO DATA" option is used to create the table structure only (without the data found in the result of the Select query).
Warning: The created table may have a different index from the original table.. Note: This type of query cannot be created using the query editor wizard. The DROP TABLE statement is used to physically delete a table. It uses the following format: DROP TABLE [ IF EXISTS ] name [, ...] Remarks: - IF EXISTS is used to avoid returning an error if the table does not exist.
- This type of query cannot be created with the wizard found in the query editor.
The ALTER TABLE statement is used to change the structure of an existing table. You can add or delete columns and indexes, change the type of the existing columns and rename the columns or the table. It uses the following format:ALTER TABLE NomTable Action [, Action] .... In this syntax: - the "Action" parameters correspond to one of the following actions:
ADD [COLUMN] Description de rubrique
ADD [COLUMN] (Description de rubrique1 [,Description de rubrique2]....)
DROP [COLUMN] [IF EXISTS] NomRubrique
DROP [COLUMN] [IF EXISTS] (NomRubrique1 [, NomRubrique2]...)
ALTER [COLUMN] NomRubrique [SET DATA] TYPE alter_type_desc
ALTER [COLUMN] NomRubrique SET DEFAULT <valeur>
ALTER [COLUMN] NomRubrique DROP DEFAULT
ALTER [COLUMN] NomRubrique ADD MASKED (FUNCTION = xxxx)
ALTER [COLUMN] NomRubrique DROP MASKED
ADD [UNIQUE / PRIMARY KEY] INDEX [<NomIndex>] (Description d'index
[, Description d'index2], ...)
DROP INDEX [IF EXISTS] NomIndex
DROP INDEX [IF EXISTS] (NomIndex1 [, NomIndex2]...)
RENAME COLUMN NomRubrique TO Nouveau_NomRubrique
- The "Description of item" parameters correspond to:
NomRubrique type [DEFAULT 'valeur' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY]
[AUTO_INCREMENT] [COMMENT 'libellé'] [COLLATE 'langue'] - ItemName: name to give to the item.
- Type: Type of heading (chosen from the available types shown above).
- DEFAULT: sets the default value for the item.
- NOT NULL: prohibits assigning the value NULL to the field.
- UNIQUE: defines a heading with or without duplicates.
- PRIMARY KEY: defines a primary key.
- AUTO_INCREMENT: defines an automatic identifier for integer fields.
- COMMENT: defines the heading label.
- COLLATE: defines the sort item language (e.g. 'FR_BE').
- SECUREPASSWORD: defines that the field is of the "Password" type..
- the "Description of index" parameters correspond to:
NomRubrique [ASC | DESC] [CI] [AI] [PI] - ASC and DESC: define sorting direction.
- CI: indicates that the box is ignored.
- AI: indicates that accents are ignored.
- PI: indicates that punctuation is ignored.
- Anonymization management: ADD MASKED / DROP MASKED:
- The ADD MASKED command is used to create an anonymization mask on a file item.. The syntax is as follows:
ALTER TABLE <Nom table> ALTER COLUMN <Nom rubrique> ADD MASKED (FUNCTION = '<code fonction>')
The following cases are possible: - Random deviation: noise
The SQL command noise modifies the original value by applying a ratio (coefficient between 0 and 1).. The calculated value will be returned instead of the original value..
Syntax:
with <ratio> representing a number between 0 and 1 (not included). This is a percentage that will be applied plus or minus.
Example: - Applies a ratio of + or - 20% to the original value: noise(0.2)
- In an SQL query: Adds + or - 20% to the original value stored in the price field of the data file Product
ALTER TABLE Produit ALTER COLUMN PrixHT ADD MASKED (FUNCTION = 'noise(0.2)')
- Value replacement (or destruction)
The SQL command value modifies the original value by replacing it with a fixed string value. This fixed value will be returned instead of the original value.
Syntax: with <valeur chaîne> represents a character string with quotation marks.
Examples: - Replaces original value with "DEMO" text: value("DEMO")
- In an SQL query: Replaces the original value stored in the Status field of the Command data file with the fixed value "SOLDEE"..
ALTER TABLE Commande ALTER COLUMN Statut ADD MASKED (FUNCTION = 'value("SOLDEE")')
- Random value generation (randomization)
To generate a random value, several functions are available, depending on the type of random value to be generated. Each function will generate a random value based on the type and parameters passed to the function.. This random value will be returned in place of the original value.- function random_int_between: The random_int_between function generates a random integer value within the requested range..
Syntax: random_int_between (<valeur entière mini>, <valeur entière maxi>)
- <valeur entière mini>: Minimum possible value for the generated integer.
- <valeur entière maxi>: Maximum possible value for the generated integer.
Examples:- Generates an integer value between 5 and 90: random_int_between(5, 90)
- In an SQL query: Replaces the original value stored in the Quantity field of the CommandLine data file with a random value between 1 and 50..
ALTER TABLE LigneCommande ALTER COLUMN Quantite ADD MASKED (FUNCTION = 'random_int_between(1, 50)')
- function random_date_between: The random_date_between function generates a random date within the requested date range..
Syntax: random_date_between (<valeur date mini>, <valeur date maxi>)
- <valeur date mini> represents the minimum possible value for the generated date.
- <valeur date maxi> represents the maximum possible value for the generated date.
Values <date mini> and <date maxi> must be enclosed in quotation marks and in YYYYMMDD format..
Examples:- Generates a date between January 01, 2022 and December 31, 2022: random_date_between ("20220101", "20221231")
- In an SQL query: Replaces the original value stored in the DateCde field of the Order data file with a random date between January 1, 2023 and July 31, 2023..
ALTER TABLE Commande ALTER COLUMN DateCde ADD MASKED (FUNCTION = 'random_date_between("20230101", "20230731")')
- Function lorem_ipsum
The lorem_ipsum function generates a character string containing a sequence of random letters, according to the requested length..
Syntax:
lorem_ipsum (<longueur de la chaîne générée>)
Examples:- Generates a string with a length of 80: lorem_ipsum (80)
- In an SQL query: Replaces the original value stored in the Comments section of the Command data file with a random string 200 characters long..
ALTER TABLE Commande ALTER COLUMN Commentaires ADD MASKED (FUNCTION = 'lorem_ipsum(200)')
- Partial scrumbling
The partial function replaces part of a field string with a fixed series of characters. Only the 'target' portion of the characters will be replaced. It is possible to leave part of the beginning of the string (prefix) and/or part of the end of the string (suffix) as they are.. Original chain size remains unchanged. If the original value is too short in relation to the defined parameters of the partial function, the prefix or suffix part will not be exposed..
Syntax:
partial(<longueur du préfixe>, <Chaîne de remplacement>, <longueur du suffixe>)
- <Chaîne de remplacement> represents the quoted string that will replace the equivalent part in the original string. This value will be repeated until the maximum size of the original string is reached (including, of course, prefix and suffix lengths)..
- <longueur du suffixe> is an integer representing the size of the end of the string not to be exposed..
Examples:- Generates a string with a length of 80: lorem_ipsum (80)
- In an SQL query: Replaces part of the original value stored in the Customer data file name field with a fixed string "DEMO".. The first 3 characters of the string and the last 2 characters are not replaced..
ALTER TABLE Client ALTER COLUMN Nom ADD MASKED (FUNCTION = 'partial(3, "DEMO", 2)') - In an SQL query: Replaces part of the original value stored in the Customer data file name field with a row of the fixed string "*".. The first 1 character of the string and the last character are not replaced..
ALTER TABLE Client ALTER COLUMN Nom ADD MASKED (FUNCTION = 'partial(1, "*", 1)')
- Stored procedure
It is possible to customize the data transformation rule. To do this, you can define a stored procedure. This procedure will be associated with the heading of the file to be transformed.
Syntax:
procedure("<nom procédure stockée")
Be careful to return a value whose type matches the type of the hidden field..
Example in a SQL query: Replaces the original value stored in the AlertStock field of the Product data file with a randomly calculated value using the RandomStock stored procedure..
ALTER TABLE Produit ALTER COLUMN StockAlerte ADD MASKED (FUNCTION = 'procedure("RandomStock")')
- The DROP MASKED command is used to remove an anonymizing mask from a file item.. The syntax is as follows:
ALTER TABLE <Nom table> ALTER COLUMN <Nom rubrique> DROP MASKED
Remarks: - This type of query cannot be created with the wizard found in the query editor.
- To use the modified table with ALTER (without an analysis related to the project), use HDeclareExternal after the ALTER TABLE query is executed.
The CREATE INDEX statement (equivalent to the ALTER TABLE statement) is used to create indexes. This statement is used to add indexes to an existing table. It uses the following format:CREATE [UNIQUE] INDEX [NomIndex] ON NomTable (Description d'index [, Description d'index]... ) In this code, the "Description of index" parameters correspond to: NomRubrique [ASC | DESC] [CI] [AI] [PI] - ASC and DESC: define sorting direction.
- CI: indicates that the box is ignored.
- AI: indicates that accents are ignored.
- PI: indicates that punctuation is ignored.
Note: This type of query cannot be created using the query editor wizard. You have the ability to use the CREATE INDEX statement on a materialized view. In this case, the TableName parameter corresponds to the name of a materialized view. If the view is refreshed, the indexes are updated automatically: there's no need to recreate the indexes.Example: Creating a materialized view and a composite key on this view.
CREATE MATERIALIZED VIEW MaVueMaterialisee AS
SELECT
Client.Departement, Client.Aff, Client.TypeAdherent, COUNT(*) AS Qte
FROM Client
WHERE Client.Solde>0 AND Client.Type=2
AND Client.Famille IN ('A', 'D', 'O')
GROUP BY Client.Departement, Client.Aff, Client.TypeAdherent;
CREATE INDEX clecomp ON MaVueMaterialisee (Departement ASC CI AI PI,
Aff ASC CI AI PI,TypeAdherent ASC)
The DROP INDEX statement is used to physically delete an index from a table. It uses the following format:DROP INDEX [ IF EXISTS ] NomIndex ON NomTable where IF EXISTS: to avoid returning an error if the index does not exist. Note: This type of query cannot be created using the query editor wizard. GRANT queries are used to grant rights on an HFSQL server. This statement is equivalent to setting the element as hAllowed. It uses the following format:- Grant the requested rights on the specified tables to the specified users:
GRANT droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
TO utilisateur [, utilisateur [, ...]] - Grant the requested rights on the specified databases to the specified users:.
GRANT droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
TO utilisateur [, utilisateur [, ...]] - Grant the requested rights on the server.
GRANT droit[, droit [, ... ]]
TO utilisateur [, utilisateur [, ...]] - Give specified users the rights to request a section:
GRANT droit[, droit [, ... ]] ON COLUMN rubrique [, rubrique[, ...]]
FROM TABLE table TO utilisateur [, utilisateur [, ...]]
Remark: - This type of query cannot be created with the wizard found in the query editor.
- These queries cannot be used on an HFSQL Classic database, nor on a database accessed via Native Connectors.
Available rights
The available rights are: | | | SQL right | HFSQL right | | INSERT | hRightsInsert | | LOCK | hRightsLock | | MANAGE RIGHTS | hRightsChangeRights | | OWNER | hRightsChangeOwner | | MANAGE DUPLICATE | hRightsManageDuplicate | | SELECT | hRightsRead | | MAINTENANCE | hRightsMaintenance | | ALTER | hRightsAutoModif | | UPDATE | hRightsUpdate | | DELETE | hRightsDelete | | DELETE FILE | hRightsDeleteFile | | REFERENCES | hRightsChangeLink | | CONNECT | hRightsConnection | | ENCRYPTED CONNECT | hRightsEncryptedConnection | | CREATE | hRightsCreateFile | | DEBUG | hRightsDebug | | RUN PROCEDURE | hRightsRunProcedure | | MANAGE REFERENCES | hRightsManageIntegrity | | MANAGE PROCEDURE | hRightsManageProcedure | | MANAGE TRIGGER | hRightsManageTrigger | | FORBID ACCESS | hRightsNoDatabaseAccess | | BACKUP | hRightsBackup | | DELETE DATABASE | hRightsDeleteDB | | STOP | hRightsStopServer | | CHANGE PASSWORD | hRightsChangePassword | | CREATE DATABASE | hRightsCreateDB | | DISCONNECT | hRightsDisconnectClient | | SEND MESSAGE | hRightsSendMessageToClient | | MANAGE TASK | hRightsManageTask | | MANAGE SERVER | hRightsManageServer | | MANAGE USER | hRightsManageUser | | READ LOG | hRightsReadLogStat | | PRIORITY | hRightsPriority | | REPLICATE | hRightsServerReplication | | SEE USER | hRightsSeeUser | | MASK/UNMASK | hRightAnonymization |
The REVOKE queries are used to remove rights (switch the rights to hInherit) on an HFSQL server. It uses the following format:- Set the requested rights as inherited on the specified tables for the specified users.
REVOKE droit[, droit [, ... ]] ON [TABLE] table [, table [, ...]]
FROM utilisateur [, utilisateur [, ...]] - Set the requested rights as inherited on the specified databases for the specified users.
REVOKE droit[, droit [, ... ]] ON DATABASE database [, database [, ...]]
FROM utilisateur [, utilisateur [, ...]] - Set the requested rights as inherited on the server.
REVOKE droit[, droit [, ... ]] FROM utilisateur [, utilisateur [, ...]] - Inherit the requested rights on the specified fields to the specified users:
UNVOKE droit[, droit [, ... ]] ON COLUMN rubrique [, rubrique[, ...]]
FROM TABLE table TO utilisateur [, utilisateur [, ...]]
Remark - This type of query cannot be created with the wizard found in the query editor.
- These queries do not work with HFSQL Classic or Native Connectors.
Available rights: Available rights are identical to the SQL GRANT command. The table of rights is available with the GRANT command. CREATE VIEW is used to create an SQL view. SQL views are equivalent to temporary files in memory. In most cases, an SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.For more details on SQL views, see SQL view. It uses the following format: CREATE VIEW Vue [ ( Alias 1, Alias 2, ..., Alias N) ] AS RequêteSELECT In this syntax: - View is the name of the view.
- Alias 1, Alias 2, Alias N represent the alias names of the items from the SELECT query.
- SELECTQuery is the SELECT query used to select the records found in the view.
Example SQL: Creation of an SQL view containing only the company, name and telephone number of customers: CREATE VIEW V_Clients
AS SELECT Société, Nom, Téléphone FROM Clients DROP VIEW is used to delete a view.DROP VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE] - If the IF EXISTS keyword is specified and the views do not exist, an error message is generated.
- View 1, View 2, ... View N represent the list of views to delete.
- If the RESTRICT keyword is specified, the view is not deleted if it is in use.
- If the CASCADE keyword is specified, the view is deleted as well as the dependent views.
A materialized view is a persistent view: a file is created on disk with the contents of the view.. This view can be handled later by an application other than the one that created it.CREATE MATERIALIZED VIEW is used to create a materialized view. CREATE MATERIALIZED VIEW Vue [ ( Alias 1, Alias 2, ...Alias N) ]
AS instruction SELECT [WITH [ NO ] DATA] - The WITH DATA keyword creates a view with data.
- The WITH NO DATA keyword creates an empty view without data.
Remark - This type of query cannot be created with the wizard found in the query editor.
- These queries cannot operate in HFSQL Classic or with Native Connectors.
DROP MATERIALIZED VIEW is used to physically delete a materialized view from the disk.DROP MATERIALIZED VIEW [IF EXISTS] Vue 1 [, Vue 2, ...Vue N] [RESTRICT | CASCADE] - If the IF EXISTS keyword is specified and the views do not exist, an error message is generated.
- View 1, View 2, ... View N represent the list of views to delete.
- If the RESTRICT keyword is specified, the view is not deleted if it is in use.
- If the CASCADE keyword is specified, the view is deleted as well as the dependent views.
REFRESH MATERIALIZED VIEW REFRESH MATERIALIZED VIEW refreshes the content of a materialized view on the disk. The view is entirely recalculated.REFRESH MATERIALIZED VIEW Vue [WITH [ NO ] DATA] - The WITH DATA keyword refreshes the view with the new data.
- The WITH NO DATA keyword refreshes the empty view without data.
The OPTIMIZE TABLE command reorganizes and reindexes a data file, and rebuilds the indexes of that file. This operation improves performance. This function is equivalent to using the WLanguage HIndex function to reorganize and reindex a data file. OPTIMIZE TABLE NomFichier FileName represents the name of the file to be reorganized. Remark: - This function is blocking: the data file is not accessible during execution of the function. Make sure the file is not in use when the function is called.
- This function cannot be used with Native Connectors.
- By default, reindexing is performed according to the following parameters: normal reindexing, full-text index reindexing, background reindexing, with a density rate of 80%.. To use other parameters, use HIndex.
This page is also available for…
|
|
|