Calling Azure SQL from Azure Functions node.js

Question

I'm trying to use tedious library to inject simple insert from the incoming body. No error is thrown, but context.logs placed inside the functions are not displaying in logs. As a result in DB I have row with NULL values instead of what is passed. Any idea what am I doing wrong? Is there any other library/method of accessing the Azure DB from Azure Functions or I am stuck with Tedious? Of course I could probably use Azure Logic App but its more expensive to run than 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();
};

Your references to "globalheaders" is case sensitive. Where you are adding your parameters change "globalheaders.id" to "globalheaders.Id" (capital I in Id), "globalheaders.payment" to "globalheaders.Payment" (capital P in Payment) and all the remaining. give that a try. — Tim Mylott
– Tim Mylott, Commented Jan 24, 2020 at 20:43
I followed your suggestion but nothing changes. Still nulls in the database and lack of ability to debug in portal due to context.logs not working e.g. the one before request.addparameter. — MatnikR
– MatnikR, Commented Jan 26, 2020 at 19:59
I am getting close. I suspect the due to lack of support for async/await from tedious there is an issue that the whole function ends before executeStatement is invoked. I suspect req.body passed to function is undefined in that moment. When I put the following: executeStatement({ Id: '1233', Name: 'Ant', Payment: "2019-10-09", Type: 'Fixed cost', Value: 156, Cycle: '1', Frequency: 'month' }) — MatnikR
– MatnikR, Commented Jan 28, 2020 at 8:19
It works then - with (req.body) or (globalheaders) it does not work, hence I suspect that those are undefined in that moment — MatnikR
– MatnikR, Commented Jan 28, 2020 at 8:20

Stanley Gong · Accepted Answer · 2020-01-27 07:16:39Z

Try this :

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();
};

Test result on local :

Data has been insetted into Azure SQL DB successfully:

PramodValavala · Accepted Answer · 2020-01-27 06:41:32Z

0

I believe the problem here is that you are mixing async/await and context.done(). You should just use 1 of the 2 as required.

Also, I believe tedious doesn't support async/await in the first place. So, removing the that and just having context.done() should do.

answered Jan 27, 2020 at 6:41

PramodValavala

6,6701 gold badge16 silver badges31 bronze badges

Comments

MatnikR · Accepted Answer · 2020-01-28 12:33:27Z

0

After long battle it appears that for some reason Azure Functions prefers adressing the property by value string:

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']);

After changing to this everything started to work

answered Jan 28, 2020 at 12:33

MatnikR

1913 silver badges21 bronze badges

Comments

Himanshu Anand · Accepted Answer · 2021-09-26 04:24:50Z

The correct way to achieve it is by using sync code. Tedious uses event based programming paradigm and do not support async programming paradigms. So to explicitly mark the completion of azure function we use context.done() method. So the right code would be as follows:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
module.exports = 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);
connection.connect();
connection.on('connect', function(err) {
    if (err) {
        context.res = {
            status: 500,
            body: "Unable to establish a connection."
        };
        context.done();
    } else {
        executeStatement(req.body);
    }
});

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);  
          }  
        });  
    });       
  request.on("requestCompleted", function (rowCount, more) {
      connection.close();
      context.res = {
        status: 200, /* Defaults to 200 */
        body: JSON.stringify(result),
        headers: {
      '     Content-Type': 'application/json'
        }
     };
  context.done();
});
    connection.execSql(request);
}

};

Collectives™ on Stack Overflow

Calling Azure SQL from Azure Functions node.js

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related