Having issues with connection when using node-mssql connected with Azure sql database

Question

I'm trying to insert the data from Bitfinex api into table rows of my database. I use node with typescript and mssql to execute the data service. Sometimes it works and I see the data is inserted, but sometimes the console log shows

"connection error: connection is not opened yet"

I don't even know why and where the root cause is.

Below is my code:

app-data-service.ts

import * as mssql from 'mssql';
import { AppConfig } from '../config';
import { LendbookService, UsersService, MockUsersService } from './data-services';
import { MOCK_USERS } from './mock-data';
import { Logger, LoggerFactory } from '../common';

export class AppDataServices {
  private static readonly LOGGER: Logger = LoggerFactory.getLogger();

  private db: any;

  public usersService: UsersService;
  public lendbookService: LendbookService;

  constructor(private appConfig: AppConfig) {
    this.initConnectionPool();
    this.usersService = new MockUsersService(MOCK_USERS);
    this.lendbookService = new LendbookService(this.db, AppDataServices.LOGGER);
  }

  private initConnectionPool() {
    this.db = new mssql.ConnectionPool({
      user: this.appConfig.mssqlUsername,
      password: this.appConfig.mssqlPassword,
      server: this.appConfig.mssqlServer,
      database: this.appConfig.mssqlDatabase,
      // If you are on Microsoft Azure, you need this:
      options: { encrypt: true }
    }, (err: any) => {
      if (err) AppDataServices.LOGGER.error('MSSQL error', err);
    });
  }

}

lendbook-service.ts

import * as mssql from 'mssql';
import { Lendbook } from '../../models';
import { Logger, LoggerFactory } from '../../../common';

export class LendbookService {
  private static readonly LOGGER: Logger = LoggerFactory.getLogger();

  constructor(private db: any, private logger: any) {}

  insert(row: object): any {
    const sql = "INSERT INTO dbo.lendbook (rate, amount, period, timestamp, type, frr) VALUES (@rate, @amount, @period, @timestamp, @type, @frr)";
    const ps = new mssql.PreparedStatement(this.db);
    ps.input('rate', mssql.Decimal);
    ps.input('amount', mssql.Decimal);
    ps.input('period', mssql.Int);
    ps.input('timestamp', mssql.DateTime);
    ps.input('type', mssql.NVarChar);
    ps.input('frr', mssql.NVarChar);

    ps.prepare(sql, (err: any) => {
      if (err) {
        LendbookService.LOGGER.error('MSSQL prepare error', err);
      }
      else {
        ps.execute(row, (err: any) => {
          if (err) {
            LendbookService.LOGGER.error('MSSQL execute error', err);
          }
          ps.unprepare((err: any) => {
            if (err) {
              LendbookService.LOGGER.error('MSSQL unprepare error', err);
            }
          });
        });
      }
    });
    return ps;
  }

}

Versions: node: 6.9.1, mssql: 4.0.4

score 0 · Answer 1 · answered Oct 04 '17 at 06:33

0

To avoid this error, you first need to call connect ([callback]) method and then move your prepared statement in its callback.

insert(row: object): any {

  this.db.connect((err: any) => {
    if (err) {

      LendbookService.LOGGER.error('MSSQL connect error', err);      
    } else {

      const sql = "INSERT INTO dbo.lendbook (rate, amount, period, timestamp, type, frr) VALUES (@rate, @amount, @period, @timestamp, @type, @frr)";
      const ps = new mssql.PreparedStatement(this.db);

      // ...
    }
  }
}

answered Oct 04 '17 at 06:33

Aaron Chen

9,835
1
16
28

I don't need to connect the database in this service route anymore, because I created a global connection pool in the global data app service. That was why I did not use the callback function, if I use it, the error would be "Connection error: Already connecting to the database.....". How about this.db.request((err: any) => {...}) ? – Hoàng Nguyễn Oct 04 '17 at 08:30
Where did you call `connect()`? – Aaron Chen Oct 04 '17 at 09:24
If you are using *Express*, check out [this solution](https://stackoverflow.com/questions/30356148/how-can-i-use-a-single-mssql-connection-pool-across-several-routes-in-an-express/30356582#30356582) by Christiaan and see if it helps. – Aaron Chen Oct 04 '17 at 09:30
take a look at app-data-service.ts, I initialized the connection in the constructor to open the pool globally, then import the lendbook-service as this.lendbookService = new LendbookService(this.db, AppDataServices.LOGGER); – Hoàng Nguyễn Oct 04 '17 at 10:08

Having issues with connection when using node-mssql connected with Azure sql database

1 Answers1