How to query azure SQL from node.js server

Question

I'm having some trouble with querying my DB on azure sql (I am very new to sql). I'm following the steps on https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-nodejs, but it only includes tutorial steps on how to read tables, not manipulate them. I am trying to do INSERT and DELETE requests on my Node.js server, but I am getting a request error in one of the node modules, which makes me think that I'm going about requesting the operations wrong.

Here's some code:

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

// Create connection to database
var config =
{
    userName: 'user_name',
    password: 'password',
    server: 'server_name',
    options:
    {
        database: '_dbname'
        , 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 {
    queryDatabase();
}
}

//this works fine, it's for initially loading the data from the database
function queryDatabase() {
console.log('\nReading rows from the Table...');
let obj = {};
let objs = [];
let request;
// Read all rows from table
request = new Request(
    "SELECT * FROM [dbo].[TABLE_NAME]",
    function (err, rowCount, rows) {
        console.log('-- Done');
    }
);
//this is for when an admin adds content to the app, SQL table not changing, 
//node.js throws error
socket.on('add item', item => {
    let index = getCollectionIndexById(item.id);
    collections[index].items.push(item.item);
    io.sockets.emit('add item', item);

    request = new Request(`INSERT INTO [dbo].[TABLE_NAME](Id, attr1, attr2, attr3, attr4)
     VALUES (`
        + item.id + ','
        + item.item.attr2 + ','
        + item.item.attr3 + ','
        + item.item.attr4 + ','
        + null, function (err, rowCount, rows) {
            if (err) throw err;
            console.log('> requested db to insert item');
        });
    connection.execSql(request);
    console.log('> item sent to app');
});
//for when the admin removes content from the app, same error 
socket.on('rm item', item => {
    collections[getCollectionIndexById(item.id)].items.splice(item.index, 1);
    io.sockets.emit('rm item', { "id": item.id, index: item.index });

    request = new Request(`DELETE FROM [dbo].[TABLE_NAME] WHERE Id= `
        + item.id + ` AND attr1= ` + item.item.attr1, function (err, rowCount, rows) {
            if (err) throw err;
            console.log('> requested db to remove item')
        });
    connection.execSql(request);

    console.log('> sent request to remove item');

});

The exact error msg is RequestError: Incorrect syntax near 'esse'., and its in one of the node modules called tedious in request.js.

So in summary, if anyone knows of a way to query Azure SQL Db's to make inserts and deletes, any help is appreciated!

Aaron Chen · Accepted Answer · 2017-08-15 07:32:08Z

1

You missed ) in the 'insert' SQL. Also, you'd need to set string value with single quotes of course.

Change the following lines of code

request = new Request(`INSERT INTO [dbo].[TABLE_NAME](Id, attr1, attr2, attr3, attr4)
     VALUES (`
        + item.id + ','
        + item.item.attr2 + ','
        + item.item.attr3 + ','
        + item.item.attr4 + ','
        + null, function (err, rowCount, rows) {
            if (err) throw err;
            console.log('> requested db to insert item');
        });
    connection.execSql(request);

to

request = new Request(`INSERT INTO [dbo].[TABLE_NAME](Id, attr1, attr2, attr3, attr4) VALUES ('${item.id}', '${item.item.attr2}', '${item.item.attr3}', '${item.item.attr4}' null)`, function (err, rowCount, rows) {
    if (err) throw err;
    console.log('> requested db to insert item');
});
connection.execSql(request);

edited Aug 15, 2017 at 7:32

answered Aug 15, 2017 at 7:04

Aaron Chen

9,9701 gold badge20 silver badges30 bronze badges

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

Comments

Alberto Morillo · Accepted Answer · 2017-08-14 19:45:14Z

0

You will find an example here of how to INSERT records on a table using NodeJs and queryRaw.

conn.queryRaw("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express 102', 'SQLEXPRESS 102', 0, 0, CURRENT_TIMESTAMP)", function (err, results)

You can use queryRaw also to DELETE records on a table.

Hope this helps.

answered Aug 14, 2017 at 19:45

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to query azure SQL from node.js server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related