ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:reference:adodb_force_type

$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) 
v5/reference/adodb_force_type.txt · Last modified: 2016/02/28 01:53 by mnewnham