envms
Insert drops "null" binds
PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
INSERT INTO track (filename, artist, title, track_length, filesize, status, owner_user_id, play_vote, con_vote, date_added, date_last_played, alltime_votes, old_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Original bind:
array(13) { 'filename' => string(8) "/foo.mp3" 'artist' => string(6) "Artist" 'title' => string(5) "Title" 'track_length' => int(100) 'filesize' => int(1005000) 'status' => string(5) "added" 'owner_user_id' => int(1) 'play_vote' => int(100500) 'con_vote' => int(0) 'date_added' => string(19) "2018-11-13 10:46:54" 'date_last_played' => NULL 'alltime_votes' => int(0) 'old_id' => NULL }
Query:
string(211) "INSERT INTO track (filename, artist, title, track_length, filesize, status, owner_user_id, play_vote, con_vote, date_added, date_last_played, alltime_votes, old_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" Bind parameters at envms/fluentpdo/src/Queries/Base.php:192 array(11) { [0] => string(8) "/foo.mp3" [1] => string(6) "Artist" [2] => string(5) "Title" [3] => int(100) [4] => int(1005000) [5] => string(5) "added" [6] => int(1) [7] => int(100500) [8] => int(0) [9] => string(19) "2018-11-13 10:48:25" [10] => int(0) }
Why there are only 11 parameters? There are 13 fields to insert. Parameters builder dropped 2 null fields from bind. That's a bug.
Hey @sanovskiy, this is not a bug and functioning as intended. If you want nulls converted to the SQL null type, call $fluent->convertWriteTypes(true); before running any queries.
But null values in bind leads to mysql error. Mismatch count of bind values and params in query string. Is it proper behavior?
If you don't convert the types then yes, it's expected behaviour. During the parameter assignment, every value is checked against false and null, which is where your parameter count mismatch comes into play. The null values are removed, hence the 11 params. Use what I suggested above and the mismatch will not happen with null values.
If you drop null values from bind you should drop relevant placeholders from query to avoid errors
Sure, there's a case for that approach. However, there's also the potential for bad data if you do remove the placeholders instead of throwing an error.
Say you have a default value of 0 for a certain column, that 0 would then silently take the place of the null value passed without the developer's knowledge.
fluentpdo is not an ORM. It's query builder. Too much logic in query builder is evil. What if null is default value for my column? What if there is need to use null column value to display no relation to other entity table on one-to-one relation?
You're asking for more logic here. The error thrown is by PDO itself. If null is the default value, then passing a null value isn't an issue is it?
$fluent->convertWriteTypes(true); solves your exact problem and will pass null types as expected. All you need to do is call it once and every query will write nulls as expected.
(new Query($pdo))->insert('table',['name'=>'newName','ref'=>null])->execute();
Will lead to sql error.
insert into table (name, ref) values (?,?)
Query is ok. Bind is ['newName']. This leads to bind count error
If you drop null bind query should look like
insert into table (name) values (?)
$fluent->convertWriteTypes(true) do not work for query parameters.
For that I commented validation in Queries/Base.php in function buildParameters()
if ($value !== null) {
$parameters[] = $value;
}