CODEWITHSUNDEEP
mysqlsqlnode.jsexpressnode-mysql2
Luca Brini
Luca Brini

Reputation: 31

MYSQL2 PROBLEM: TypeError: Bind parameters must not contain undefined. To pass SQL NULL specify JS null

I've just written a simple http api server using nodejs and express. It wraps the mysql database, and return its data.

However, for each every query I do, my server crashs.

I create the pool connection in a file called "db_manager":

const mysql = require("mysql2/promise");
require("dotenv").config();

try{
    var pool = mysql.createPool({
        host: process.env.DB_HOST,
        user: process.env.DB_USER,
        database: process.env.DB_NAME,
        password: process.env.DB_PASSWORD,
    });
} catch (err){
    console.log(err)
}

module.exports = { pool };

Then i just import the pool in the various file I use.

const { pool } = require("./db_manager");
//code
var query = config.onlyOpened ? "SELECT * FROM `Deadlines` WHERE `private` = FALSE AND `closed` == FALSE" : "SELECT * FROM `Deadlines` WHERE `private` = FALSE";
const [rawResults, _] = await pool.execute(query);

And it throws this error

~/Projects/MySQLExpress/node_modules/mysql2/lib/pool.js:177
        throw e;
        ^

TypeError: Bind parameters must not contain undefined. To pass SQL NULL specify JS null
    at ~/Projects/MySQLExpress/node_modules/mysql2/lib/connection.js:628:17
    at Array.forEach (<anonymous>)
    at PoolConnection.execute (~/Projects/MySQLExpress/node_modules/mysql2/lib/connection.js:620:22)
    at ~/Projects/MySQLExpress/node_modules/mysql2/lib/pool.js:172:14
    at ~/Projects/MySQLExpress/node_modules/mysql2/lib/pool.js:64:16
    at PoolConnection.<anonymous> (~/Projects/MySQLExpress/node_modules/mysql2/lib/connection.js:777:13)
    at Object.onceWrapper (events.js:422:26)
    at PoolConnection.emit (events.js:315:20)
    at ClientHandshake.<anonymous> (~/Projects/MySQLExpress/node_modules/mysql2/lib/connection.js:121:14)
    at ClientHandshake.emit (events.js:315:20)
[nodemon] app crashed - waiting for file changes before starting...

Upvotes: 3

Views: 14836

Answers (2)

Debojyoti Chatterjee
Debojyoti Chatterjee

Reputation: 388

This error is pretty much self explanatory.

Bind parameters must not contain undefined. To pass SQL NULL specify JS null

You need to check what exactly you're passing as a query in the execute helper

Hint: check if the config variable config.onlyOpened is getting resolved properly, and what value does your variable query holds??

I had the same error popping up for me, and I figured out that I name a variable wrong. I was writing a INSERT statement though:

try {
  return sqlConn.execute(
    "INSERT INTO products (id, title, image, price, description) VALUES (?, ?, ?, ?, ?)",
    [this.id, this.title, this.image, this.price, this.desc]);
} catch (error) {
  console.log(error)
}

Upvotes: 1

lpizzinidev
lpizzinidev

Reputation: 13284

Check your queries format:

  1. You are using == in the first one (AND closed == FALSE)
  2. Use 0 and 1 to filter boolean values
var query = config.onlyOpened ? 
    "SELECT * FROM `Deadlines` WHERE `private` = 0 AND `closed` = 0" : 
    "SELECT * FROM `Deadlines` WHERE `private` = 0";

Upvotes: 3

Related Questions