$ADODB_FORCE_TYPE
The behaviour of autoExecute(), getUpdateSql() and getInsertSql() when converting empty or null PHP variables to SQL is controlled by the global $ADODB_FORCE_TYPE variable.
The default value is ADODB_FORCE_VALUE
Constants
Constant | Value | Description |
---|---|---|
ADODB_FORCE_IGNORE | 0 | All empty fields in array are ignored |
ADODB_FORCE_NULL | 1 | All empty php null and string 'null' fields are changed to sql NULL values. Earlier release of ADOdb used ADODB_FORCE_NULLS |
ADODB_FORCE_EMPTY | 2 | All empty php null and string 'null' fields are changed to sql empty or 0 values |
ADODB_FORCE_VALUE | 3 | Value is left as it is. PHP null and string 'null' are set to sql NULL values. Empty fields are set to empty SQL values. This is the default value |
New in version 5.21.0 | ||
ADODB_FORCE_NULL_AND_ZERO | 4 | If the field is of a string type, PHP character null and string 'null' are set to SQL NULL values. If the field is of an integer,logical or float type and is empty, it will be set to zero |
Usage
$ADODB_FORCE_TYPE = ADODB_FORCE_EMPTY;
Guidance
Using ADODB_FORCE_IGNORE
Using this value while generating SQL statements creates more compact, efficient statements, it is impossible to deliberately set an existing value to NULL, as ADOdb will simply ignore the field whilst creating the update statement. To set a field empty, it must be set to the string value 'null'.
The difference between EMPTY and NULL
When developing an application it is important to define a standard for fields with no value in them. the difference between the 2 is easily demonstrated in the following code snippet:
$SQL = "SELECT * FROM some_table WHERE character_field = ''";
or:
$SQL = "SELECT * FROM some_table WHERE character_field IS NULL";
How The Different Constants Work
Assuming a table definition like this:
CHAR1 C(10), CHAR2 C(10), CHAR3 C(10), INT1 I, INT2 I
Assuming the Values to be inserted are
CHAR1 = 'ABC' CHAR2 = NULL CHAR3 = '' INT1 = 10 INT2 = NULL INT3 = 0
The SQL statements generated are as follows:
ADODB_FORCE_IGNORE
INSERT INTO TABLE (CHAR1,INT1,INT3) VALUES ('ABC',10,0)
ADODB_FORCE_NULL
INSERT INTO TABLE (CHAR1,CHAR2,CHAR3,INT1,INT2,INT3) VALUES ('ABC',null,null,10,null,0)
ADODB_FORCE_EMPTY
INSERT INTO TABLE (CHAR1,CHAR2,CHAR3,INT1,INT2,INT3) VALUES ('ABC','','',10,0,0)
ADODB_FORCE_NULL_AND_ZERO
INSERT INTO TABLE (CHAR1,CHAR2,CHAR3,INT1,INT2,INT3) VALUES ('ABC',null,null,10,0,0)