Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • Jan 31 2019 14:34
    willmorgan commented on 889cbf1
  • Jan 31 2019 13:15
    gsamal commented on 889cbf1
  • Jan 31 2019 13:10
    dhensby closed #797
  • Jan 31 2019 13:10
    dhensby commented #797
  • Jan 31 2019 13:07
    dhensby closed #795
  • Jan 31 2019 13:07
    dhensby commented #795
  • Jan 31 2019 12:58
    gsamal commented #789
  • Jan 31 2019 11:50
    cesco69 opened #797
  • Jan 31 2019 10:22

    dhensby on master

    Update changelog for v5 Merge pull request #796 from dh… (compare)

  • Jan 31 2019 10:22
    dhensby closed #796
  • Jan 31 2019 09:53
    dhensby commented #789
  • Jan 31 2019 09:52
    dhensby commented #778
  • Jan 31 2019 09:51
    dhensby review_requested #796
  • Jan 31 2019 09:51
    dhensby opened #796
  • Jan 31 2019 06:45
    suckerp commented #795
  • Jan 31 2019 02:58
    liumorgan starred tediousjs/node-mssql
  • Jan 31 2019 02:15
    zhoukk starred tediousjs/node-mssql
  • Jan 30 2019 22:22
    vjpr edited #795
  • Jan 30 2019 22:22
    vjpr edited #795
  • Jan 30 2019 22:22
    vjpr opened #795
Jason Luboff
@JLuboff
As @dhensby is one of the main maintainers, he may have a better idea
Daniel Hensby
@dhensby
I believe this is a feature in the dotnet library, but it's not possible in node. It's down to the drivers and their integration, none of them support this feature at the moment
kemmottar
@kemmottar
@dhensby
Thank u
Luke
@luker2
function generateUpsertQueryString(myListOfObjects: MyDbTableRow[]) {
    return myListOfObjects.map(o => `

-- update existing row if possible
UPDATE dbo.MyDbTable
SET SomeCol1 = '${o.someCol1}',
    SomeCol2 = '${o.someCol1}'
WHERE SomeCol3 = '${o.someCol1}';

-- otherwise insert new row
IF @@ROWCOUNT = 0
    INSERT INTO dbo.MyDbTable
    (
        SomeCol1,
        SomeCol2,
        SomeCol3
    )
    VALUES
    (   
        '${o.someCol1}',
        '${o.someCol2}',
        '${o.someCol3}'
    );
`).join('\n')
}
hey @dhensby / anybody, I typically use mssql's "params" feature to escape strings. In the case above, the function is basically generating a giant query string. I pass the function a list of objects which should be used to update/insert rows into a table. So the function loops thru and generates an "upsert" statement for each item in the list. I foresee sql injection with this approach of using javascript template strings. Otherwise it seems I'd have to come up with a clever way to format all this data into the "params" object. Any ideas/recommendations?
Jason Luboff
@JLuboff
@luker2 According to the docs, template literals should be sanitized
ES6 Tagged template literals
const sql = require('mssql')

sql.connect(config).then(() => {
    return sql.query`select * from mytable where id = ${value}`
}).then(result => {
    console.dir(result)
}).catch(err => {
    // ... error checks
})

sql.on('error', err => {
    // ... error handler
})
All values are automatically sanitized against sql injection. This is because it is rendered as prepared statement, and thus all limitations imposed in MS SQL on parameters apply. e.g. Column names cannot be passed/set in statements using variables.
I have not personnelly tested that though
Luke
@luker2

@JLuboff unrelated, but this seems like some weird syntax:

return sql.query`select * from mytable where id = ${value}`

But thanks, if I understand correctly, I don't need to make any changes to my approach above. I'll run some tests to make sure that's happening.

Jason Luboff
@JLuboff
Ya, thats tagged template literals (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals around the middle of the page it talks about it)... I agree that its an odd looking syntax too hah
Daniel Hensby
@dhensby

@luker2 - the way you're doing it in your original call won't provide safety because it won't cause the automatic parameterisation of the query.

if you mapped it directly into a query call (as suggested by @JLuboff) then it should be safe as it will generate a parameterised query for you.

function generateUpsertQueryString(myListOfObjects: MyDbTableRow[]) {
    return myListOfObjects.map(o => mssql.query`

-- update existing row if possible
UPDATE dbo.MyDbTable
SET SomeCol1 = '${o.someCol1}',
    SomeCol2 = '${o.someCol1}'
WHERE SomeCol3 = '${o.someCol1}';

-- otherwise insert new row
IF @@ROWCOUNT = 0
    INSERT INTO dbo.MyDbTable
    (
        SomeCol1,
        SomeCol2,
        SomeCol3
    )
    VALUES
    (   
        '${o.someCol1}',
        '${o.someCol2}',
        '${o.someCol3}'
    );
`)
}
Luke
@luker2

@dhensby gotcha. Would it ever be easy to expose a standalone function like mysql.escape() ?

https://github.com/mysqljs/mysql#escaping-query-values

Daniel Hensby
@dhensby
no, because the escaping requires connection context and there isn't as simple a way to escape strings because the connection doesn't expose a way to do this nicely like mysql does. the answer is to use parameterised queries
Jonas Amundsen
@badeball
Is it possible to not use a connection pool, but a single connection that I manage myself?
Daniel Hensby
@dhensby
not really, the use of the pool is pretty tightly integrated into the library - what is it you're looking to achieve?
Jonas Amundsen
@badeball
I'm just doing some performance testing and would like to control all the variables
If I create a pool with min: 10, max: 10 and invoke connect(), will I have opened 10 connections at that point?
Daniel Hensby
@dhensby
no - the pool is backed by tarnjs and tarn doesn't pre-emptively create the resources, it just won't discard them
I'd recommend you "prime" the pool before doing any performance testing so that the initial connections don't muddy your results.
Daniel Hensby
@dhensby
you should be able to force 10 connections in the pool by performing 10 concurrent requests
Jonas Amundsen
@badeball
Would I be able to do that by eg. synchronously invoking query() 10 times with SELECT 1;?
Daniel Hensby
@dhensby
exactly something like await Promise.all(Array.from(new Array(10)).map(() => pool.request().query('SELECT 1')));
Jonas Amundsen
@badeball
Yup, alrights. Thanks so much for your help!
Daniel Hensby
@dhensby
no worries. You should also be able to verify the number of connections in the pool using pool.size or even pool.available
Oleksandr Tsymbaliuk
@TAVkiev
Hay all ! Looking for small clarification about "single connection", thanks in advance )
I need to cover few things by tests without any mocking.
Can I perform this flow:
According to it https://www.npmjs.com/package/mssql#connection-pools
For example, if I will add this stuff to separate file, "db.js", and in test files I will address to function messageHandler, it will still one connection, despite this function is calling by different files ? Or it is better to create one pool connection somewhere in global- before hook and then address to this connection from other test files (something like in example, https://www.npmjs.com/package/mssql#managing-connection-pools) ?
const sql = require('mssql')

const pool1 = new sql.ConnectionPool(config);
const pool1Connect = pool1.connect();

pool1.on('error', err => {
    // ... error handler
})

async function messageHandler() {
    await pool1Connect; // ensures that the pool has been created
    try {
        const request = pool1.request(); // or: new sql.Request(pool1)
        const result = await request.query('select 1 as number')
        console.dir(result)
        return result;
    } catch (err) {
        console.error('SQL error', err);
    }
}
Daniel Hensby
@dhensby

@TAVkiev That should work, yes. But note this is one pool not one connection. The pool will have multiple connections (depending on the pool config, of course).

The nature of node's file loading means that the call to require(...) is cached so each time your test pulls in this function you will only have one pool.

Remember at the end of your tests to close the pool or you'll find your test suite hangs.

Oleksandr Tsymbaliuk
@TAVkiev
@dhensby Many thanks !
Paul O'Brien
@piobrien

Having issues with a simple query that's failing with a RequestError: Invalid object name 'dbo.SomeTable'

Any idea why this would not find any tables?
SQL Management Studio testing shows the user credentials are correct and the account has access to the tables etc.

let sqlConfig = {
            user: 'username',
            password: 'password',
            server: 'servers name or IP',
            database: 'TestDB',
            port: 1433,
            options: {
                database: 'TestDB',
                "encrypt": true,
                "enableArithAbort": true
            }
        };

        let pool = await sql.connect(sqlConfig);
        let result = await pool.request().query('SELECT * FROM [dbo].[SomeTable]');
Daniel Hensby
@dhensby
It can only be that the table is not in that DB...
Paul O'Brien
@piobrien
In case it helps someone else in the future. I have two database connections in use and forgot about the global connection pool. https://www.npmjs.com/package/mssql#pool-management
NivethikaM
@NivethikaM
Line number of code is not shown when an error message is thrown.
Example: Incorrect syntax of query is passed. The error mesage is getting displayed but it is not showing the line of code. If we get the line of code it will be easy for us to debug it.
David Gasperoni
@mcdado
I think it’s not possible because the error is thrown from the event loop chain. You should catch any time you execute a request.
NivethikaM
@NivethikaM
I am running queries in try catch block only. I am catching the request. It is showing the error message with all the details except the line of code.
All the npm modules including Sequelize is showing the line of code in the error message. When we know the line of code, it is easy for the developers to debug it.
Daniel Hensby
@dhensby
@NivethikaM if you are aware of a way to get the line number showing, a pull request would be appreciated. Thanks
Gabrielle Lima
@gabizinha12
Hello
tipyc
@tipyc
Hi friends, does anyone know how to send a JSON object to sql server using mssql?
In sql I ask ISJON () this returns false I receive [object, object]
Daniel Hensby
@dhensby
I'm not certain, but I think you'd probably need to just retrieve it as a string and decode it at the application level?
tipyc
@tipyc
I am sending the object as a parameter of type NVARCHAR
image.png
but in sql I only get [object, object]
How could I send a valid json?
ArtemRudenko
@artemrudenko
Hi All,
When I am executing query in SQL Management Studio I see 2020-03-31 04:35:43 value for let's say timestamp column
However when I am executing the very same query from node-mssql with default driver I am receiving a Date with 2020-03-30 21:35:43 GMT-0700. Is it somehow possible to read Date value from DB without converting time to local(e.g. as is)?
Daniel Hensby
@dhensby
have you tried the useutc option?
ArtemRudenko
@artemrudenko
@dhensby Hi, I have tried both and got the following:
Origin(DB value): 2020-03-31 04:35:43.410

useUTC: true(default) =>  Date: Mon Mar 30 2020 21:35:43 GMT-0700 (Mountain Standard Time)
if I call toDBString on the resulting value => '2020-03-31 04:35:43'

useUTC: false => Date: Tue Mar 31 2020 04:35:43 GMT-0700 (Mountain Standard Time)
if I call toDBString on the resulting value => '2020-03-31 11:35:43'
David Gasperoni
@mcdado
What is the DB local time, MST? The datetimes are in the local timezone and not in UTC right?
Keep in mind that JS dates have the timezone embedded in them, just outputting them might be misleading
ArtemRudenko
@artemrudenko
EST
Sorry but DB local time - what do you mean?