|
|
|
|
|
|
|
|
|
|
- Overview
- How to manage the NULL value in an item?
- Managing the NULL value
- Limitations
- How to use the NULL value in my applications?
- How to save a NULL value in an item?
- Customizing the display of the items whose value is NULL
- Query: Selection condition
- Example
- Limitations
NULL values in HFSQL
 Not available with this kind of connection
WINDEV, WEBDEV and WINDEV Mobile support NULL values in items of HFSQL data files and for all other types of access (Native, OLE DB, etc.). Therefore, when calculations are performed in your data files, the records containing a Null item will be ignored. For example, if a query calculates the average grade of students for the quarter, only the grades of the attending students will be taken into account. If a student is absent, their grade will be a Null value. How to manage the NULL value in an item? Managing the NULL value To manage the null value in your data files, you can use: - In the data model editor:
- the "Support NULL values" option in the data file description ("Details" tab). This option allows you to specify if the data file supports NULL values. In this case, you will have the ability to manage the NULL value for the different items of the data file.
- the "Allow NULL values in indices (compatible only with version 28 and later)" option in the data file description ("Details" tab). This option indicates that the key item can contain NULL values. Records whose indIices contain NULL values will be searchable.
ATTENTION: When this feature is activated, the index file generated is no longer compatible with versions lower than 28.
Only applications compiled in version 28 and later will be able to access these data files.
If an application written in version 27 and below attempts to access a file with this option enabled, an error is generated: "Error, the file version is too recent". - a "Default to NULL" option available for each item of your files ("General" tab of the item description). This option allows you to define the null value as default value for the item.
- a "NULL allowed" option for each item of your files ("General" tab of the item description). This option is used to allow (or not) the use of the NULL value for this item. By default, if NULL is supported by the data file, the "NULL Allowed" option is checked for all the items of the data file.
- In programming, several properties:
| | | Null | The Null property is used to:- Assign NULL to the item for the current record.
- Find out whether the item is associated with the NULL value for the current record.
- Define the default value of an item when the item is described programmatically.
| | NullableIndex | The NullIableIndex property determines if a key item supports the "Null" value. | | NullAllowed | The NullAllowed property is used to manage the NULL value in the items of HFSQL data files. | | NullSupported | The NullSupported property is used to manage the NULL value in HFSQL data files. |
Limitations The NULL value cannot be used: - on array items.
- on automatic identifiers
- on composite keys.
How to use the NULL value in my applications? How to save a NULL value in an item? - Check the "NULL supported" box when the data file is described in the data model editor ("Description of data file" in the context menu, "Info" tab).
By default, "NULL Allowed" is checked for all file items. - Generate the analysis.
- Check "NULL if empty" for the edit control linked to the item. If no value was entered by the user in this control, the NULL value will be automatically assigned to the item when using:
Remarks: - The "NULL if empty" option for the edit controls can also be used to manage the optional parameters of a query with parameters. For more details, see Using a query with parameters.
- If the default value of the item is NULL (box checked in the editor), HReset resets the Null property to True.
- By default, "Allow NULL values in indices (compatible only with version 28 and later)" is unchecked for data files.. This means that the data file is compatible with older versions.
If this option is unchecked, the NULL value in the index is treated as an empty string ("") for string fields, or as 0 for Numerical fields..
If this option is checked, the key field can contain the value NULL. Records whose indexes contain NULL values are searchable.. This option has an impact on journey times and index searches..
Customizing the display of the items whose value is NULL By default, if an edit control is linked to an item whose value is NULL, the value displayed in the control is "". To customize this display mode, use ControlError associated with the ceNull constant. Query: Selection condition In the query editor, you have the ability to take into account or to ignore the records that have a null item. To do so, create a selection condition and choose "Is null" or "Is not null". In a selection query that performs a calculation, all the records that have a null value (for the calculation item) will be ignored. Example The following example is a query used to calculate the average grade of students for the 2003 French class. If one of the grades corresponds to the NULL value, this grade is ignored. SELECT Marks.Course AS Course,
Marks.MarkDate AS MarkDate,
LEFT(Marks.MarkDate,4) AS Year,
AVG(Marks.Mark) AS the_average_Mark
FROM Marks
WHERE Marks.Course = 'French'
AND Marks.Mark IS NOT NULL
AND LEFT(Marks.MarkDate,4) = '2003'
GROUP BY Marks.Course, Marks.MarkDate, LEFT(Marks.MarkDate,4) - When managing the integrity, the NULL value is ignored.
- A linked record on 0 or empty string ("") is equivalent to a linked record on NULL.
- For backward compatibility, the transfer of Null by assignment is not allowed.
- Don't:
File1.Item1 = File2.Item2 - Do:
File1.Item1..Null = File2.Item2..Null
 Management of nullable types: If the "Allow nullable types" option is checked in the project description, it becomes possible to use the syntax: File1.Item1 = File2.Item2 For more details, see NULL value management: Allow null types.
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|