Табличные Значенные Параметры в Узле / Утомительном Примере
У меня есть функция, которая очень хорошо работает для выполнения хранимых процедур и фрагментов SQL. Мне было интересно, сможет ли кто-нибудь помочь мне сделать эквивалентный callProcedure для табличных параметров?
Моя текущая функция заключается в следующем.
`var exec = function (sql, callback) {
var request = new Request(sql, function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
} else {
console.log(rowCount + ' rows');
}
callback(err, obj);
});
var obj = [];
request.on('row', function (columns) {
var values = {};
columns.forEach(function (column) {
if (column.isNull) {
values[column.metadata.colName] = null;
} else {
values[column.metadata.colName] = column.value;
}
});
obj.push(values);
//console.log(obj);
//console.log(" this is the obj in the row function");
});
conn.execSql(request);
//console.log('obj: ' + obj);
return obj;
function columnMetadata(columnsMetadata) {
columnsMetadata.forEach(function (column) {
//console.log(column);
});
}`
Я сделал это в SQL, чтобы сделать тип таблицы:
`--Create the data type
CREATE TYPE dbo.inventoryRequestType AS TABLE
(
ItemNo int NULL
, Qty int NULL
, Department nvarchar(50) NULL
, RequestedBy nvarchar(100) NULL
)
GO`
И эта хранимая процедура используется для вставки с использованием нового типа таблицы:
`Create PROCEDURE [dbo].[create_purchasing_list]
-- Add the parameters for the stored procedure here
@purchaseList inventoryRequestType ReadOnly
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Inventory Requests]
(
ItemNo
, Qty
, Department
, RequestedBy
)
SELECT
ItemNo
, Qty
, Department
, RequestedBy
FROM @purchaseList
END`
В SQL для выполнения хранимой процедуры можно использовать следующее:
`DECLARE @purchaseList inventoryRequestType
INSERT INTO @purchaseList
SELECT 4444, 1, 'layup', 'marc'
EXEC Purchasing.dbo.create_purchasing_list @purchaseList
`
---> вот где я заблудился. Я не уверен, как заставить эту последнюю часть работать с node / tedious
Вот пример того, как я хотел бы определить таблицу в функции, с которой мне нужна помощь.
Столбцы и строки Переменные будут определены с помощью содержащей функции.
var table = {
columns: [
Columns
//{name: 'id', type: Types.Int},
//{name: 'firstName', type: Types.nvarchar, length: 50}
//{name: 'option', type: Types.Bit}
],
rows: [
Rows
//[15, 'Bob', true],
//[16, 'Rob', false]
]
};
Заранее спасибо,
Марк
2 ответа
Теперь у меня есть полностью функциональное решение. Это очень динамично, поэтому я хочу поделиться им, так как я не смог найти решение в примерах здесь.
Это мой файл db-functions.js, который вызывается моим app.js
var express = require('express');
var tds = require('tedious');
var Request = require('tedious').Request;
var Types = require('tedious').TYPES;
var ConnectionPool = require('tedious-connection-pool');
var poolConfig = {
min: 10,
log: true
};
var config = {
userName: "User",
password: "password",
server: "192.168.1.123", //--------------------{ Production Server }
options:
{
requestTimeout: 30 * 1000,
instanceName: '\InstanceOnServer',
//instanceName: '\SQLServer', // Name of SQL Instance
rowCollectionOnRequestCompletion: true,
database: 'PlantDB',
encrypt: true,
debug: {
data: true, // lots of info generated in console when true
payload: false,
token: false,
packet: true, // was on true
log: true
}
}
};
var pool = new ConnectionPool(poolConfig, config);//multiple connections
pool.acquire(function (err, connection) {
if (err) {
console.log(err);
} else {
console.log('connected');
}
})
//var conn = new tds.Connection(config);
pool.on('error', function (err) {
console.log(err);
});
var conn = new tds.Connection(Config);
function requestDone(rowCount, more) {
console.log(rowCount + "rows------");
console.log(column);
}
function infoError(info) {
console.log(info.number + ' : ' + info.message);
}
function debug(message) {
console.log(message);
}
var exec = function (sql, callback) {
// use this function to execute SELECT statements and Stored Procedures
var request = new Request(sql, function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
} else {
console.log(rowCount + ' rows');
}
callback(err, obj);
});
var obj = []; //holds the data generated by the SQL statement when applicable
request.on('row', function (columns) { //identify individual rows
var values = {}; //insert a value
columns.forEach(function (column) { //identify the column
if (column.isNull) { /*adds a key to the value so that each item in
the object is broken into "Column: Value" pairs*/
values[column.metadata.colName] = null;
} else {
values[column.metadata.colName] = column.value;
}
});
obj.push(values);
});
conn.execSql(request);
//console.log('obj: ' + obj);
return obj;
function columnMetadata(columnsMetadata) {
columnsMetadata.forEach(function (column) {
//console.log(column);
});
}
};
var storedProcedures = {
'purchasing_list': //This is an actual stored procedure that exists in my
//Purchases Database which is used to identify this object
{
'fullName': 'Purchases.dbo.purchasing_list',
//fullname of stored procedure
'variable': 'purchaseList',
//the name of my "Table Type" Variable created in SQL
'Columns' : [
{ 'name': 'ItemNo', 'type': Types.Int }
, { 'name': 'ProductName', 'type': Types.NVarChar }
, { 'name': 'ProductDescription', 'type': Types.NVarChar }
, { 'name': 'UnitOfMeasure', 'type': Types.NVarChar }
, { 'name': 'Qty', 'type': Types.Int }
, { 'name': 'Department', 'type': Types.NVarChar }
, { 'name': 'RequestedBy', 'type': Types.NVarChar }
]
},
'another_stored_procedure':
{
'fullName': 'Purchases.dbo.another_stored_procedure',
'variable': 'purchaseList',
'Columns' : [
{ 'name': 'ItemNo', 'type': Types.Int }
, { 'name': 'ProductName', 'type': Types.NVarChar }
, { 'name': 'ProductDescription', 'type': Types.NVarChar }
, { 'name': 'UnitOfMeasure', 'type': Types.NVarChar }
, { 'name': 'Qty', 'type': Types.Int }
, { 'name': 'Department', 'type': Types.NVarChar }
, { 'name': 'RequestedBy', 'type': Types.NVarChar }
]
}
}
var callProc = function (procedureName, Rows, callback) {
var usefulData = storedProcedures[procedureName];
var message = '';
var table = {
columns: usefulData['Columns'],
rows: Rows
//insert an array of arrays with values defined by storedProcedure
};
var request = new Request(usefulData['fullName'], function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
message = 'There was a problem submitting your request!';
} else {
console.log(rowCount + 'No errors in TVP')
//this will likely return an undefined rowCount since this function
//is generally used to insert and update
message = 'Success'
}
callback(err, message);
});
request.on('doneProc', function (rowCount, more, returnStatus, rows) {
//console.log('Row Count' + rowCount);
//console.log('More? ' + more);
//console.log('Return Status: ' + returnStatus);
//console.log('Rows:' + rows);
})
request.addParameter(usefulData['variable'], Types.TVP, table);
//Add a table type set of parameters to the request to complete the
//transaction
conn.callProcedure(request);
};
var insert = function (sql, callback) {
var request = new Request(sql, function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
callback = err;
} else {
console.log('Insert Statement succeeded');
callback = 'Success';
}
});
conn.execSql(request);
};
var update = function (sql, callback) {
var insert = '';
var request = new Request(sql, function (err, rowCount) {
if (err) {
insert = ('Statement failed: ' + err)
} else {
insert = ('Insert Statement succeeded');
}
callback(err, insert);
});
conn.execSql(request);
};
exports.conn = conn
exports.exec = exec
exports.callProc = callProc
exports.insert = insert
exports.update = update
`
В файле App.js я загрузил свою базу данных так:
var db = require('./public/javascripts/db-functions');
CallProcedure, который позволяет вставлять или обновлять несколько строк, можно сделать следующим образом:
app.route('/update_purchasing')
.post(function (req, res) {
var Rows = req.body.Rows;
db.callProc('create_purchasing_list', Rows, function (err, success) {
if (err) {
res.json('Error creating purchasing list: ' + err);
} else {
res.json('Successfully updated your purchase list');
}
})
})
Строки могут выглядеть следующим образом, основываясь на столбцах, выбранных в хранимой процедуре:
Rows = [
[0101, 'hair gel', 'makes your hair stick up', 'vat', 1, 'house', 'Bob'],
[0102, 'carbon', 'makes light boats', 'roll', 1, 'Layup', 'Rob']
]`
Пример того, как выполнить хранимую процедуру, используя EXEC, может выглядеть так:
app.get('/start', function (req, res) {
var sess = req.session;
db.exec("dbo.populate_shipping_schedule" + "parameter1", function(err, schedule) {
if (!err) {
sess.schedule = schedule;
res.render('start', { title: 'Start' });
} else {
console.log('This is an Error: ' + err)
}
})
})
`
Используя ваш оператор "В SQL для выполнения хранимой процедуры можно использовать следующее:" Я обнаружил, что вы можете добавить это в переменную и передать в свое соединение SQL, и это работает нормально.
Вы даже можете динамически добавлять несколько строк в свой TVP, если замените
(Select 4444,1,'layup', 'marc'); с чем-то вроде
(Select id,value1,text1, text2 from yourTable)...
var theSQL = DECLARE @purchaseList inventoryRequestType;
theSQL += INSERT INTO @purchaseList;
theSQL += SELECT 4444, 1, 'layup', 'marc';
theSQL += EXEC Purchasing.dbo.create_purchasing_list @purchaseList;
let pool = poolManager.getDBPool(yourpoolmanager)
return await pool.request().query(theSQL)
`var storedProcedures = {
'purchasing_list':
{
'fullName': 'Purchases.dbo.purchasing_list',
'variable': 'purchaseList',
'Columns' : [
{ 'name': 'ItemNo', 'type': Types.Int }
, { 'name': 'ProductName', 'type': Types.NVarChar }
, { 'name': 'ProductDescription', 'type': Types.NVarChar }
, { 'name': 'UnitOfMeasure', 'type': Types.NVarChar }
, { 'name': 'Qty', 'type': Types.Int }
, { 'name': 'Department', 'type': Types.NVarChar }
, { 'name': 'RequestedBy', 'type': Types.NVarChar }
]
}
}
var callProc = function (procedureName, Rows,callback) {
var usefulData = storedProcedures[procedureName];
var table = {
columns: usefulData['Columns'],
rows: Rows //insert an array of arrays with values defined by storedProcedure
};
var request = new Request(usefulData['fullName'], function (err, rowCount) {
if (err) {
console.log('Statement failed: ' + err);
callback = err;
} else {
console.log('No errors in TVP')
callback = 'Success';
}
});
request.addParameter(usefulData['variable'], Types.TVP, table);
conn.callProcedure(request);
};`