SET TRUENULLCREATE
The SET TRUENULLCREATE statement turns on or off true nulls when you create new tables.
Syntax
SET TRUENULLCREATE = < on | off >
Remarks
This setting first appeared in Pervasive.SQL 2000 (7.5). On is the default, causing tables to be created with a NULL indicator byte at the beginning of each empty field. If it is set to off by a SQL statement, tables are created from then on using the legacy NULL from Pervasive.SQL 7 and earlier releases. The legacy null behavior persists until the session is disconnected. In a new session, the setting is on again.
Since connections each have their own TRUENULLCREATE setting, they can differ even within the same application.
Even though they are not true nulls, legacy nulls behave as nullable, and you can INSERT NULL into any column type. When you query the value, however, one of the following nonnull binary equivalents is returned:
•0 for Binary types
•Empty string for STRING and BLOB types, including legacy types such as LVAR and LSTRING
Accordingly, you must use these equivalents in WHERE clauses to retrieve specific values.
The following table describes the interaction between default values and nullable columns.
Column Type | Default value used if no literal default value is defined for the column | Default value if literal value is defined |
Nullable | NULL | As defined |
Not NULL | Error – "No default value assigned for column" | As defined |
Pre-v7.5 nullable | The legacy null for the column | As defined |
If a statement attempts to insert an explicit NULL into a NOT NULL column that has a default value defined, the statement fails with an error. The default value is not used in place of the attempt.
For any column with a default value defined, that value may be invoked in an INSERT statement by omitting the column from the insert column list or using the keyword DEFAULT for the insert value.
If all columns in a table are either nullable or have default values defined, you can insert a record with all default values by using DEFAULT VALUES as the values clause. If any column is not nullable and no default is defined, or if you want to specify a column list, you cannot use this type of clause.
Using DEFAULT VALUES for BLOB, CLOB, or BINARY data types is not currently supported.
Examples
To toggle the setting and create new tables with legacy null support in the current session, use:
SET TRUENULLCREATE=OFF
To return the engine to the default and create tables with true null support in the current session, use:
SET TRUENULLCREATE=ON