How to insert HTTP query parameters into azure sql db using azure functions

Question

I have a azure function that has three query parameters , I want to insert those into Azure SQL db. Below is the code that I wrote, however, I get an error saying invalid column. The select query works fine.

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

module.exports = function (context, req) {
 context.log('JavaScript HTTP trigger function');
 let bId = req.query.binId
 let bIP = req.query.IPAddress
 let btype =req.query.bincat

 context.log(bId);
 context.log(bIP);
 context.log(btype);

 const config = {
    userName: 'used the necessary input',
    password: 'used the necessary input',
    server: 'used the necessary input',
    options: {
        database: 'used the necessary input',
        encrypt: true
    }
};
if (bId && btype && bIP) {
    context.res = {
        body: "Details: " + (bId) + ","+(bIP)+","+(btype) 
    };
}
else {
    context.res = {
        status: 400,
        body: "Please pass a name on the query string"
    };
}


context.log('config done');
const connection = new Connection(config);
connection.on('connect', err => {
    err ? context.log(err) : executeStatement();
});
context.log('connection done');
const query = 'insert into Bins(BinId, IPAddress, BinType) values (bId,bIP, btype)';
//const query = 'select * from Bins';
const executeStatement = () => {
    const request = new Request(query, (err, rowCount) => {
        context.log('inside exe');
        err ? context.log(err) : context.log(rowCount);
    });
    request.on('row', columns => {
        columns.forEach(column => context.log(column.value));
    context.done();
    });
connection.execSql(request);
};
 context.log('executed statement');
 //context.done();
// context.done();
};

The error is in the insert command.

ahmelsayed · Accepted Answer · 2018-07-09 19:31:27Z

1

You're missing request.addParameter(... for your parameters, also your parameters should start with @. see the documentation for more details https://tediousjs.github.io/tedious/parameters.html

After you create the request object, you need to do somehting like

var TYPES = require('tedious').TYPES

const query = 'insert into Bins(BinId, IPAddress, BinType) values (@bId,@bIP, @btype)';
const request = new Request(query, .....

request.addParameter('bId', TYPES.Int, bId);
request.addParameter('bIP', TYPES.VarChar, bIP);
request.addParameter('btype', TYPES.VarChar, btype);


...

answered Jul 9, 2018 at 19:31

ahmelsayed

7,4723 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert HTTP query parameters into azure sql db using azure functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related