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
ArtemRudenko
@artemrudenko
EST
Sorry but DB local time - what do you mean?
ArtemRudenko
@artemrudenko
@mcdado My case is that i have est date saved in DB. I need to read it, convert to utc and than convert to local. So to do this now i have to read it, convert to DB string as date-utils does and after that convert to utc and local
David Gasperoni
@mcdado
What I mean is, afaik the timezone is not included in DATETIME, you would need another column with DATETIMEOFFSET if you needed it, otherwise always write the date time in UTC and convert once read from the db.
So it would be an application concern to know what timezone the server is in, and correct accordingly.
    const currentTimestamp = new Date(Date.UTC(
      now.getFullYear(),
      now.getMonth(),
      now.getDate(),
      now.getHours(),
      now.getMinutes(),
      now.getSeconds()
    ));
I do something like this
With const now = new Date();
ArtemRudenko
@artemrudenko
@mcdado, thank you, unfortunately I am not able to change table structure and add timezone offset. However each and every date in DB is expected to be in EST, so if I understood you correctly I have to convert it myself using EST offset. For now I have implemented this using the way you mentioned.
Just to be sure am I able to read Date value from DB as string without converting to local? In such a case i would send this value to moment-timezone America/New-York and this would produce me est date as is?
David Gasperoni
@mcdado
Yeah as long as you know what zone the dB is in, you have to perform the translation. +1 for using moment
ArtemRudenko
@artemrudenko
ok, got it, thank you David
flashp0wa
@flashp0wa:matrix.org
[m]

Hey everyone! I would like to get a little help.... I am trying to wrap my head around the connection pools, but I am a bit confused...

So i Have an "SQLConnector.js" file, which looks like this

const sql = require('mssql');

const sqlConnector = () => {

    config = {
        user: "xxxxx",
        password: "xxxx",
        server: "localhost",
        database: "Test",
        options: {
            trustServerCertificate: true
}

const pool = new sql.ConnectionPool(config);
return poolConnect = pool.connect();

}

module.exports = {

sqlConnector,
sql
}

In another file "writeToDb1.js" i am calling the exported sqlConnector module like this:

const pool = await sqlConnector.sqlConnector();
await pool.request().query(query)

Everything works like a dream... but I do not get this pool management... If i create a new file 'writeToDb2.js' with the above mentioned code... will that create another pool?

As I understood from the documentation, once a pool has been created, the connect() will resolve to that pool always... so i tried creating a file 'writeToDb3.js' with the following code:

const sql = require('mssql')

// run a query against the global connection pool

function runQuery(query) {
// sql.connect() will return the existing global pool if it exists or create a new one if it doesn't
return sql.connect().then((pool) => {
return pool.query(query)
})
}

But this one throws an error Cannot read property 'port' of undefined. I believe its because i did not provide the config file. This part is not clear also...I already created the pool above with the config file.... I thought i just have to call the connect() function.

Any help would be highly appreciated!

Daniel Hensby
@dhensby

@flashp0wa:matrix.org With your current code everytime you call sqlConnector() you'll be creating a new pool. You're going to have a problem with this code because you're never closing the pools you create and will cause a connection limit error on your server like this, you'll also no doubt have created memory leaks within your application layer. If you want to have a persistent pool you would need a variable you assign the pool to and return that if it is assigned, eg:

let pool;

const getPool = () => {
  if (!pool) pool = new ConnectionPool(config).connect();  
  return pool;

}

the reason that sql.connect() is not working is because you have created a pool yourself , then you've attempted to access a global pool (managed by the library code) that you have not configured or instantiated yet

flashp0wa
@flashp0wa:matrix.org
[m]
Thanks a lot m8! Its working fine now!!! :D
flashp0wa
@flashp0wa:matrix.org
[m]

Hey Everybody!

I have here a function called streamRead(). In this function i have defined the logic to read values from the database which has two event 'request.on('row', callback) and request.on('done', callback). Looking like this:

function streamRead {
let rowsToProcess = [];
request.on('row', row => {
  rowsToProcess.push(row);
  if (rowsToProcess.length >= 15) {
    request.pause();
    processRows();
  }
});
request.on('done', () => {
    let iNeedThisValueReturned = processRows();
});

console.log('This runs immediately after calling request.on('done',...), without waiting the function inside (processRows()) to finish. This is sad because i need the value returned from processRows()');
return iNeedThisValueReturned; //this is a promise actually containing the resolve with the value i need

}

So the problem is decribed in the console.log. If i call streamRead from somewhere else in the code. I get no return object. Like this:

async function aFunctionInAnotherFile {
let valueReturnedFromStreamRead = await asd.streamRead();
console.log(valueReturnedFromStreamRead) // This is empty because reques.on('done') is still running and 'return iNeedThisValueReturned' has been returned immidiately after reques.on('done') has been called. resulting in an empty object. Also console.log() is immediately running after the await function. Like the await is not working
}

I dont know if the problem that the 'await' not working in 'aFunctionInAnotherFile()' or I just have to let JS know to wait for on.request('done') to finish.
The main problem just to reiterate... the whole code finished running while the function in the reques.on('done') is still counting it's stuff
Anybody has any idea what I am missing... cant wrap my head around it for 2 days now. I hope i was able to represent in an understandable fashion. If not please dont hesitate to request clarification, i am trying my best :)

Thank a lot in advance!

Daniel Hensby
@dhensby
@flashp0wa:matrix.org seems like the problem is you're not waiting for the done callback to be fired before moving on. You need to make sure you wait for the async parts of the code to complete. You could "promisify" the stream or you can use a traditional callback approach.
Not trying to be rude, but this async nature of callbacks is pretty fundamental to node/js dev, so I won't go into it here, but there are lots of resources online - eg: https://node.dev/post/understanding-streams-in-node-js
or just searching something like "wait for stream to complete javascript" - also see https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Asynchronous/Introducing
flashp0wa
@flashp0wa:matrix.org
[m]

Okey, so the answer was to include in the code once(request, 'done'). Also it makes no sense to wait for the callbackfuntion, since that is not even fired (the streamRead still working on the 'row' events... meaning the query is still read working and reading data from the database.). This way i can wait for the 'done' event finish before returning to the caller.

So the solution looks like this:

function streamRead {
const { once } = require('events');


let rowsToProcess = [];
request.on('row', row => {
  rowsToProcess.push(row);
  if (rowsToProcess.length >= 15) {
    request.pause();
    processRows();
  }
});
request.on('done', () => {
    let iNeedThisValueReturned = processRows();
});

await once(request,'done');

return iNeedThisValueReturned; //this is a promise actually containing the resolve with the value i need

}
flashp0wa
@flashp0wa:matrix.org
[m]

:point_up: Edit: Okey, so the answer was to include in the code once(request, 'done'). Also it makes no sense to wait for the callbackfuntion, since that is not even fired (the streamRead still working on the 'row' events... meaning the query is still read working and reading data from the database.). This way i can wait for the 'done' event finish before returning to the caller.

So the solution looks like this:

function streamRead {
const { once } = require('events');


let rowsToProcess = [];
request.on('row', row => {
  rowsToProcess.push(row);
  if (rowsToProcess.length >= 15) {
    request.pause();
    processRows();
  }
});
request.on('done', () => {
    let iNeedThisValueReturned = processRows();
});

await once(request,'done');

return iNeedThisValueReturned;

}
Ernesto Gimeno
@egimenos
Hello
I posted in this issue, I didn't notice this chat existed
I'm struggling to make the right syntax work in order to get my query sanitized using template string literals
David Gasperoni
@mcdado
I replied directly in the issue
Ernesto Gimeno
@egimenos
Ok, thank you David. The thing is I still would like to be able to save the query in a variable, since it is way longer than the example I wrote in the issue.
I'm building the query in their own function to improve maintenability
I should probably try your other suggestion about using parameters
David Gasperoni
@mcdado
Ok, no problem. I’d suggest avoiding the template literal and simply create the string however you see fit and pass the variables as input parameters. Create the Request object and pass the parameters with the .input() method.
And run .execute to run the query/request
I might get some names wrong
I don’t have code at hand
Ernesto Gimeno
@egimenos
Hi again guys
So I'm trying to just place the query inline instead of using a variable, taking advantage of the tagged template literal to easily sanitize the inputs
Now the problem is I'm getting an error String or binary data would be truncated when executing this query:
return this.pool.request().query`
    UPDATE [MAIN].[dbo].[WEB_GESTION_TRACKINGS]
    SET STATUS  ='${status}', STATUS_DESC = '${statusDesc}', INFO_EXTENDIDA = '${JSON.stringify(
      latestEvent
    )}', URL = '${trackingUrl}',
    STATUS_SHORTCODE = '${statusShortcode}', UPDATED_AT =  convert(datetimeoffset, '${lastUpdateddAt}', 127), COURIER_CODE = '${carrier}', NOTAS = '${note}', STATUS_MSG = '${statusMessage}', SERVICE_ID = '${id}'
    WHERE TRACKING = '${tracking}'`;
  }
If I use the regular syntax with this.pool.request().query(...)the query just works
I would like to be able to see which is the SQL built by I don't know how
Ernesto Gimeno
@egimenos
Nevermind I figured out. Is not necesasary to wrap the string values in single quotes when using this syntax
I also noted that I can't use functions suchs as UPDATED_AT = convert(datetimeoffset, '${lastUpdateddAt}'
As I read in one of the issues, it is not a query builder
abhijit koik
@abhikoik_twitter
Hi Guys, I have datetimeoffset column in one of the tables. The value looks like this in the DB - 2014-02-14 22:59:59.9999999 +05:00
However when it retrieved through query I getting this as date object with few missing precision milliseconds like-2014-02-14T17:59:59.999Z.
I need to retrieve the DB value as it is i.e 2014-02-14 22:59:59.9999999 +05:00.
Is there way I can retrieve datetimeoffset as string than date? Any help is greatly appreciated . thanks
Daniel Hensby
@dhensby
@abhikoik_twitter have you tried to pull it out explicitly as a varchar rather than as a date?
abhijit koik
@abhikoik_twitter

Here is the code snippet I am using-

//2021-08-19 08:14:31.9139045 +00:00 - value in the DB column RecordedOn type datetimeoffset

` const request = new sql.Request(pool)

sql.map.register(String, sql.DateTimeOffset);
let result = await request.query("select RecordedOn from Records");
console.dir(result.recordsets[0][0].RecordedOn instanceof Date)
console.dir(result.recordsets[0][0].RecordedOn.toISOString())`

And this is the corresponding output

true 2021-08-19T08:14:31.913Z
Please note that I tried mapping datetimeoffset to string using sql.map.register(String, sql.DateTimeOffset);
But still it is returning as date, can you please guide me how can I get datetimeoffset as varchar or string.

Thanks