Segmentation Fault while attempting to use Table Value Paramaters #254
Comments
|
https://github.com/TimelordUK/node-sqlserver-v8/blob/master/test/tvp.js Are you able to provide example of what sql and proc causes issue. There is a tvp test suite is one of these working. Without code to reproduce issue I can’t realistically suggest a fix. |
|
After doing some more testing today it doesn't even really appear to be the sproc it's just trying to use TVPs that's causing the error CREATE TYPE dbo.MatterColumnSearch AS TABLE
(
MatterColumn VARCHAR(100) NOT NULL
,SearchTerm NVARCHAR(MAX) NOT NULL
,Comparator NVARCHAR(20) NOT NULL
,PRIMARY KEY CLUSTERED (MatterColumn ASC) WITH (IGNORE_DUP_KEY = OFF)
);
GO
SELECT
MatterColumn
,SearchTerm
,Comparator
FROM @MatterColumnSearch; |
|
can you test without the clustered index such as below const createTypeSql = `CREATE TYPE ${tableTypeName} AS TABLE (description varchar(max), username nvarchar(30), age int, salary real, code numeric(18,3), start_date datetime2)`
const insertProcedureSql = `create PROCEDURE ${insertProcedureTypeName}
@tvp ${tableTypeName} READONLY
AS
BEGIN
set nocount on
INSERT INTO ${tableName}
(
[description],
[username],
[age],
[salary],
[code],
[start_date]
)
SELECT
[description],
[username],
[age],
[salary],
[code],
[start_date]
n FROM @tvp tvp
END` |
|
same error if I try using it without the index |
|
can u try and get this sample to run with your connection i get this output to log IF OBJECT_ID('dbo.tmpTableBuilder', 'U') IS NOT NULL DROP TABLE dbo.tmpTableBuilder;
CREATE TABLE dbo.tmpTableBuilder ([id] int , [MatterColumn] varchar (100) NOT NULL, [SearchTerm] nvarchar (MAX) NOT NULL, [Comparator] nvarchar (20)
NOT NULL)
IF TYPE_ID(N'tmpTableBuilderType') IS not NULL drop type tmpTableBuilderType
CREATE TYPE tmpTableBuilderType AS TABLE (
id int,
MatterColumn varchar(100) not null,
SearchTerm nvarchar(max) not null,
Comparator nvarchar(20) not null)
{
"schema": "dbo",
"name": "tmpTableBuilderType",
"rows": [],
"columns": [
{
"name": "id",
"userType": "id int",
"scale": 0,
"precision": 10,
"type": {
"offset": 0,
"declaration": "int",
"length": 0
}
},
{
"name": "MatterColumn",
"userType": "MatterColumn varchar",
"scale": 0,
"precision": 0,
"type": {
"offset": 0,
"declaration": "varchar",
"length": 100
}
},
{
"name": "SearchTerm",
"userType": "SearchTerm nvarchar(0)",
"scale": 0,
"precision": 0,
"type": {
"offset": 0,
"declaration": "nvarchar",
"length": 0
}
},
{
"name": "Comparator",
"userType": "Comparator nvarchar(10)",
"scale": 0,
"precision": 0,
"type": {
"offset": 0,
"declaration": "nvarchar",
"length": 10
}
}
]
}
[
{
"id": 0,
"MatterColumn": "MatterColumn_0",
"SearchTerm": "SearchTerm_0",
"Comparator": "Comparator_0"
},
{
"id": 1,
"MatterColumn": "MatterColumn_1",
"SearchTerm": "SearchTerm_1",
"Comparator": "Comparator_1"
},
{
"id": 2,
"MatterColumn": "MatterColumn_2",
"SearchTerm": "SearchTerm_2",
"Comparator": "Comparator_2"
},
{
"id": 3,
"MatterColumn": "MatterColumn_3",
"SearchTerm": "SearchTerm_3",
"Comparator": "Comparator_3"
},
{
"id": 4,
"MatterColumn": "MatterColumn_4",
"SearchTerm": "SearchTerm_4",
"Comparator": "Comparator_4"
}
]
const sql = require('msnodesqlv8')
const connectionString = 'Driver={ODBC Driver 17 for SQL Server}; Server=DESKTOP-VIUCH90;UID=x; PWD=x; Database=node'
builder().then(() => console.log('done'))
async function builder () {
function makeOne (i) {
return {
id: i,
MatterColumn: `MatterColumn_${i}`,
SearchTerm: `SearchTerm_${i}`,
Comparator: `Comparator_${i}`
}
}
try {
const rows = 5
const connection = await sql.promises.open(connectionString)
const tableName = 'tmpTableBuilder'
const mgr = connection.tableMgr()
const builder = mgr.makeBuilder(tableName)
builder.addColumn('id').asInt().isPrimaryKey(1)
builder.addColumn('MatterColumn').asVarChar(100).notNull()
builder.addColumn('SearchTerm').asNVarCharMax().notNull()
builder.addColumn('Comparator').asNVarChar(20).notNull()
const vec = []
for (let i = 0; i < rows; ++i) {
vec.push(makeOne(i))
}
const t = builder.toTable()
const typeName = 'tmpTableBuilderType'
const dropTypeSql = `IF TYPE_ID(N'${typeName}') IS not NULL drop type ${typeName}`
const userTypeSql = `CREATE TYPE ${typeName} AS TABLE (
id int,
MatterColumn varchar(100) not null,
SearchTerm nvarchar(max) not null,
Comparator nvarchar(20) not null)`
const selectSql = 'select * from ?;'
const create = builder.createTableSql
const drop = builder.dropTableSql
console.log(drop)
await builder.drop()
console.log(create)
await builder.create()
console.log(dropTypeSql)
await connection.promises.query(dropTypeSql)
console.log(userTypeSql)
await connection.promises.query(userTypeSql)
const table = t.asTableType()
console.log(JSON.stringify(table, null, 4))
// convert a set of objects to rows
table.addRowsFromObjects(vec)
// use a type the native driver can understand, using column based bulk binding.
const tp = sql.TvpFromTable(table)
const res = await connection.promises.query(selectSql, [tp])
console.log(JSON.stringify(res.first, null, 4))
await builder.drop()
await connection.promises.close()
} catch (e) {
console.log(e)
}
} |
|
I'm helping move this along. Off of that sql server we get this result |
|
Hmmm. Not the segmentation fault but if you change selectSql to You get Works |
|
More details: It appears to be an issue with getUserTypeTable with quoted types |
|
Thanks a lot I will take a look at it
…On Tue, 12 Jul 2022 at 18:09, Aaron Aichlmayr ***@***.***> wrote:
More details:
const table = await connection.promises.getUserTypeTable('dbo.' + typeName)
Works (only changing that line)
const table = await connection.promises.getUserTypeTable('[dbo].[' +
typeName + ']') Segfaults (only changing that line)
const table = await connection.promises.getUserTypeTable('[' + typeName +
']') Segfaults (only changing that line)
const typeName = '[dbo].[tmpTableBuilderType]' also works.
It appears to be an issue with getUserTypeTable with quoted types
—
Reply to this email directly, view it on GitHub
<#254 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ABXWJG7HZBLBSDW44HDFGZLVTWRENANCNFSM52XN6WPA>
.
You are receiving this because you commented.Message ID:
***@***.***>
|
|
are you able to check master - your lines inserted below now returns table const sql = require('msnodesqlv8')
// const connectionString = 'Driver={ODBC Driver 17 for SQL Server}; Server=DESKTOP-VIUCH90;UID=linux; PWD=linux; Database=node'
const { TestEnv } = require('../../test/env/test-env')
const env = new TestEnv()
const connectionString = env.connectionString
builder().then(() => {
console.log('done')
})
async function builder () {
function makeOne (i) {
return {
id: i,
MatterColumn: `MatterColumn_${i}`,
SearchTerm: `SearchTerm_${i}`,
Comparator: `Comparator_${i}`
}
}
try {
const rows = 5
const connection = await sql.promises.open(connectionString)
const tableName = 'tmpTableBuilder'
const mgr = connection.tableMgr()
const builder = mgr.makeBuilder(tableName)
builder.addColumn('id').asInt().isPrimaryKey(1)
builder.addColumn('MatterColumn').asVarChar(100).notNull()
builder.addColumn('SearchTerm').asNVarCharMax().notNull()
builder.addColumn('Comparator').asNVarChar(20).notNull()
const vec = Array(rows).fill(0).map((_, i) => makeOne(i))
const t = builder.toTable()
const dropTypeSql = builder.dropTypeSql
const userTypeSql = builder.userTypeTableSql
const typeName = `${tableName}Type`
const selectSql = `DECLARE @test AS ${typeName};
INSERT INTO @test SELECT * FROM ?;
SELECT * FROM @test`
const create = builder.createTableSql
const drop = builder.dropTableSql
console.log(drop)
await builder.drop()
console.log(create)
await builder.create()
console.log(dropTypeSql)
await connection.promises.query(dropTypeSql)
console.log(userTypeSql)
await connection.promises.query(userTypeSql)
const table = t.asTableType()
const table2 = await connection.promises.getUserTypeTable('dbo.' + typeName)
// Works (only changing that line)
const table3 = await connection.promises.getUserTypeTable('[dbo].[' + typeName + ']')
const table4 = await connection.promises.getUserTypeTable('[' + typeName + ']')
console.log(JSON.stringify(table, null, 4))
// convert a set of objects to rows
table.addRowsFromObjects(vec)
// use a type the native driver can understand, using column based bulk binding.
const tp = sql.TvpFromTable(table)
// can now clear rows
table.rows = []
const res = await connection.promises.query(selectSql, [tp])
console.log(JSON.stringify(res.meta[0], null, 4))
console.log(JSON.stringify(res.first, null, 4))
await builder.drop()
await connection.promises.close()
} catch (e) {
console.log(e)
}
}IF OBJECT_ID('dbo.tmpTableBuilder', 'U') IS NOT NULL DROP TABLE dbo.tmpTableBuilder;
CREATE TABLE dbo.tmpTableBuilder ([id] int , [MatterColumn] varchar (100) NOT NULL, [SearchTerm] nvarchar (MAX) NOT NULL, [Comparator] nvarchar (20)
NOT NULL)
IF TYPE_ID(N'dbo.tmpTableBuilderType') IS not NULL drop type dbo.tmpTableBuilderType
CREATE TYPE dbo.tmpTableBuilderType AS TABLE ([id] int , [MatterColumn] varchar (100) NOT NULL, [SearchTerm] nvarchar (MAX) NOT NULL, [Comparator] nv
archar (20) NOT NULL)
{
"schema": "dbo",
"name": "tmpTableBuilderType",
"rows": [],
"columns": [
{
"name": "id",
"userType": "int ",
"scale": 0,
"precision": 10,
"type": {
"offset": 0,
"declaration": "int",
"length": 0
}
},
{
"name": "MatterColumn",
"userType": "varchar (100) NOT NULL",
"scale": 0,
"precision": 0,
"type": {
"offset": 0,
"declaration": "varchar",
"length": 100
}
},
{
"name": "SearchTerm",
"userType": "nvarchar (MAX) NOT NULL",
"scale": 0,
"precision": 0,
"type": {
"offset": 0,
"declaration": "nvarchar",
"length": 0
}
},
{
"name": "Comparator",
"userType": "nvarchar (20) NOT NULL",
"scale": 0,
"precision": 0,
"type": {
"offset": 0,
"declaration": "nvarchar",
"length": 20
}
}
]
}
[
{
"size": 10,
"name": "id",
"nullable": true,
"type": "number",
"sqlType": "int"
},
{
"size": 100,
"name": "MatterColumn",
"nullable": false,
"type": "text",
"sqlType": "varchar"
},
{
"size": 0,
"name": "SearchTerm",
"nullable": false,
"type": "text",
"sqlType": "nvarchar"
},
{
"size": 20,
"name": "Comparator",
"nullable": false,
"type": "text",
"sqlType": "nvarchar"
}
]
[
{
"id": 0,
"MatterColumn": "MatterColumn_0",
"SearchTerm": "SearchTerm_0",
"Comparator": "Comparator_0"
},
{
"id": 1,
"MatterColumn": "MatterColumn_1",
"SearchTerm": "SearchTerm_1",
"Comparator": "Comparator_1"
},
{
"id": 2,
"MatterColumn": "MatterColumn_2",
"SearchTerm": "SearchTerm_2",
"Comparator": "Comparator_2"
},
{
"id": 3,
"MatterColumn": "MatterColumn_3",
"SearchTerm": "SearchTerm_3",
"Comparator": "Comparator_3"
},
{
"id": 4,
"MatterColumn": "MatterColumn_4",
"SearchTerm": "SearchTerm_4",
"Comparator": "Comparator_4"
}
]
done
Process finished with exit code 0 |
|
Works for me, thanks! |
|
This should now be released on 2.6.0 |
|
I’m closing this as I believe issue is resolved. Please open with new example if you are till experiencing issues. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I've been experiencing an issue when trying to pass a TVP Parameter to a stored procedure, a segmentation fault is thrown.
I have tried using both version 17 and 18 of the mssql library and both of them are having the same issue. Do you have any idea what could be the cause of this issue?
The text was updated successfully, but these errors were encountered: