81 lines
2.4 KiB
JavaScript
81 lines
2.4 KiB
JavaScript
// services/appSqlPool.js
|
|
// Shared MSSQL connection pool for the PORTAL'S OWN tables (ZWORK_ORDERS,
|
|
// ZCUST_USERS, ZAPPROVAL_STEPS, pl_account_map, cf_items, bs_config, etc.) —
|
|
// deliberately a SEPARATE server/database from the SAP B1 SQL Server
|
|
// (services/sqlPool.js), so nothing this app creates ever touches the SAP
|
|
// database. Same shape/API as sqlPool.js (getPool/query), just pointed at
|
|
// APP_SQL_* env vars instead of SQL_*.
|
|
'use strict';
|
|
const sql = require('mssql');
|
|
|
|
let _pool = null;
|
|
let _connecting = false;
|
|
let _waiters = [];
|
|
|
|
function buildConfig() {
|
|
return {
|
|
server: process.env.APP_SQL_HOST,
|
|
port: parseInt(process.env.APP_SQL_PORT) || 1433,
|
|
user: process.env.APP_SQL_USER,
|
|
password: process.env.APP_SQL_PASSWORD,
|
|
database: process.env.APP_SQL_DATABASE,
|
|
connectionTimeout: 30000,
|
|
requestTimeout: 60000,
|
|
pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },
|
|
options: {
|
|
encrypt: true,
|
|
trustServerCertificate: true,
|
|
enableArithAbort: true,
|
|
cryptoCredentialsDetails: { minVersion: 'TLSv1' },
|
|
},
|
|
};
|
|
}
|
|
|
|
async function getPool() {
|
|
if (_pool && _pool.connected) return _pool;
|
|
|
|
if (_connecting) {
|
|
return new Promise((resolve, reject) => _waiters.push({ resolve, reject }));
|
|
}
|
|
|
|
_connecting = true;
|
|
try {
|
|
const pool = new sql.ConnectionPool(buildConfig());
|
|
|
|
pool.on('error', err => {
|
|
console.error('[APP-SQL-POOL] Pool error — resetting:', err.message);
|
|
_pool = null;
|
|
});
|
|
|
|
await pool.connect();
|
|
_pool = pool;
|
|
console.log(`[APP-SQL-POOL] ✅ Connected ${process.env.APP_SQL_DATABASE} @ ${process.env.APP_SQL_HOST}`);
|
|
_waiters.forEach(w => w.resolve(_pool));
|
|
return _pool;
|
|
} catch (err) {
|
|
_pool = null;
|
|
console.error('[APP-SQL-POOL] ❌ Connection failed:', err.message);
|
|
_waiters.forEach(w => w.reject(err));
|
|
throw err;
|
|
} finally {
|
|
_connecting = false;
|
|
_waiters = [];
|
|
}
|
|
}
|
|
|
|
async function query(sqlText, params = []) {
|
|
const pool = await getPool();
|
|
const req = pool.request();
|
|
let i = 0;
|
|
const text = sqlText.replace(/\?/g, () => {
|
|
const name = `p${i}`;
|
|
req.input(name, params[i]);
|
|
i++;
|
|
return `@${name}`;
|
|
});
|
|
const result = await req.query(text);
|
|
return result.recordset || [];
|
|
}
|
|
|
|
module.exports = { getPool, query };
|