Unable to seed rows using CURRENT_TIMESTAMP

[phrfpeixoto]

Hello, I'm trying to write a simple seeder:

class MySeeder extends AbstractSeed
{
    public function run(): void
    {
        $data = [
            [
                'user_name' => 'Administrator',
                'user_creation' => 'CURRENT_TIMESTAMP',
            ]
        ];
        $table = $this->table('users');
        $table->insert($data)->saveData();
    }
}

Executing this seeder fails:

[PDOException (22007)]
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: 'CURRENT_TIMESTAMP' for column 'user_creation' at row 1

I explored the code a bit, and tried using Literal and Expression, but got the same result.
Is there a way to achieve this?

[MasterOdin]

Yeah, this is currently not possible, and is a bug in this block of code:

phinx/src/Phinx/Db/Adapter/PdoAdapter.php

Lines 378 to 415 in 5f9ee84

	public function bulkinsert(Table $table, array $rows): void
	{
	$sql = sprintf(
	'INSERT INTO %s ',
	$this->quoteTableName($table->getName())
	);
	$current = current($rows);
	$keys = array_keys($current);
	$sql .= '(' . implode(', ', array_map([$this, 'quoteColumnName'], $keys)) . ') VALUES ';
	if ($this->isDryRunEnabled()) {
	$values = array_map(function ($row) {
	return '(' . implode(', ', array_map([$this, 'quoteValue'], $row)) . ')';
	}, $rows);
	$sql .= implode(', ', $values) . ';';
	$this->output->writeln($sql);
	} else {
	$count_keys = count($keys);
	$query = '(' . implode(', ', array_fill(0, $count_keys, '?')) . ')';
	$count_vars = count($rows);
	$queries = array_fill(0, $count_vars, $query);
	$sql .= implode(',', $queries);
	$stmt = $this->getConnection()->prepare($sql);
	$vals = [];
	foreach ($rows as $row) {
	foreach ($row as $v) {
	if (is_bool($v)) {
	$vals[] = $this->castToBool($v);
	} else {
	$vals[] = $v;
	}
	}
	}
	$stmt->execute($vals);
	}
	}

The root here is that when inserting, we create a prepared statement where all values are then binded. Stuff like CURRENT_TIMESTAMP (or any function really) cannot be a binded parameter, but should be in the SQL directly. As such, the fix would be that when we go to construct the SQL statement, instead of just saying all values are going to be binded, we need to step through each, and either insert the literal (if using Literal) or a ? to be binded.

[MasterOdin]

I think the bug also exists in the PdoAdapter::insert method as well, so will want to fix it in both places.

[MasterOdin]

Using literals is now supported as of 0.16.3 released today.