const oracledb = require('oracledb') const conf = require('./oracle-config') const PkgGateway = 'gateway' const log = require('../lib/log') oracledb.autoCommit = true oracledb.fetchAsString = [oracledb.CLOB] async function initialize () { await oracledb.createPool(conf.oracle_cn) } async function shutdown () { await oracledb.getPool().close() } exports.initialize = initialize exports.shutdown = shutdown function doRelease (conn) { conn.release(function (err) { if (err) { log.error('release() error', err.message) } }) } let extractResult = async function (result) { return new Promise(function (resolve, reject) { if (result && result.outBinds && result.outBinds.ret) { let lob = result.outBinds.ret let clobString = '' lob.setEncoding('utf8') lob.on('data', function (chunk) { clobString += chunk }) lob.on('close', function (err) { if (!err) resolve(JSON.parse(clobString)) else reject(err) }) lob.on('error', function (err) { reject(err) }) } else resolve([]) }) } let doConnect = async function (cb) { return oracledb.getConnection() } let doCreateTempLob = function (conn) { return conn.createLob(oracledb.CLOB) } let doLoadTempLob = function (conn, data) { return new Promise(function (resolve, reject) { doCreateTempLob(conn) .then((templob) => { templob.on( 'close', function () { }) templob.on( 'error', function (err) { reject(err) }) templob.on( 'finish', function () { resolve(templob) }) var Readable = require('stream').Readable var s = new Readable() s.push(JSON.stringify(data))// the string you want s.push(null) s.pipe(templob) }, (err) => { reject(err) }) }) } exports.readAll = async function (pkg, pars, ctx) { let sql = 'begin ' + PkgGateway + '.read(:par, :pkg, :ctx, :ret); end;' let conn = await doConnect() let parameters try { parameters = { par: { val: JSON.stringify(pars), dir: oracledb.BIND_IN, type: oracledb.STRING }, pkg: { val: pkg, dir: oracledb.BIND_IN, type: oracledb.STRING }, ctx: { val: JSON.stringify(ctx), dir: oracledb.BIND_IN, type: oracledb.STRING }, ret: { dir: oracledb.BIND_OUT, type: oracledb.CLOB } } let result = await conn.execute(sql, parameters) let ret = await extractResult(result) doRelease(conn) return (ret) } catch (err) { log.error('readAll err ', err.message, sql, parameters.pkg) doRelease(conn) throw new Error(err) } } exports.read = async function (pkg, key, ctx) { let sql = 'begin ' + PkgGateway + '.read(:key, :pkg, :ctx, :ret); end;' let conn = await doConnect() let result, ret, parameters try { parameters = { key: { val: parseInt(key), dir: oracledb.BIND_IN, type: oracledb.NUMBER }, pkg: { val: pkg, dir: oracledb.BIND_IN, type: oracledb.STRING }, ctx: { val: JSON.stringify(ctx), dir: oracledb.BIND_IN, type: oracledb.STRING }, ret: { dir: oracledb.BIND_OUT, type: oracledb.CLOB } } result = await conn.execute(sql, parameters) ret = await extractResult(result) doRelease(conn) return (ret) } catch (err) { log.error('read err ', err.message, sql, parameters.pkg) doRelease(conn) throw new Error(err) } } exports.create = async function (pkg, body, ctx) { let sql = 'begin ' + PkgGateway + '.create_(:obj, :pkg, :ctx, :ret); end;' let conn = await doConnect() let parameters try { let templob = await doLoadTempLob(conn, body) parameters = { obj: { val: templob, dir: oracledb.BIND_IN, type: oracledb.CLOB }, pkg: { val: pkg, dir: oracledb.BIND_IN, type: oracledb.STRING }, ctx: { val: JSON.stringify(ctx), dir: oracledb.BIND_IN, type: oracledb.STRING }, ret: { dir: oracledb.BIND_OUT, type: oracledb.CLOB } } let result = await conn.execute(sql, parameters) let ret = await extractResult(result) doRelease(conn) return (ret) } catch (err) { log.error('create err ', err.message, sql, parameters.pkg) doRelease(conn) throw new Error(err) } } exports.update = async function (key, pkg, body, ctx) { let sql = 'begin ' + PkgGateway + '.update_(:key, :obj, :pkg, :ctx, :ret); end;' let conn = await doConnect() let parameters try { let templob = await doLoadTempLob(conn, body) parameters = { key: { val: parseInt(key), dir: oracledb.BIND_IN, type: oracledb.NUMBER }, obj: { val: templob, dir: oracledb.BIND_IN, type: oracledb.CLOB }, pkg: { val: pkg, dir: oracledb.BIND_IN, type: oracledb.STRING }, ctx: { val: JSON.stringify(ctx), dir: oracledb.BIND_IN, type: oracledb.STRING }, ret: { dir: oracledb.BIND_OUT, type: oracledb.CLOB } } let result = await conn.execute(sql, parameters) let ret = await extractResult(result) doRelease(conn) return (ret) } catch (err) { log.error('create err ', err.message, sql, parameters.pkg) doRelease(conn) throw new Error(err) } } exports.delete = async function (pkg, key, ctx) { let sql = 'begin ' + PkgGateway + '.delete_(:key, :pkg, :ctx); end;' let conn = await doConnect() let parameters try { parameters = { key: { val: parseInt(key), dir: oracledb.BIND_IN, type: oracledb.NUMBER }, pkg: { val: pkg, dir: oracledb.BIND_IN, type: oracledb.STRING }, ctx: { val: JSON.stringify(ctx), dir: oracledb.BIND_IN, type: oracledb.STRING } } await conn.execute(sql, parameters) doRelease(conn) return } catch (err) { log.error('delete err ', err.message, sql, parameters.pkg) doRelease(conn) throw new Error(err) } } exports.method = async function (pkg, pars, ctx) { let conn = await doConnect() let sql let templob let parameters = { pkg: { val: pkg, dir: oracledb.BIND_IN, type: oracledb.STRING }, ctx: { val: JSON.stringify(ctx), dir: oracledb.BIND_IN, type: oracledb.STRING } } if (JSON.stringify(pars).length > 30000) { sql = 'begin ' + PkgGateway + '.method(:par, :pkg, :ctx); end;' templob = await doCreateTempLob(conn) templob = await doLoadTempLob(conn, pars) parameters.par = { val: templob, dir: oracledb.BIND_INOUT, type: oracledb.CLOB } } else { sql = 'begin ' + PkgGateway + '.method(:par, :pkg, :ctx, :ret); end;' parameters.par = { val: JSON.stringify(pars), dir: oracledb.BIND_IN, type: oracledb.STRING } parameters.ret = { dir: oracledb.BIND_OUT, type: oracledb.CLOB } } try { let result = await conn.execute(sql, parameters) let ret = await extractResult(result) doRelease(conn) return ret } catch (err) { log.error('method ' + pkg + ' err ', err.message, sql, parameters.pkg) doRelease(conn) throw new Error(err) } }