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
}
: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;
}
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}'`;
}
this.pool.request().query(
...)
the query just works
UPDATED_AT = convert(datetimeoffset, '${lastUpdateddAt}'
2014-02-14 22:59:59.9999999 +05:00
2014-02-14T17:59:59.999Z
.2014-02-14 22:59:59.9999999 +05:00
.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
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"
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
}
}
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)
@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! 🎉
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,
}
dhensby on master
Update changelog for v5 Merge pull request #796 from dh… (compare)