azure - 从 Azure Functions node.js 调用 Azure SQL

Question

我正在尝试使用繁琐的库从传入的正文中注入简单的插入。不会引发错误，但放置在函数内的 context.logs 不会显示在日志中。结果，在 DB 中，我有 NULL 值而不是传递的行。知道我在做什么错吗？是否有任何其他库/方法可以从 Azure Functions 访问 Azure DB，或者我陷入了乏味？当然，我可能会使用 Azure Logic App，但它的运行成本比 Azure Functions 高。

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var globalheaders = {
    Id: '1233',
    Name: 'Ant',
    Payment: "2019-10-09",
    Type: 'Fixed cost',
    Value: 156,
    Cycle: '1',
    Frequency: 'month'
}

module.exports = async function (context, req) {
    context.log('JavaScript HTTP trigger function processed a request.');
    globalheaders = req.body;
    context.log(globalheaders);
var config = {  
        server: '********.database.windows.net',  //update me
        authentication: {
            type: 'default',
            options: {
                userName: '*******', //update me
                password: '*******'  //update me
            }
        },
        options: {
            // If you are on Microsoft Azure, you need encryption:
            encrypt: true,
            database: 'cashmandb'  //update me
        }
    }; 
    var connection = new Connection(config);
    await connection.on('connect', function(err) {

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

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


        } else {
            context.log('before execution');
            executeStatement();
        }
    });
context.log('connection executed')
    async function executeStatement() {  
        request = new Request("INSERT dbo.cost (Id, Name, Payment, Type, Value, Cycle, Frequency) OUTPUT INSERTED.Id VALUES (@Id, @Name, @Payment, @Type, @Value, @Cycle, @Frequency);", function(err) {  
         if (err) {  
            context.log(err);}  
        });  
        context.log('executestatement')
        request.addParameter('Id', TYPES.NChar,globalheaders.id);  
        request.addParameter('Name', TYPES.NVarChar , globalheaders.name);  
        request.addParameter('Payment', TYPES.Date, globalheaders.payment);  
        request.addParameter('Type', TYPES.NVarChar,globalheaders.type); 
        request.addParameter('Value', TYPES.Int,globalheaders.value);  
        request.addParameter('Cycle', TYPES.NChar,globalheaders.cycle); 
        request.addParameter('Frequency', TYPES.NChar,globalheaders.frequency); 
        request.on('row', function(columns) {  
            columns.forEach(function(column) {  
              if (column.value === null) {  
                context.log('NULL');  
              } else {  
                context.log("Product id of inserted item is " + column.value);  
              }  
            });  
        });       

        await connection.execSql(request);
    }

    context.done();
};

标签： azureazure-sql-databaseazure-functionstedious

Answer 1

尝试这个 ：

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


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

var config = {  
        server: 'xxxx.database.windows.net',  //update me
        authentication: {
            type: 'default',
            options: {
                userName: 'xxx', //update me
                password: 'xxx'  //update me
            }
        },
        options: {
            // If you are on Microsoft Azure, you need encryption:
            encrypt: true,
            database: 'xxx'  //update me
        }
    }; 
    var connection = new Connection(config);
    await connection.on('connect', function(err) {

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

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


        } else {

            executeStatement(req.body);
        }
    });

    async function executeStatement(globalheaders) {  

        request = new Request("INSERT dbo.cost (Id, Name, Payment, Type, Value, Cycle, Frequency) OUTPUT INSERTED.Id VALUES (@Id, @Name, @Payment, @Type, @Value, @Cycle, @Frequency);", function(err) {  
         if (err) {  
            context.log(err);}  
        });  
        request.addParameter('Id', TYPES.NChar,globalheaders.Id);  
        request.addParameter('Name', TYPES.NVarChar , globalheaders.Name);   
        request.addParameter('Payment', TYPES.Date,globalheaders.Payment);  
        request.addParameter('Type', TYPES.NVarChar,globalheaders.Type); 
         request.addParameter('Value', TYPES.Int,globalheaders.Value);  
         request.addParameter('Cycle', TYPES.NChar,globalheaders.Cycle); 
         request.addParameter('Frequency', TYPES.NChar,globalheaders.Frequency); 
        request.on('row', function(columns) {  
            columns.forEach(function(column) {  
              if (column.value === null) {  
                context.log('NULL');  
              } else {  
                context.log("Product id of inserted item is " + column.value);  
              }  
            });  
        });       

        await connection.execSql(request);
    }

    context.done();
};

本地测试结果：

数据已成功插入 Azure SQL DB：