Issue when using non_agent_agi.php

[jessiekidfernando]

Hello Everyone,

We are inserting leads via non_agent_api.php where we are just leaving the date_of_birth as "", however we are seeing it as 0000-00-00. I believe it should be NULL. Anyone know why?

MariaDB [asterisk]> desc vicidial_list;
+-------------------------+------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| lead_id | int(9) unsigned | NO | PRI | NULL | auto_increment |
| entry_date | datetime | YES | | NULL | |
| modify_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| status | varchar(6) | YES | MUL | NULL | |
| user | varchar(20) | YES | | NULL | |
| vendor_lead_code | varchar(50) | YES | | NULL | |
| source_id | varchar(50) | YES | | NULL | |
| list_id | bigint(14) unsigned | NO | MUL | 0 | |
| gmt_offset_now | decimal(4,2) | YES | MUL | 0.00 | |
| called_since_last_reset | enum('Y','N','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9','Y10') | YES | MUL | N | |
| phone_code | varchar(10) | YES | | NULL | |
| phone_number | varchar(18) | NO | MUL | NULL | |
| title | varchar(4) | YES | | NULL | |
| first_name | varchar(30) | YES | | NULL | |
| middle_initial | varchar(1) | YES | | NULL | |
| last_name | varchar(30) | YES | | NULL | |
| address1 | varchar(100) | YES | | NULL | |
| address2 | varchar(100) | YES | | NULL | |
| address3 | varchar(100) | YES | | NULL | |
| city | varchar(50) | YES | | NULL | |
| state | varchar(2) | YES | | NULL | |
| province | varchar(50) | YES | | NULL | |
| postal_code | varchar(10) | YES | MUL | NULL | |
| country_code | varchar(3) | YES | | NULL | |
| gender | enum('M','F','U') | YES | | U | |
| date_of_birth | date | YES | | NULL | |
| alt_phone | varchar(12) | YES | | NULL | |
| email | varchar(70) | YES | | NULL | |
| security_phrase | varchar(100) | YES | | NULL | |
| comments | varchar(255) | YES | | NULL | |
| called_count | smallint(5) unsigned | YES | | 0 | |
| last_local_call_time | datetime | YES | MUL | NULL | |
| rank | smallint(5) | NO | MUL | 0 | |
| owner | varchar(20) | YES | MUL | | |
| entry_list_id | bigint(14) unsigned | NO | | 0 | |
+-------------------------+------------------------------------------------------------------+------+-----+-------------------+-----------------------------+

Thanks in advance.

[jjohnson78]

That's just how MySQL/MariaDB handles a "" value on a column designated as type 'date'. It also triggers a warning on the DB (nothing major):

+---------+------+----------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 1 |
+---------+------+----------------------------------------------------+

In other words, this isn't the API, it's the database auto-correcting data. The field CAN be null, but only if you actually pass NULL in your SQL (or don't include the date_of_birth column in the INSERT statement).