CODEWITHSUNDEEP
sql-servernode.jstypescript
Neniel
Neniel

Reputation: 193

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

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.

Upvotes: 0

Views: 2824

Answers (1)

Duncan
Duncan

Reputation: 95712

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.

Upvotes: 4

Related Questions