从JS中的Azure函数连接到Azure SQL DB [英] Connect to Azure SQL DB from a Azure Functions in JS

问题描述

我试图从用nodeJS编写的azure函数连接到我的Azure SQL数据库.我已经在应用程序设置中设置了连接字符串，但仍然无法正常工作.我使用ODBC键进行连接设置.有人尝试过吗?下面是我的函数的示例代码

  var Connection = require('乏味').Connection;var Request = require('乏味').Request;//创建与数据库的连接const config = process.env ["sqldb_connection"];var connection = new Connection(config);//如果连接成功，尝试连接并执行查询connection.on('connect'，function(err){如果(错误){console.log(err)}别的{queryDatabase()}});函数queryDatabase(){console.log('正在读取表格中的行...');//从表中读取所有行request = new Request(从组织中选择ORG_ID，ORG_NAME"，函数(err，rowCount，行){console.log(rowCount +'返回的行');process.exit();});request.on('row'，function(columns){column.forEach(function(column){console.log(％s \ t％s"，column.metadata.colName，column.value);});});connection.execSql(request);} 

我说错了

 执行功能时发生异常:Functions.HttpTriggerJS2.mscorlib:字典中不存在给定的键. 

解决方案

1. 在 Kudu远程执行控制台中的 D:\ home \ site \ wwwroot 下运行 npm install tedious 命令./p>

2. 在Azure Functions中，所有代码都应放在 module.exports 函数中.

3. 您应该使用 context.log()而不是 console.log().

4. 您实际上不必在功能的应用程序设置中设置连接设置字符串.

5. 当代码完成后，应通过调用 context.done()通知运行时.

以下代码对我有用:

  var Connection = require('乏味').Connection;var Request = require('乏味').Request;module.exports =函数(上下文，要求){context.log('JavaScript HTTP触发函数处理了一个请求.');var config = {userName:< userName>"，密码:< password>"，服务器:< AzureSQLDBName> .database.windows.net"，//如果您使用的是Windows Azure，则需要执行以下操作:选项:{数据库:您的数据库"，加密:true}};var connection = new Connection(config);connection.on('connect'，function(err){如果(错误){context.log(err);context.res = {状态:500，正文:无法建立连接."};context.done();} 别的 {executeStatement();}});函数executeStatement(){request = new Request("select 42，'hello world'"，function(err，rowCount){如果(错误){context.log(err);context.res = {状态:500，正文:无法连接到执行语句."};context.done();} 别的 {context.log(rowCount +'rows');}});request.on('row'，function(columns){column.forEach(function(column){context.log(column.value);});context.done();});connection.execSql(request);}}; 

有关更多信息，请参见 Azure函数JavaScript开发人员指南.

I am trying to connect to my Azure SQL DB from an azure function written in nodeJS. I have set the connection strings in the application settings and it still does not work. I used ODBC key for connection settings. Did anyone try this? Below is the sample code for my function

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

// Create connection to database
const config = process.env["sqldb_connection"];
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
       {
           queryDatabase()
       }
   }
 );

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

       // Read all rows from table
     request = new Request(
          "SELECT ORG_ID,ORG_NAME FROM org",
             function(err, rowCount, rows)
                {
                    console.log(rowCount + ' row(s) returned');
                    process.exit();
                }
            );

     request.on('row', function(columns) {
        columns.forEach(function(column) {
            console.log("%s\t%s", column.metadata.colName, column.value);
         });
             });
     connection.execSql(request);
   }

I am getting an error saying

Exception while executing function: Functions.HttpTriggerJS2. mscorlib: The given key was not present in the dictionary.

解决方案

1. Run the npm install tedious command in the Kudu remote execution console under D:\home\site\wwwroot.

2. In Azure Functions, all of your code should be put inside the module.exports function.

3. You should use context.log() instead of console.log().

4. You really don't have to set the connection settings string in Application settings of the function.

5. You should inform the runtime when your code has finished by calling context.done().

The following code works for me:

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

module.exports = function (context, req) {
    context.log('JavaScript HTTP trigger function processed a request.');

    var config = {
        userName: '<userName>',
        password: '<password>',
        server: '<AzureSQLDBName>.database.windows.net',

        // If you're on Windows Azure, you will need this:
    options:
        {
                database: 'your DB',
                  encrypt: true
        }
};

    var connection = new Connection(config);

    connection.on('connect', function(err) {

        if (err) {
            context.log(err);

            context.res = {
                status: 500,
                body: "Unable to establish a connection."
            };
            context.done();

        } else {
            executeStatement();
        }
    });

    function executeStatement() {

        request = new Request("select 42, 'hello world'", function(err, rowCount) {
            if (err) {
                context.log(err);

                context.res = {
                    status: 500,
                    body: "Failed to connect to execute statement."
                };
                context.done();

            } else {
                context.log(rowCount + ' rows');
            }
        });

        request.on('row', function(columns) {
            columns.forEach(function(column) {
                context.log(column.value);
            });

            context.done();
        });

        connection.execSql(request);
    }
};

For more information, see Azure Functions JavaScript developer guide.

这篇关于从JS中的Azure函数连接到Azure SQL DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！