Search code examples
sqlsqliteknex.jsnode-sqlite3

SQLITE_RANGE: bind or column out of range for INSERT statement

I want to insert rows into a SQLite3 table using the knex.raw method. Unfortunately I get a 'SQLITE_RANGE' error, which makes my test fail. I have verified the bindings passed to the raw query in the following fashion:

  • They respect the order of the INSERT statement
  • They respect the specified column types
  • They respect the number of bindings requested in the raw query

Beyond that I have looked online, but couldn't find a solution to my issue. Below are the details of the operation attempted:

Error: 

SQLITE_RANGE: bind or column index out of range errno: 25, code: 'SQLITE_RANGE'

Table definition:

-- --------------------------------------------------------

--
-- Table structure for table `ds13odba`
--

CREATE TABLE IF NOT EXISTS `ds13odba` (
  `SURGERY_CODE` VARCHAR(6) ,
  `TYPE` VARCHAR(1) ,
  `FP59STALIB` VARCHAR(6) ,
  `ID` INT UNSIGNED NOT NULL ,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL

);

-- --------------------------------------------------------

*Take note that the column types defined here are affinity types, i.e. MySQL types. These are valid in SQLite3 and are casted and optimized by the engine to their equivalent in SQLite3.

Query:

INSERT INTO `ds13odba` (FP59STALIB, ID, SURGERY_CODE, TYPE) VALUES (?,?,?,?);
INSERT INTO `ds13odba` (FP59STALIB, ID, SURGERY_CODE, TYPE) VALUES (?,?,?,?);
INSERT INTO `ds13odba` (FP59STALIB, ID, SURGERY_CODE, TYPE) VALUES (?,?,?,?);

Bindings:

[ 
  '047202', 1, '000001', 'D',
  '047203', 2, '000002', 'D',
  '047204', 3, '000003', 'D' 
]

Calling code:

await knex.raw(...convertToInsertSQL(records));

Which resolves to:

await knex.raw(insertStatements.join('\n'), bindings);

Could you help me with this issue?

Cheers 🦋

Solution

  • The issue stems from SQLite3's lack of support of multi-statements per exec() call, as documented here.

    After some testing on my end, I discovered that the SQLite3 engine will assign automatically all the bindings to the first statement of the prepared SQL. Any following statements will be ignored.

    This still applies for transactions, as the bindings will be applied to the 'BEGIN TRANSACTION;' statement rather than to the following statements.

    The solution is to use a compound INSERT statement with bindings.

    Hence this:

    INSERT INTO `ds13odba` (FP59STALIB, ID, SURGERY_CODE, TYPE) VALUES (?,?,?,?);
INSERT INTO `ds13odba` (FP59STALIB, ID, SURGERY_CODE, TYPE) VALUES (?,?,?,?);
INSERT INTO `ds13odba` (FP59STALIB, ID, SURGERY_CODE, TYPE) VALUES (?,?,?,?);

    becomes this:

    INSERT INTO `ds13odba` (FP59STALIB, ID, SURGERY_CODE, TYPE)
  VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?);

    *Bear in mind that compound INSERT statements are only available as of version 3.7.11 of the SQLite3 engine.