- Characteristics of created data file
- The different created files
- Managing passwords
- Managing fatal errors when creating a data file
- Managing non-Latin character sets
- Replication: Range of identifiers
- Priority for defining the location of log files
- Creating an existing table
- Constraints for referential integrity
- OLE DB Oracle: Creating a table containing a binary memo
- OLE DB: Creating a table containing automatic identifiers
- Managing indexes
- Creating the data file from its description
- DATE and TIME items with the Native SQL Server Connector
- <Source>.Creation and Native SQLite Connector
In french: <Source>.Création
Not available with this kind of connection
Creates an empty data file (.FIC extension) with the index file and the memo file if necessary.
This function can also be used to delete all records from a data file. In this case, the record in memory is not canceled. To initialize it, use <Source>.Reset
: The automatic creation of non-existing data files can be enabled in the project description ("Files" tab of the project description, "Automatically create the data files when they are opened"). <Source>.Creation
is automatically used if the data file does not exist when it is handled for the first time.
If the data file is logged, you have the ability to specify the location of log files.
If integrity constraints have been defined and if they are supported, the links are updated on the server.
Remark: From version 19, HFSQL is the new name of HyperFileSQL.
// Create the Orders data file (described in the analysis)
<Result> = <Source>.Creation([<Password> [, <Path of JNL file> [, <Path of JournalOperation file> [, <Option>]]]])
- True if the data file was created,
- False if a problem occurred. HError is used to identify the error. If a fatal error occurs, a specific process is required to continue the program and to check whether the data file was created (see the Notes).
<Source>: Type corresponding to the specified source
Name of data file to create.
MySQL: the table name must contain no accented character (for example: "ÉlanFile"). This is a problem specific to MySQL (tested on versions 3.23.52-max-nt and 3.23.47-max-nt of the MySQL server).
<Password>: Optional character string (with quotes)
- Password associated with the data file.
The password of HFSQL files is not case-sensitive.
- Empty string ("") if no password is used.
<Path of JNL file>: Optional character string (with quotes)
Access path to JNL file associated with the logged data file. This parameter is taken into account if the data file is logged. In this case, <File name>JNL.fic will be created at the specified location.
This parameter must not be specified. In HFSQL Client/Server, the path of JNL file is defined and it cannot be modified.
This parameter is ignored.
<Path of JournalOperation file>: Optional character string (with quotes)
Access path to JournalOperation file and to JournalIdentification file associated with the logged file. The JournalOperation file contains all the operations performed on the file with logs. This parameter is taken into account if the data file is logged.
This parameter must not be specified. In HFSQL Client/Server, the path of operation file is defined and it cannot be modified.
This parameter is ignored.
<Option>: Optional Integer constant
Options for creating the data file:
|hChangeCharset||The character set defined by ChangeCharset will be taken into account in the new data file. The sorts, the searches, ... on string keys (strings, characters, date and time) will be performed according to this character set.|
|hNoLink||Management of integrity: The links are not updated on the server (creation, modification or deletion).|
Reminder: by default, the links are updated on the server when creating a data file.
This constant is available in HFSQL Client/Server mode only.
This parameter is ignored.
Characteristics of created data file
The characteristics of created data file are as follows:
- Name of data file:
Physical name defined during the file description in the data model editor. This name can be modified by <Source>.ChangeName before creating the data file.
- Directory of data file:
Directory defined during the file description in the data model editor. This directory can be modified by <Source>.ChangeDir and HSubstDir before creating the data file.
- Content of data file:
The created data file is empty, which does not mean that its size is null (the file contains a header describing its structure).
- The order of items created in the physical file is the physical order of items created in the analysis (and not the logical order).
If the data file already existed: If the data file already existed (same name, same directory), this data file is recreated: the data file contains no record. The index file and the memo file (if they exist) are also recreated. In this case, the integrity constraints are not checked.
: Re-creating the data file is possible only if the data file is not used by another application. If a file currently used must be cleared, use <Source>.Delete
during a browse. You can also use <Source>.DeleteAll
to delete all records from a data file.
- The password can be defined:
- when the data file is created by <Source>.Creation.
- before the data file is created by <Source>.Password.
- If the data file existed and if it was password protected, the data file is re-created, even if the password changes or does not exist anymore.
: The password is independent of data encryption. The data encryption must be configured during the file description in the data model editor ("Details" tab).
The password is not associated with a data file but with a database. This password must be specified during the connection (HConnect
Managing fatal errors when creating a data file
If a fatal error occurs when creating a data file, it may be interesting to continue the program while checking whether the data file was created or not. To do so, we recommend that you perform the following exception process:
WHEN EXCEPTION IN
Error("HFSQL error: " + HError())
Trace("The file is created")
Managing non-Latin character sets
If the hChangeCharset
constant is specified, the character set used in the data file will be the one specified by ChangeCharset
. The sorts, the searches, ... on string keys (strings, characters, date and time) will be performed according to this character set.
Remark: The size of string items specified in the analysis is expressed in bytes and not in characters. Depending on the character set used, you may have to increase the item size (if the characters used are coded on 2 bytes for example).
Priority for defining the location of log files
The location of log files is defined according to the following priority order:
- Location specified when creating the logged data file (<Source>.Creation).
- Location specified by <Source>.ChangeLogDir.
- Location specified in the analysis.
- Location of logged data file.
If another application is using <Source>.DeclareExternal
on a logged data file, the modifications performed by this application are saved in the log files specified in the header of the logged HFSQL data file.
Creating an existing table
If the table already existed, <Source>.Creation tries to delete and to re-create the table.
For example: Using an Oracle database
- with the OLE DB "MSDAORA" provider: a new table cannot be created. If the table already exists, the records are erased.
- with the OLE DB "oraOLEDB.Oracle" provider: the creation of a new table is possible as well as the re-creation of an existing table. However, no index or memo item can be created.
When creating a table, WINDEV and WEBDEV try to create the items in the OLE DB type corresponding to the HFSQL type. If this type is not supported, WINDEV and WEBDEV successively try all the "compatible" OLE DB types until they find a type supported by the OLE DB database.
OLE DB Oracle: Creating a table containing a binary memo
Caution: creating an Oracle table containing binary memos fails in Oracle 7.x (because the LOBs are not supported in this version of Oracle).
OLE DB: Creating a table containing automatic identifiers
The automatic identifier will be created in the table only if the OLE DB provider supports the automatic identifiers.
If an index failed to be added, no error message is returned by <Source>.Creation. The table is created without the index that is causing the problem. In this case, duplicates may be found in an item defined as unique key by WINDEV and WEBDEV if the external database does not support the unique keys.
Creating the data file from its description
The data file can be directly created from its description in the data model editor. To do so, select "Create the data file" and select the directory where the data file must be created.
DATE and TIME items with the Native SQL Server Connector
When using <Source>.Creation, the Native SQLServer Connector creates DATE and TIME items if the server version and the client layer version correspond to SQL Server 2008 (or later).
<Source>.Creation and Native SQLite Connector
<Source>.Creation may fail ("database table is locked" error) if the following conditions are fulfilled:
- the file to create already exists.
- a browse is in progress on another file of the same database.
This is caused by a limitation of SQLite database.
This page is also available for…