When inserting multiple rows to SQLite3 in PHP using a prepared statement, if you don't bind a parameter for a row then the value from the previous row will be inserted, even if you "clear" the statement between lines.
Look at the following example:
$db = new SQLite3('dogsDb.sqlite');
//create the database
$db->exec("CREATE TABLE Dogs (Id INTEGER PRIMARY KEY, Breed TEXT, Name TEXT, Age INTEGER)");
$sth = $db->prepare("INSERT INTO Dogs (Breed, Name, Age) VALUES (:breed,:name,:age)");
$sth->bindValue(':breed', 'canis', SQLITE3_TEXT);
$sth->bindValue(':name', 'jack', SQLITE3_TEXT);
$sth->bindValue(':age', 7, SQLITE3_INTEGER);
$sth->execute();
$sth->clear(); //this is supposed to clear bindings!
$sth->reset();
$sth->bindValue(':breed', 'russel', SQLITE3_TEXT);
$sth->bindValue(':age', 3, SQLITE3_INTEGER);
$sth->execute();
Even though I would expect the second line to have a NULL value for the 'name' column, the value is 'jack' instead!
So either 'clear' doesn't seem to work (although it returns true) or I haven't really understood what it's supposed to do.
How can I clear the bindings between inserts in SQLite3 (or even PDO)? What's the best way to insert multiple rows where some rows might have null values for some fields?
Aucun commentaire:
Enregistrer un commentaire