CODEWITHSUNDEEP
sqlnode.jssqlitenode-sqlite3
micronyks
micronyks

Reputation: 55443

SQLITE_RANGE: column index out of range : SQLite

I have a simple sqlite SELECT query as follow,

db.js

const dbFile = path.resolve(__dirname, ../disk.db);
const dbExists = fs.existsSync(dbFile);


if (!dbExists) {
   console.log("database file doesn't exist")
   fs.openSync(dbFile, 'w');
}


console.log("database file exists")

exports.db= new sqlite3.Database(dbFile, sqlite3.OPEN_READONLY, (err) => {
  if (err) {
      console.error(err.message);
  }
  console.log(`Connected to disk database`);
});

query.js

const database= require("../db.js"); 
const studentDB = database.db;   

const rollNos = [12, 15];

const query = `Select * from Student where rollNo in (?)`

CommonQuery.executeQuery(query, rollNos, studentDB, cb)

common.query.js

class CommonQuery {

    excecuteQuery(query, param, db, cb) {
        db.serialize(() => {
          db.all(query, param,
            (error, rows) => {
              if (error) {                     // it throws error --- SQLITE_RANGE: column index out of range 
                console.log("error occured in executing query !", query, error);
                return cb(error);
              }
    
              return cb(null, rows);
            })

        });
    
      }
    }
}

If I execute below query in sqlite database editor , it works fine

Select * from Student where rollNo in (12, 15)

Upvotes: 1

Views: 1184

Answers (1)

mighty_mike
mighty_mike

Reputation: 1090

The error you're seeing,

SQLITE_RANGE: column index out of range

typically means that there's a mismatch between the number of placeholders in your SQL query and the number of parameters you're trying to bind.

You need to generate a place holder for each value. Something like this:

SELECT * FROM Student WHERE rollNo IN (?, ?)

You can try something like this:

const rollNos = [12, 15];
const placeholders = rollNos.map(() => '?').join(',');
const query = `SELECT * FROM Student WHERE rollNo IN (${placeholders})`;
console.log(query)

// Output: SELECT * FROM Student WHERE rollNo IN (?,?)

Upvotes: 5

Related Questions