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

Question

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

"connection error: connection is not opened yet"

我甚至不知道为什么和那些根本原因是。

下面是我的代码：

应用程序，数据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; 
    } 

} 

版本：节点：6.9.1 ，mssql：4.0.4

Answer 1

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

     // ... 
    } 
    } 
}