使用与Azure sql数据库连接的node-mssql时出现连接问题

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

我正在尝试将Bitfinex api中的数据插入到我的数据库的表行中。我使用带有typescript和mssql的节点来执行数据服务。有时它工作，我看到数据已插入，但有时控制台日志显示

"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

应用 - 数据 - 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

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

版本：节点：6.9.1，mssql：4.0.4

1 个解决方案

#1

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

要避免此错误，首先需要调用connect（[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);

      // ...
    }
  }
}