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
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

Ashish Karanje
@karanje-ashish

Hello Guys,
I am trying to use mssql package - as it offers connection pooling to connect to Azure SQL DB
using the authentication type: "azure-active-directory-password", Following is the code snippet.
`
// Import the mssql package
var sql = require("mssql");
// Create a configuration object for our Azure SQL connection parameters
const config = {
driver: "tedious",
server: "xxxxxxxxxSQL.database.windows.net",
authentication: {
type: "azure-active-directory-password",
options: {
userName: "AzureAdUser", // Use your username
password: "AzureADpwd", // Use your password
domain: "dummy.domain",
}
},
options: {
database: "sqlDB",
encrypt: true,
},
};

function getData() {

var conn = new sql.ConnectionPool(dbConfig);
conn.connect()
// Successfull connection
.then(function () {

// Create request instance, passing in connection instance
var req = new sql.Request(conn);

// Call mssql's query method passing in params
req.query("Select * from dbo.table1")
.then(function (recordset) {
console.log(recordset);
conn.close();
})
// Handle sql statement execution errors
.catch(function (err) {
console.log(err);
conn.close();
})

})
// Handle connection errors
.catch(function (err) {
console.log(err);
conn.close();
});
}

getData();

`

When I execute this code - I get the following error.
But I am able to connect to this same Azure SQL db from Sql Server Mgmt Studio using same Credentials and authentication type from my machine.

PS C:\Projects\POC> node .\index1.js
ConnectionError: Security token could not be authenticated or authorized.
at C:\Projects\POC\node_modules\mssql\lib\tedious\connection-pool.js:71:17
at Connection.onConnect (C:\Projects\POC\node_modules\tedious\lib\connection.js:1043:9)
at Object.onceWrapper (node:events:510:26)
at Connection.emit (node:events:390:28)
at Connection.emit (C:\Projects\POC\node_modules\tedious\lib\connection.js:1071:18)
at C:\Projects\POC\node_modules\tedious\lib\connection.js:2961:22
at processTicksAndRejections (node:internal/process/task_queues:96:5) {
code: 'EFEDAUTH',
originalError: ConnectionError: Security token could not be authenticated or authorized.
at ConnectionError (C:\Projects\POC\node_modules\tedious\lib\errors.js:13:12)
at C:\Projects\POC\node_modules\tedious\lib\connection.js:2960:63
at processTicksAndRejections (node:internal/process/task_queues:96:5) {
code: 'EFEDAUTH'
}
}

Can someone provide any help with this matter, to help me understand what is that i'm missing in the above code?
Or if anyone was able to leverage mssql to use Authentication.type = "azure-active-directory-password" or "azure-active-directory-access-token"

Daniel Hensby
@dhensby
I'm not an expert with these authentication types, it's probably worth looking at the tedious docs and then making sure it's right as per their docs and then provide provide that config to the mssql connection
Ashish Karanje
@karanje-ashish
@dhensby : Ok, thanks. I was able to figure out the adjustment with config object.
Jacob Barker-Sargent
@JacobBSargent

Dealing with a DB connection issue. Not sure what is causing the error.

Code:

const sql = require('mssql');

const config = {
    server: 'JACOBSARGEN0DB3\\SQLEXPRESS',
    port: 000000,
    user: 'exampleUser',
    password: 'examplePassword',
    database: 'example',
    options: {
        encrypt: false,
        enableArithAbort: true,
    },
}

async function call() {
    try {
        let pool = await sql.connect(config)
        let result = await pool.request().query('SELECT * FROM Clients')
        console.log(result)
        sql.close()
    } catch (error) {
        console.log(error.message)
        console.log('---------------')
        console.log(error)
        sql.close()
    }
}
call()
sql.on('error', err => {
    console.log('error has occured!')
   console.log(err.message)
})

Error:

ConnectionError: Failed to connect to JACOBSARGEN0DB3\SQLEXPRESS in 15000ms
    at C:\Users\jacobsargent\Desktop\window\node_modules\mssql\lib\tedious\connection-pool.js:70:17
    at Connection.onConnect (C:\Users\jacobsargent\Desktop\window\node_modules\tedious\lib\connection.js:1049:9)
    at Object.onceWrapper (node:events:646:26)
    at Connection.emit (node:events:526:28)
    at Connection.emit (C:\Users\jacobsargent\Desktop\window\node_modules\tedious\lib\connection.js:1077:18)
    at Connection.connectTimeout (C:\Users\jacobsargent\Desktop\window\node_modules\tedious\lib\connection.js:1282:10)
    at Timeout._onTimeout (C:\Users\jacobsargent\Desktop\window\node_modules\tedious\lib\connection.js:1227:12)
    at listOnTimeout (node:internal/timers:559:17)
    at processTimers (node:internal/timers:502:7) {
  code: 'ETIMEOUT',
  originalError: ConnectionError: Failed to connect to JACOBSARGEN0DB3\SQLEXPRESS in 15000ms
      at Connection.connectTimeout (C:\Users\jacobsargent\Desktop\window\node_modules\tedious\lib\connection.js:1282:26)
      at Timeout._onTimeout (C:\Users\jacobsargent\Desktop\window\node_modules\tedious\lib\connection.js:1227:12)
      at listOnTimeout (node:internal/timers:559:17)
      at processTimers (node:internal/timers:502:7) {
    code: 'ETIMEOUT',
    isTransient: undefined
  }
}
Daniel Hensby
@dhensby
@JacobBSargent I know it can be a bit tricky to connect to SQLExpress instances, have you looked through past issues like this one: tediousjs/node-mssql#213 ?
David Gasperoni
@mcdado
Also, instead of using the instance name consider using the port, if possible for your setup. In either case, just stick to either one.
And of course make sure that if you use the port, that the instance service is bound to that port.
a670372846
@a670372846
[Nest] 8344 - 2022/04/21 23:52:55 ERROR [TypeOrmModule] Unable to connect to the database. Retrying (2)...
ConnectionError: Failed to connect to 127.0.0.1:1433 - 7364:error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol:c:\ws\deps\openssl\openssl\ssl\statem\statem_lib.c:1958:
at connectListener (E:\v_o\avenger-api\node_modules\mssql\lib\tedious\connection-pool.js:70:17)
at Connection.onConnect (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:1763:9)
at Object.onceWrapper (events.js:520:26)
at Connection.emit (events.js:400:28)
at Connection.emit (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:1906:18)
at Connection.socketError (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:2221:12)
at Socket.<anonymous> (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:2010:44)
at Socket.emit (events.js:412:35)
at emitErrorNT (internal/streams/destroy.js:106:8)
at emitErrorCloseNT (internal/streams/destroy.js:74:3)
a670372846
@a670372846
When I used this "options": {
"cryptoCredentialsDetails": {
"minVersion": "TLSv1"
}
}
ormconfig.json
[Nest] 15764 - 2022/04/22 00:12:34 ERROR [TypeOrmModule] Unable to connect to the database. Retrying (2)...
ConnectionError: Failed to connect to 127.0.0.1:1433 - self signed certificate
at connectListener (E:\v_o\avenger-api\node_modules\mssql\lib\tedious\connection-pool.js:70:17)
at Connection.onConnect (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:1763:9)
at Object.onceWrapper (events.js:520:26)
at Connection.emit (events.js:400:28)
at Connection.emit (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:1906:18)
at Connection.socketError (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:2221:12)
at Socket.<anonymous> (E:\v_o\avenger-api\node_modules\tedious\src\connection.ts:2010:44)
at Socket.emit (events.js:412:35)
at emitErrorNT (internal/streams/destroy.js:106:8)
at emitErrorCloseNT (internal/streams/destroy.js:74:3)
a670372846
@a670372846
link to mssql2012
Daniel Hensby
@dhensby
As it says, you're using a self signed certificate so it won't connect as it's not a trusted endpoint
Jacob Barker-Sargent
@JacobBSargent
@dhensby @mcdado Thanks guys, I have a further question:- how do we ensure a secure connection to a remote MSSQL DB? Not sure if this is a silly question. As a caveat, we can't distinguish which IP is allowed access as the app trying to create the connection as it is on Heroku. Would love to hear your thoughts.
David Gasperoni
@mcdado
The app is on Heroku with possibly dynamic IP and the DB is internet accessible? I’m not an expert windows sysadmin but if you use a secure password and only the required port is open on the firewall, it should be ok. In that case avoid using windows authentication and use a dedicated sql server login associated to a dedicated user in the database
For more sql server focused questions I’d suggest this community: http://sqlslack.com/
Jacob Barker-Sargent
@JacobBSargent

@mcdado, Yes that's correct. Thanks for the point on the 'only required port' and the two links.

For reference to this chat, I found an addon for Heroku called QuotaGuard, it sets up two static IP addresses for your app. Allowing you to work within firewall rules. See more here: https://elements.heroku.com/addons/quotaguardstatic

Allowing for a secure connection! 🎉

Jacob Barker-Sargent
@JacobBSargent
Quick question, when setting up the config for the server, which is an IP > how is this meant to be presented? I have seen people use the server name like 'NAME\SQLEXPRESS' but when you are connecting to an IP address, how does this change - or would below be correct?
const config = {
    server: '111.111.11.111',
    user: 'barr',
    password: 'foo',
    database: 'dbName',
    options: {
        encrypt: false,
        trustServerCertificate: true,
        truestedConnection: false,
        instancename: 'SQLEXPRESS',
        enableArithAbort: true,
    },
    port: 53771,
}