Welcome Guest, Not a member yet? Register   Sign In
[SOLVED] SQLite3 Error
#1

(This post was last modified: 11-20-2023, 01:58 PM by grimpirate. Edit Reason: Switching title to [SOLVED] )

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]);
 }

Reply
#2

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.
Reply
#3

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],
]);
Reply
#4

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.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB