1

I'm trying to insert a record in an automobile table. Here's a trimmed down version of the table.

NOTE: this is not a real life working model, should not be used or evaluated as such. I'm just showing the problem I'm having in a simplified way. 

mysql> describe desc_autos; +-----------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------------------+----------------+ | car_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | automobile_name | varchar(25) | NO | MUL | | | +-----------------+------------------+------+-----+---------------------+----------------+ 7 rows in set (0.00 sec) mysql> select count(*) FROM desc_autos WHERE automobile_name IS NULL; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) FROM desc_autos WHERE automobile_name = ''; +----------+ | count(*) | +----------+ | 19322 | +----------+ 1 row in set (0.02 sec) +-------------+ CREATE TABLE `desc_autos` ( `car_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `automobile_name` varchar(15) NOT NULL, PRIMARY KEY (`car_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +-------------+ 1 row in set (0.00 sec)

So you see the automobile_name field is NOT NULL, and blank values are being shown for many records. (Disregard field names - just accept there is a field with non-null values)

I am converting from native MySQL functions to PDO. If I insert a record with a non-blank automobile_name value, no problem - works fine. If automobile_name is blank, PDO balks. 

 $automobile_name = ''; try { $q = " INSERT INTO desc_autos ( automobile_name ) VALUES ( :automobile_name ) "; $stmt = $dbx_pdo->prepare($q); $stmt->bindParam(':automobile_name', $automobile_name, PDO::PARAM_STR); $stmt->execute(); return true; } catch(PDOException $err) { log_error(); return false; } }

This results in the following error: 

Error: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'automobile_name' cannot be null

I thought I had to implicitly tell PDO to insert a NULL value, otherwise blank values would be inserted as... blank values.

Ex:

$stmt->bindValue(':automobile_name', !empty($automobile_name) ? $automobile_name : NULL, PDO::PARAM_STR);

I'm not needing to insert NULLs in this case - I need to drop a blank, non-null value in the given field.

The question:

Why is PDO treating a blank value as null?

Improve this question
5
  • Are you implying the IS NULL worked with mysql_query()? Because PDO doesn't really influence MySQL expressions. As for inserting, you're binding the value with PDO::PARAM_STR, which will turn NULL values into blank strings. Commented Dec 16, 2014 at 18:48
  • @mario My reaction too, but at the top is $automobile_name = ''; explicitly setting it to the empty string. Commented Dec 16, 2014 at 18:50
  • @mario, prior to this the insert query looked like $q = "INSERT INTO desc_autos (automobile_name) VALUES ( $automobile_name )"; then with mysqli_query( $dbx, $q); If $automobile_name was blank (or even null), it would be inserted as a blank value in MySQL. Commented Dec 16, 2014 at 18:56
  • 2
    Okay. Seems I misread entirely what you want anyway. Go for the non-/implicit conversion flag as advised by @MB, try ->bindValue (often better retains types), or adapt your table scheme (e.g. DEFAULT "" or something). Commented Dec 16, 2014 at 18:58
  • @Mario could you add an answer regarding changing table schema? Setting automobile_name to accept NULL values eliminated the error. I then converted existing blank values to NULL with an UPDATE. This initially appears to resolve the issue in my dev system. @MichaelBerkowski has a good answer too. Commented Dec 16, 2014 at 19:12

1 Answer 1

Reset to default
5

It sounds like you need to set the ATTR_ORACLE_NULLS (nevermind the name) to the appropriate setting so it does not convert empty strings to NULL. Apparently your current value is PDO::NULL_EMPTY_STRING causing the empty string '' to be nulled.

// Set it to "natural" mode so NULLs and empty strings aren't modified $dbx_pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);

Documentation: PDO connection attributes

Note: According to my quick test, PDO::NULL_NATURAL would appear to be the default setting, but perhaps yours was modified elsewhere.

Based on your SHOW CREATE TABLE output, the automobile_name column is NOT NULL but doesn't explicitly have a default defined. I would probably take the approach of setting the default to the empty string '' and allowing PDO to insert NULL. As mario alluded in the main comment thread:

ALTER TABLE desc_autos MODIFY `automobile_name` varchar(15) NOT NULL DEFAULT '';

In the PDOStatement bindParam(), you may then use either a PHP null or the empty string '' to force the database to use its default. That would require no changes to your current line:

$stmt->bindParam(':automobile_name', $automobile_name, PDO::PARAM_STR);
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

While I don't know why it would be named like that, I can confirm that in oracle db, NULL == ''. Annoying at times when you are used to other databases.
Just tried this and continue to get the error - see my comment to the Question for what I was using before. Looks like I need to modify the table scheme per @mario.
@acoder Are you using emulated prepares or real ones? With emulation, I could get NULL to convert to the empty string by modifying this attr, but not the other way around (which is what you want). With real, non-emulated prepares I can't either to make a difference. But looking at your table, it looks like you already have '' as the default value. Can you post SHOW CREATE TABLE desc_autos?
NOte that this is potentially solved now - I changed automobile_name to accept null values in my dev system and this eliminated the error.
@acoder Well if you don't want nulls, still you should go ahead with changing it to DEFAULT ''
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.