Prepared statement is not getting executed with TypeScript and mssql NodeJS package

Question

I'm using TypeScript and mssql (v4) NodeJS package to connect my application to a SQL Server database. I'm facing that it seems that the code defined for sql.ConnectionPool is not being called and I was not able to figure out the reason. Basically I want to perform an INSERT operation using a prepared statement. Here's my code of CRUD.ts file:

var sql = require('mssql');
var config = {
    server: 'localhost',
    database: 'MyDB',
    user: 'MyUser',
    password: '*******',
    port: '1433'
};

export async function newItem(someValue1: string, someValue2: string) {
    try {
        const db = new sql.ConnectionPool(config, err => {
            console.info("Checking for errors...");
            if (err) {
                console.error("Connection failed: " + err);
            } else {
                console.info("Connected")
                const insertStatement = 'INSERT INTO [dbo].[MyData]([SomeValue1],[SomeValue2])VALUES(@SomeValue1, @SomeValue2)';
                const ps = new sql.PreparedStatement(db);
                ps.input('SomeValue1', sql.String)
                ps.input('SomeValue2', sql.String)
                ps.prepare(insertStatement, err => {
                    if (err) {
                        console.error(err);
                    } else {
                        ps.execute({
                            SomeValue1: someValue1,
                            SomeValue2: someValue2
                        }, (err, result) => {
                            if (err) {
                                console.error(err);
                            } else {
                                ps.unprepare(err => {
                                    if (err) {
                                        console.error(err);
                                    }
                                });
                            }
                        });
                    }
                });
            }
        });     
    }catch (e) {
        console.error(e);
    }

}

I tried following several examples on mssql documentation with no success.

Any kind of help would be really appreciated.

Thanks in advance.

You are using an async function so you really should rewrite your code to use await instead of callbacks. e.g. result = await ps.prepare(insertStatement) and then you can avoid many levels of nested callbacks. — Duncan
– Duncan, Commented Jan 4, 2018 at 14:55

Duncan · Accepted Answer · 2018-01-04 15:22:42Z

4

I've never used this particular library, but I think rewriting your code to use async/await will help a lot, especially as you have already marked the function itself as async. If you do that then your call to await newItem(...) will wait for all of the processing to complete instead of as now returning immediately before any of the callbacks have triggered.

Here's what I think your code could look like (but untested so may have some errors):

var sql = require('mssql');
var config = {
    server: 'localhost',
    database: 'MyDB',
    user: 'MyUser',
    password: '*******',
    port: '1433'
};

export async function newItem(someValue1: string, someValue2: string) {
    let db;
    try {
      db = await new sql.ConnectionPool(config).connect();
    }
    catch(err) {
      console.error("Connection failed: " + err);
      throw(err);
    }
    try {     
      console.info("Connected")
      const insertStatement = 'INSERT INTO [dbo].[MyData]([SomeValue1],[SomeValue2])VALUES(@SomeValue1, @SomeValue2)';
      const ps = new sql.PreparedStatement(db);
      ps.input('SomeValue1', sql.String)
      ps.input('SomeValue2', sql.String)
      await ps.prepare(insertStatement);
      await ps.execute({
            SomeValue1: someValue1,
            SomeValue2: someValue2
        });
      await ps.unprepare();
    } catch (e) {
        console.error(e);
        throw(e);
    }
}

Note that I added a call to .connect() on the pool as the documentation seems to imply that is required. When you use await any errors should come back as Promise rejections which the await turns into exceptions. That means instead of checking each call for an error code you just need a single try...catch (actually two of them here to keep the different message you use for a connection error).

I also re-threw the errors to indicate to the caller that the code failed. If you just want to errors to bubble up to the caller then you could also remove the error handling entirely: as errors turn into Promise rejections or exceptions they will propagate upwards.

answered Jan 4, 2018 at 15:22

Duncan

96.4k15 gold badges129 silver badges160 bronze badges

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

3 Comments

Neniel Over a year ago

Thanks for your answer, Duncan! I implemented it as you suggested and then I was facing another issue. But I was able to solve it. I was having another async function that was calling the already mentioned one: return await new Promise(function (resolve, reject) { DBOperations.newItem(someValue1, someValue2); }) I just added the async and await keyword, as shown below: return await new Promise(async function (resolve, reject) { await DBOperations.newItem(someValue1, someValue2); })

Duncan Over a year ago

@danielcode7 an async function already returns a Promise so remove the new Promise and just return the call directly: return DBOperations.newItem(someValue1, someValue2); you don't even have to await the result as you're just passing the Promise back.

Neniel Over a year ago

Thanks for highlighting that point, but I'm using new Promise because the calling function (another async function) will return another different Promise with the resolve() method.

Collectives™ on Stack Overflow

Prepared statement is not getting executed with TypeScript and mssql NodeJS package

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related