How to make multiple connections at once to a SQLServer database by using node.js and connection pooling?

Question

I'm building a node.js application with SQLServer database using pool connection. it have async functions and each one should save one record to the database after they finish. the problem is only about 10 records from 10 async tasks are inserted and the rest give this error "Already connecting to database! Call close before connecting to different database.".

I tried to edit the configurations of the pool but in vane.

var config = {
            host: "localhost",
            user: "test",
            password: "test",
            database: "testDB",
            server: '************',
            pool: {
                max: 20,
                maxWaitingClients:1,
                fifo:true,
                min: 0,
                idleTimeoutMillis: 30000
            }
        };

const pool = new sql.ConnectionPool(config);
pool.on('error', err => {
    console.error('SQLServer Pool Error: ', err.toString());
});

async writeToDB(System, url, PortListening, RequestTest, time, TestType, Request, Response, responseTime, pool) {
        try {
            var query = "INSERT INTO `testTable` " +
                "(SystemName,URL,PortListening,RequestTest,TestDate,TestType,Request,Response,ResponseTime) " +
                "VALUES " +
                "('" + System + "','" + url + "','" + PortListening + "','" + RequestTest + "','" + time + "','" + TestType + "',N'" + Request + "',N'" + Response + "'," + responseTime + ")";

            await pool.connect();
            let result =await  pool.request().query(query);

            return {success: result};
        } catch (err) {
            console.log(System);
            console.error(err);
            return {err: err};
        } finally {
            pool.close();
         }
    }

It should insert all the rows from all the async tasks without any data loss.

what library are you using to connect to sql server ?

indolentdeveloper
– indolentdeveloper

2019-09-05 17:52:58 +00:00
Commented Sep 5, 2019 at 17:52 — indolentdeveloper
– indolentdeveloper, Commented Sep 5, 2019 at 17:52
mssql: npmjs.com/package/mssql

Eslam Ali
– Eslam Ali

2019-09-06 16:22:59 +00:00
Commented Sep 6, 2019 at 16:22 — Eslam Ali
– Eslam Ali, Commented Sep 6, 2019 at 16:22

indolentdeveloper · Accepted Answer · 2019-09-05 17:56:02Z

1

Omit maxWaitingClients from the pool setting. Let the client take care it of automatically.

answered Sep 5, 2019 at 17:56

indolentdeveloper

1,3132 gold badges11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eslam Ali · Accepted Answer · 2019-09-08 12:29:20Z

I found out the answer. I should open the pool "pool.connect()" one time when the application start then close it before the application closes. so the code should be something like:

var config = {
        host: "localhost",
        user: "test",
        password: "test",
        database: "testDB",
        server: '************',
        pool: {
            max: 20,
            maxWaitingClients:1,
            fifo:true,
            min: 0,
            idleTimeoutMillis: 30000
        }
    };

const pool = new sql.ConnectionPool(config);
pool.on('error', err => {
    console.error('SQLServer Pool Error: ', err.toString());
});

pool.connect();

async writeToDB(System, url, PortListening, RequestTest, time, TestType, Request, Response, responseTime, pool) {
        try {
            var query = "INSERT INTO `testTable` " +
                "(SystemName,URL,PortListening,RequestTest,TestDate,TestType,Request,Response,ResponseTime) " +
                "VALUES " +
                "('" + System + "','" + url + "','" + PortListening + "','" + RequestTest + "','" + time + "','" + TestType + "',N'" + Request + "',N'" + Response + "'," + responseTime + ")";



     let result =await  pool.request().query(query);
    } catch (err) {
        return {err: err};
    } 
}

Collectives™ on Stack Overflow

How to make multiple connections at once to a SQLServer database by using node.js and connection pooling?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related