Comments (4)
How interesting. Can you confirm what you mean by "doesn't work"?
Do you have a minimal set queries / DB structure & data that I can use to replicate the issue?
from node-mssql.
Doesn't work = returns 0 rows.
My database has ARABIC_CI_AS
collation, and although it isn't desired, I can't easily change it.
If you want to test, here's a simple query to create the db:
CREATE DATABASE PersianTest
COLLATE ARABIC_CI_AS
USE PersianTest
CREATE TABLE People (ID INT, Name NVARCHAR(20))
INSERT INTO PersianTest.dbo.People (ID, Name) VALUES (1, 'شادی'), (2, 'شادي'), (3, 'علی'), (4, 'علي')
from node-mssql.
The same thing happens for procedures:
-- using varchar
CREATE PROCEDURE TestPersian @inputField VARCHAR(255) AS BEGIN
SELECT
Name
FROM
People
WHERE
Name = @inputField;
END
GRANT EXEC ON dbo.TestPersian TO PUBLIC
-- using nvarchar
CREATE PROCEDURE NTestPersian @inputField NVARCHAR(255) AS BEGIN
SELECT
Name
FROM
People
WHERE
Name = @inputField;
END
GRANT EXEC ON dbo.NTestPersian TO PUBLIC
Using sql directly:
/* persian text */
EXEC TestPersian 'علی'; -- ✅
EXEC NTestPersian 'علی'; -- ✅
EXEC TestPersian N'علی'; -- ✅
EXEC NTestPersian N'علی'; -- ❌
/* arabic text */
EXEC TestPersian 'علي'; -- ✅
EXEC NTestPersian 'علي'; -- ✅
EXEC TestPersian N'علي'; -- ✅
EXEC NTestPersian N'علي'; -- ✅
Using js:
/* arabic text */
data = await pool.request().input('inputField', VarChar, 'علي').execute('TestPersian'); // ✅
data = await pool.request().input('inputField', VarChar, 'علي').execute('NTestPersian'); // ✅
data = await pool.request().input('inputField', NVarChar, 'علي').execute('TestPersian'); // ✅
data = await pool.request().input('inputField', NVarChar, 'علي').execute('NTestPersian'); // ✅
/* persian text */
data = await pool.request().input('inputField', VarChar, 'علی').execute('TestPersian'); // ❌
data = await pool.request().input('inputField', VarChar, 'علی').execute('NTestPersian'); // ❌
data = await pool.request().input('inputField', NVarChar, 'علی').execute('TestPersian'); // ✅
data = await pool.request().input('inputField', NVarChar, 'علی').execute('NTestPersian'); // ❌
from node-mssql.
OK - this seems to be a problem either with the underlying tedious driver, or SQL as whole. I've just created this test script making use of raw tedious driver and get the same behaviour as I do with node-mssql
:
const { connect, Request, TYPES } = require('tedious');
function doConnect () {
return new Promise((resolve, reject) => {
const connection = connect({
server: 'localhost',
options: {
encrypt: true,
database: 'PersianTest',
trustServerCertificate: true,
rowCollectionOnRequestCompletion: true
},
authentication: {
type: 'default',
options: {
userName: 'sa',
password: 'yourStrong(!)Password'
}
}
}, (err) => {
if (err) { reject(err) } else { resolve(connection) }
})
})
}
(async () => {
const names = ['شادی', 'شادي', 'علی', 'علي']
const res = await Promise.all(names.map((name) => {
return new Promise(async (resolve, reject) => {
const conn = await doConnect()
const request = new Request(`SELECT * FROM [People] WHERE [Name] = @name`, (err, count, rows) => {
conn.close()
if (err) {
reject(err)
} else {
resolve(rows.map((cols) => {
return cols.reduce((acc, col) => ({
...acc,
[col.metadata.colName]: col.value
}), {})
}, []))
}
})
request.addParameter('name', TYPES.NVarChar, name)
conn.execSql(request)
})
}))
console.log(res)
})().then(() => {
console.log('Done')
}).catch(console.error)
Output:
[
[],
[ { ID: 1, Name: 'شادي' }, { ID: 2, Name: 'شادي' } ],
[],
[ { ID: 3, Name: 'علي' }, { ID: 4, Name: 'علي' } ]
]
If I don't use a parameter, it works as expected:
const { connect, Request, TYPES } = require('tedious');
function doConnect () {
return new Promise((resolve, reject) => {
const connection = connect({
server: 'localhost',
options: {
encrypt: true,
database: 'PersianTest',
trustServerCertificate: true,
rowCollectionOnRequestCompletion: true
},
authentication: {
type: 'default',
options: {
userName: 'sa',
password: 'yourStrong(!)Password'
}
}
}, (err) => {
if (err) { reject(err) } else { resolve(connection) }
})
})
}
(async () => {
const names = ['شادی', 'شادي', 'علی', 'علي']
const res = await Promise.all(names.map((name) => {
return new Promise(async (resolve, reject) => {
const conn = await doConnect()
const request = new Request(`SELECT * FROM [People] WHERE [Name] = '${name}'`, (err, count, rows) => {
conn.close()
if (err) {
reject(err)
} else {
resolve(rows.map((cols) => {
return cols.reduce((acc, col) => ({
...acc,
[col.metadata.colName]: col.value
}), {})
}, []))
}
})
conn.execSql(request)
})
}))
console.log(res)
})().then(() => {
console.log('Done')
}).catch(console.error)
Output:
[
[ { ID: 1, Name: 'شادي' }, { ID: 2, Name: 'شادي' } ],
[ { ID: 1, Name: 'شادي' }, { ID: 2, Name: 'شادي' } ],
[ { ID: 3, Name: 'علي' }, { ID: 4, Name: 'علي' } ],
[ { ID: 3, Name: 'علي' }, { ID: 4, Name: 'علي' } ]
]
Would you mind opening an issue with the tedious driver directly?
from node-mssql.
Related Issues (20)
- Potential impact of disabling connection validation when released from the pool HOT 1
- RequestError: Incorrect syntax near '='. Unable to query to table when values containing equal sign '=', HOT 7
- The "config.server" property is required and must be of type string. HOT 1
- @azure/core-rest-pipeline breaking support for node.js 14 HOT 2
- Does node-mssql support multiSubnetFailover? HOT 9
- Suspected memory leak when streaming data HOT 4
- arrayRowMode returns a function for the column type HOT 6
- Testing library-using code in Jest results in SyntaxError: Unexpected token 'export' HOT 1
- Request timeouts in mssql/msnodesqlv8 when using default Pool size HOT 4
- Bulk Insert row lock not supported HOT 1
- Committing a transaction early, within a transaction ? HOT 4
- [Question] Does node-mssql support Kerberos authentication HOT 1
- ConnectionError: Connection not yet open. HOT 1
- How to bind a multidimensional array HOT 3
- How to add order by from user input? HOT 1
- (node:1009) MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 error listeners added to [ConnectionPool]. Use emitter.setMaxListeners() to increase limit HOT 1
- Bulk Insert Time Out HOT 1
- Bulk upload with a DateTime2 column HOT 3
- error self signed certificate - Lambda function Nodejs connect to mssql on ec2 instance after migrating from 6.2.0 to 10.0.2 HOT 6
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from node-mssql.