在Node中，如何从全局数据库连接执行sql [英] In Node, how to execute sql from global database connection

问题描述

在node.js中使用全局数据库连接时，我无法执行sql.

I am unable to execute the sql, when using the global database connection in node.js.

我已按照Azure文档中的步骤进行操作: https://docs.microsoft.com/en-us/azure/mysql/connect-nodejs 并能够在控制台上显示输出.但是，我想将所有Azure SQL数据库连接放在一个单独的文件中，但是select查询不会在控制台上打印输出.

I have followed the steps as in Azure documentation: https://docs.microsoft.com/en-us/azure/mysql/connect-nodejs and able to display the output on the console. But, I want to put all my Azure SQL database connection in a separate file, but the select query is not printing the output on the console.

DatabaseManager.js

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;


var sqlConnection = function sqlConnection() {
// Create connection to database
var config =
  {
    userName: 'uname',
    password: 'password',
    server: 'dbserver.database.windows.net',
    options:
        {
            database: 'mydatabase',
            encrypt: true
        }
  }

var connection = new Connection(config);
// Attempt to connect and execute queries if connection goes through

connection.on('connect', function(err) {
  if (err) 
    {
        console.log(err)
   }

  else 
    {
        console.log('CONNECTED TO DATABASE');
   }

  }
 );
}
module.exports = sqlConnection;

app.js

var restify = require('restify');
var builder = require('botbuilder');
var botbuilder_azure = require("botbuilder-azure");
var azure = require('azure-storage');
var dbconnection = require('./DatabaseManager');

bot.dialog('profileDialog',
    (session) => {
      session.send('You reached the profile intent. You said \'%s\'.', session.message.text);

      console.log('Reading rows from the Table...');
        dbconnection("select FNAME from StudentProfile where ID=1"),
        function (err, result, fields) {
            if (err) throw err;
            console.log(result);
        }
          session.endDialog();   
    }

控制台输出:

从表中读取行...
连接到数据库

Reading rows from the Table...
CONNECTED TO DATABASE

我期待FNAME的输出，但是在控制台上没有任何输出.有什么我想念的吗?

I was expecting the output of FNAME, but nothing is printing on the console. Is there anything, I am missing?

谢谢.

推荐答案

这里有两个问题.首先，每个文件只能导入一次模块.这只是出于性能考虑，实际上不会破坏您的代码.

There's a couple of problems here. First off, you should only ever import a module once per file. This is just a performance consideration and won't actually break your code.

接下来，请注意从DatabaseManager模块导出的内容.现在，您正在导出创建连接的函数，然后对该连接不执行任何操作.我们可以通过使用一种称为回调"的模式来解决此问题，该模式可以让我们提供一个函数，然后将连接作为参数进行调用.

Next, pay attention to what you're exporting from your DatabaseManager module. Right now, you're exporting a function that creates the connection and then doesn't do anything with it. We can fix this by using a pattern called a "callback" which lets us provide a function that will then be called with the connection as an argument.

我在解释事情的代码中添加了大量注释.这段代码无法按原样运行-在几个地方，我可以执行此操作或执行此操作".您必须选择一个.

I added a ton of comments to the code explaining things. This code won't run as-is - there's a couple places where I have "do this or this". You'll have to choose one.

var Tedious = require('tedious'); // Only require a library once per file
var Connection = Tedious.Connection;
var Request = Tedious.Request;

// Or using the object spread operator
var { Connection, Request } = require('tedious');

// You called this `sqlConnection`. I'm going to use a verb since it's a
// function and not a variable containing the connection. I'm also going
// to change the declaration syntax to be clearer.

function connect(cb) { // cb is short for callback. It should be a function.
  var config = {
    userName: 'uname',
    password: 'password',
    server: 'dbserver.database.windows.net',
    options: {
      database: 'mydatabase',
      encrypt: true
    }
  }; // Put a semi-colon on your variable assignments

  var connection = new Connection(config);

  // Attempt to connect and execute queries if connection goes through
  connection.on('connect', function(err) {
    if (err) {
      console.log(err);
      return; // Stop executing the function if it failed
    }

    // We don't need an "else" because of the return statement above
    console.log('CONNECTED TO DATABASE');

    // We have a connection, now let's do something with it. Call the
    // callback and pass it the connection.
    cb(connection);
  });
}

module.exports = connect; // This exports a function that creates the connection

然后回到您的主文件中，您可以像这样使用它.

Then back in your main file, you can use it like so.

var restify = require('restify');
var builder = require('botbuilder');
var botbuilder_azure = require('botbuilder-azure');
var azure = require('azure-storage');
var connect = require('./DatabaseManager'); // renamed to be a verb since it's a function.

bot.dialog('profileDialog', (session) => { // Hey, this is a callback too!
  session.send('You reached the profile intent. You said \'%s\'.', session.message.text);

  console.log('Creating a connection');

  connect((connection) => {
  // or with the traditional function notation
  connect(function(connection) {

    console.log('Reading rows from the Table...');

    // Execute your queries here using your connection. This code is
    // taken from 
    // https://github.com/tediousjs/tedious/blob/master/examples/minimal.js
    request = new Request("select FNAME from StudentProfile where ID=1", function(err, rowCount) { // Look another callback!
    if (err) {
      console.log(err);
    } else {
      console.log(rowCount + ' rows');
    }
    connection.close();
  });

  request.on('row', function(columns) {  // Iterate through the rows using a callback
    columns.forEach(function(column) {
      if (column.value === null) {
        console.log('NULL');
      } else {
        console.log(column.value);
      }
    });
  });

  connection.execSql(request);
});

这篇关于在Node中，如何从全局数据库连接执行sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！