[SOLVED] SQLite3 Error

Code:

/var/www/localhost/htdocs/sub # php spark db:seed FormulaSeeder

CodeIgniter v4.4.3 Command Line Tool - Server Time: 2023-11-20 12:55:59 UTC-05:00


[CodeIgniter\Database\Exceptions\DatabaseException]

SQLite3::exec(): near ")": syntax error

at SYSTEMPATH/Database/BaseConnection.php:647

This exception is thrown via CLI which appears to be prompted by my use of insertBatch method and the inclusion of 'content_4_id' into the third row (the column exists in the table and is nullable). I know this because I ran the seeder without the third row and it processed successfully. However, when I change my code (as a workaround) with individual insert methods, no exception is thrown. Is this a bug in the CodeIgniter system code?

MALFUNCTIONING FormulaSeeder.php

PHP Code:

<?php

namespace App\Database\Seeds;

use CodeIgniter\Database\Seeder;

class FormulaSeeder extends Seeder
{
public function run()
{
$this->db->table('formula')->insertBatch([
['url_id' => 1, 'source_id' => 1, 'medium_id' => 1, 'content_1_id' => 1, 'content_2_id' => 1],
['url_id' => 1, 'source_id' => 2, 'medium_id' => 1, 'content_1_id' => 2, 'content_2_id' => 2],
['url_id' => 1, 'source_id' => 2, 'medium_id' => 1, 'content_1_id' => 2, 'content_2_id' => 2, 'content_4_id' => 1],
]);
}
} 


WORKAROUND FormulaSeeder.php

PHP Code:

<?php

namespace App\Database\Seeds;

use CodeIgniter\Database\Seeder;

class FormulaSeeder extends Seeder
{
 public function run()
 {
 $this->db->table('formula')->insert(['url_id' => 1, 'source_id' => 1, 'medium_id' => 1, 'content_1_id' => 1, 'content_2_id' => 1]);
 $this->db->table('formula')->insert(['url_id' => 1, 'source_id' => 2, 'medium_id' => 1, 'content_1_id' => 2, 'content_2_id' => 2]);
 $this->db->table('formula')->insert(['url_id' => 1, 'source_id' => 2, 'medium_id' => 1, 'content_1_id' => 2, 'content_2_id' => 2, 'content_4_id' => 1]);
 }
} 


Halberd | CodeIgniter 4 PowerShell Setup Script

Your dataset for insertBatch() is not symmetrical. You must use symmetrical data for any of the *Batch() methods. You need to define content_4_id in the first and second row as well.

Try:

Code:

$this->db->table('formula')->insertBatch([
['url_id' => 1, 'source_id' => 1, 'medium_id' => 1, 'content_1_id' => 1, 'content_2_id' => 1, 'content_4_id' => null],
['url_id' => 1, 'source_id' => 2, 'medium_id' => 1, 'content_1_id' => 2, 'content_2_id' => 2, 'content_4_id' => null],
['url_id' => 1, 'source_id' => 2, 'medium_id' => 1, 'content_1_id' => 2, 'content_2_id' => 2, 'content_4_id' =>    1],
]);

The offending SQL statement that is generated for the insertBatch statement is

Code:

INSERT INTO `formula` ()
VALUES (1,1,1,1,1), (2,2,1,2,1), ()

as output by an inserted die($sql) statement on line 143 of vendor/codeigniter4/framework/system/Database/SQLite3/Connection.php. I would infer that because I'm changing the keys from row to row this produces the exception. Presumably for an insertBatch command to process correctly, it should have the same associative keys throughout each row. This may be a feature rather than a bug, although admittedly it seems a bit like a gotcha. Perhaps it should be noted in the documentation as the error isn't really an SQL execution problem but moreso an improper parametrization issue. Seems as though insertBatch should compensate for different keys in rows given that the workaround means doing a foreach loop to traverse an array and perform multiple inserts rather than one function call on the whole array, but that's just my two cents.

Halberd | CodeIgniter 4 PowerShell Setup Script