Connection to SQL Server with Node.js doesn't work

Question

I have a big problem: I want to to connect to a MS SQL Server but whatever I am doing it doesn't work... I don't know where my mistake lies..

const parse = require("csv-parse/lib/sync");
const fs = require("fs");
const path = require("path");
var mssql = require('mssql/msnodesqlv8');
var iconv = require('iconv-lite');
var http = require('http');

// Connection to SSMS Database
var dbConfig = {
    host: 'localhost',
    user: 'user',
    server: 'server',
    database: 'database',
    options: {
        trustedConnection: true,
        useUTC: true
      }
};

var connection = new mssql.Connection(dbConfig, function(err) {
var request = new mssql.Request(connection);

...

 request.query(`INSERT INTO ${richtigername} (${namen}) VALUES (${values})`, function (err, recordset) {

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

                                res.send(recordset)
                            } 

                            mssql.close();


                            });

Now there is the error message:

TypeError: request is not a function

It is so difficult to get data into a SQL Server database.. the tool works fine with MySQL but SQL Server is horrible..

I have two more queries before this with also: request.query(...) and if (err) { console.log(err); res.send(recordset) ... MySQL is on the other hand so easy... only

connection.query(...) and before this

// Verbindung zur MySQL Datenbank
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'database'
});

and

 // Verbindung zur Datenbank starten
    connection.connect((error) => {
        if (error) {
            console.error(error);
        } else {

        }

    }) 
}

Firstly, SSMS isn't a database, it's a client tool. You're connecting to SQL Server. Secondly, please edit your question and add the info below. Do you actually have a SQL Server installed? Can you connect to it with a client tool (SSMS) — Nick.Mc
– Nick.Mc, Commented Nov 22, 2019 at 11:43
oh sry I know ;) yes I have sql server express and SSMS and also the database in SSMS ;) — Frederic
– Frederic, Commented Nov 22, 2019 at 12:09
Assuming you want to connect to SQL Server from Node.js, check the docs. This doc section shows how to install the correct driver (tedious), and how to connect and execute queries — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 22, 2019 at 12:17
@Frederic what you wrote isn't a MySQL query either. You created a SQL string through string interpolation, exposing you to SQL injection attacks. You can use the same string in SQL Server but don't do that. The doc example for SQL Server shows how to use parameters. MySQL also supports parameters. Instead of passing them by name though, you have to pass them by position — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 22, 2019 at 12:20
Also we haven’t established whether you’ve installed SQL Server yet — Nick.Mc
– Nick.Mc, Commented Nov 22, 2019 at 13:15

smoore4 · Accepted Answer · 2019-11-22 15:42:24Z

First, assign a connection pool then use that to run your SQL. This example uses a stored procedure, but it is easily modified to run a query.

var express = require('express');
var app = express();

app.get('/', function (req, res) {

    var sql = require("mssql");

    // config for your database
    var config = {
        user: 'user',
        password: 'yourpassword',
        server: 'yourserver', 
        database: 'yourdb' 
    };

  new sql.ConnectionPool(config).connect().then(pool => {
    return pool.request()
      // for select query, uncomment .query and comment .input and .execute
      //.query("select * from table1 where id = 2")
      // for stored proc, comment .query and uncomment .input and .execute
      // .input is only needed if proc has params
      .input('p_id', sql.Int, 5)
      .execute('getData')
    }).then(result => {
      let rows = result.recordset
      res.setHeader('Access-Control-Allow-Origin', '*')
      res.status(200).json(rows);
      sql.close();
    }).catch(err => {
      console.log(err);
      res.status(500).send({ message: "${err}"})
      sql.close();
    });

  });

var server = app.listen(5000, function () {
    console.log('Server is running..');
});

Collectives™ on Stack Overflow

Connection to SQL Server with Node.js doesn't work

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related