💡 Oracle Database:
Alternatively, sqler-oracle can be used instead of ODBC.
Setup:
Install the Oracle Instant Client & ODBC Instant Client (latet versions seems to work best)
Windows ODBC Data Source
Configure the ODBC driver via the ODBC Data Source Administrator:
UNIX /etc/odbc.ini unixODBC (Oracle ODBC Connection Parameters)
[ODBC Data Sources]
OracleXE=Test
[OracleXE]
Driver = /usr/lib/oracle/18.4.0.0.0/client/lib/libsqora.so.18.4
DSN = OracleXE
ServerName = XE
UserID = system
Password = oracl3local
Examples:
The examples below use the following setup:
Private Options Configuration: (appended to the subsequent connection options, shows other connections for illustration purposes)
{
"univ": {
"db": {
"mssql": {
"username":"sa",
"password": "sqlS3rv35local"
},
"oracle": {
"username": "system",
"password": "oracl3local"
},
"mysql": {
"username":"root",
"password": "my3qllocal"
},
"postgresql": {
"username":"postgres",
"password": "p0stgr3slocal"
},
"cache": {}
}
}
}
Connection Options Configuration:
{
"db": {
"dialects": {
"odbc": "sqler-odbc"
},
"connections": [
{
"id": "oracle",
"name": "oracle",
"dir": "db/oracle",
"service": "OracleXE",
"dialect": "odbc",
"pool": {},
"driverOptions": {
"connection": {
"DSN": "${service}",
"UID": "system",
"PWD": "oracl3local",
"FileUsage": 1
},
"pool": {
"shrink": true
}
}
}
]
}
}
NOTE:
db.connections.driverOptions.connectionforDSNinterpolates intodb.connections[].service
Test code that illustrates how to use Oracle + ODBC with various examples
// assuming "conf" contains combined "univ" and "db" objects from above
// create/initialize ODBC manager
const manager = new Manager(conf);
await manager.init();
// see subsequent examples for different examples
const result = await runExample(manager, 'mssql');
console.log('Result:', result);
// after we're done using the manager we should close it
process.on('SIGINT', async function sigintDB() {
await manager.close();
console.log('Manager has been closed');
});
Create Table:
-- db/oracle/setup/create.tables.sql
-- When using a single statement only the statement inside the single quotes needs to exist in the SQL file
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE "TEST" ("ID" integer not null primary key, "NAME" varchar2(384), "CREATED_AT" timestamp with time zone, "UPDATED_AT" timestamp with time zone)';
EXECUTE IMMEDIATE 'CREATE TABLE "TEST2" ("ID" integer not null primary key, "NAME" varchar2(384), "REPORT" CLOB, "CREATED_AT" timestamp with time zone, "UPDATED_AT" timestamp with time zone)';
END;
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
// create multiple tables in a single execution
const rslt = await manager.db[connName].setup.create.tables();
return rslt;
};
Create Rows:
-- db/oracle/create.table.rows.sql
-- When using a single statement BEGIN/END can be omitted
BEGIN
BEGIN
INSERT INTO "TEST" ("ID", "NAME", "CREATED_AT", "UPDATED_AT")
VALUES (:id, :name, :created, :updated);
END;
BEGIN
INSERT INTO "TEST2" ("ID", "NAME", "REPORT", "CREATED_AT", "UPDATED_AT")
VALUES (:id2, :name2, :report2, :created2, :updated2);
END;
END;
'use strict';
const Fs = require('fs');
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
// The odbc module needs the date to be in a valid ANSI compliant format.
// Could also use:
// https://www.npmjs.com/package/moment-db
const date = new Date().toISOString().replace('T', ' ').replace('Z', '');
// The odbc module currently doesn't support Fs.ReadStream/Fs.createReadStream()
const report = await Fs.promises.readFile('./test/files/audit-report.png');
// Insert rows into multiple tables within a single ODBC execution
const rslt = await manager.db[connName].create.table.rows({
binds: {
id: 1, name: 'TABLE: 1, ROW: 1', created: date, updated: date,
id2: 1, name2: 'TABLE: 2, ROW: 1', report2: report, created2: date, updated2: date
}
});
return rslt;
};
Read Rows:
-- db/oracle/read.table.rows.sql
SELECT TST.ID AS "id", TST.NAME AS "name", NULL AS "report",
TST.CREATED_AT AS "created", TST.UPDATED_AT AS "updated"
FROM dbo.TEST TST
WHERE UPPER(TST.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%')
UNION
SELECT TST2.ID AS "id", TST2.NAME AS "name", CONVERT(varchar(max), TST2.REPORT, 1) AS "report",
TST2.CREATED_AT AS "created", TST2.UPDATED_AT AS "updated"
FROM dbo.TEST2 TST2
WHERE UPPER(TST2.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%')
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
const rslt = await manager.db[connName].read.table.rows({ binds: { name: 'table' } });
return rslt;
};
Update Rows:
Although the following updates can be made within a single SQL script, multiple SQL scripts are used to illustrate the use of tranactions and/or prepared statements.
-- db/oracle/update.table1.rows.sql
UPDATE TEST
SET NAME = :name, UPDATED_AT = :updated
WHERE ID = :id
-- db/oracle/update.table2.rows.sql
UPDATE TEST2
SET NAME = :name2, UPDATED_AT = :updated2
WHERE ID = :id2
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
// The odbc module needs the date to be in a valid ANSI compliant format.
// Could also use:
// https://www.npmjs.com/package/moment-db
const date = new Date().toISOString().replace('T', ' ').replace('Z', '');
// binds
const binds1 = {
id: 1, name: 'TABLE: 1, ROW: 1 (UPDATE)', updated: date
};
const binds2 = {
id2: 1, name2: 'TABLE: 2, ROW: 1 (UPDATE)', updated2: date
};
const rtn = {};
//-------------------------------------------------------
// There are two different ways to perform a transaction
// 1. Implicit (suitable for a single execution per tx)
// 2. Explicit (suitable for multiple executions per tx)
// using implicit transactions:
console.log(`---------------------------- Implicit Transaction Update ----------------------------`);
await implicitTransactionUpdate(manager, connName, binds1, binds2, rtn);
// Using an explicit transaction:
console.log(`---------------------------- Explicit Transaction Update ----------------------------`);
await explicitTransactionUpdate(manager, connName, binds1, binds2, rtn);
// Using a prepared statement:
console.log(`---------------------------- Prepared Statement Update ----------------------------`);
await preparedStatementUpdate(manager, connName, binds1, rtn);
// Using a prepared statement within an explicit transaction
console.log(`---------------------------- Prepared Statement Explicit Transaction Update ----------------------------`);
await preparedStatementExplicitTxUpdate(manager, connName, binds1, rtn);
return rtn;
};
async function implicitTransactionUpdate(manager, connName, binds1, binds2, rtn) {
rtn.txImpRslts = new Array(2); // don't exceed connection pool count
// Example execution in parallel using an implicit transaction for
// each SQL execution (autoCommit = true is the default)
rtn.txImpRslts[0] = manager.db[connName].update.table1.rows({
name: 'TX Implicit 1 (UPDATE)', // name is optional
binds: binds1
});
rtn.txImpRslts[1] = manager.db[connName].update.table2.rows({
name: 'TX Implicit 2 (UPDATE)', // name is optional
binds: binds2
});
// could have also ran is series by awaiting when the SQL function is called
rtn.txImpRslts[0] = await rtn.txImpRslts[0];
rtn.txImpRslts[1] = await rtn.txImpRslts[1];
}
async function explicitTransactionUpdate(manager, connName, binds1, binds2, rtn) {
rtn.txExpRslts = new Array(2); // don't exceed connection pool count
try {
// start a transaction
const txId = await manager.db[connName].beginTransaction();
// Example execution in parallel (same transacion)
rtn.txExpRslts[0] = manager.db[connName].update.table1.rows({
name: 'TX Explicit 1 (UPDATE)', // name is optional
autoCommit: false,
transactionId: txId, // ensure execution takes place within transaction
binds: binds1
});
rtn.txExpRslts[1] = manager.db[connName].update.table2.rows({
name: 'TX Explicit 2 (UPDATE)', // name is optional
autoCommit: false,
transactionId: txId, // ensure execution takes place within transaction
binds: binds2
});
// could have also ran is series by awaiting when the SQL function is called
rtn.txExpRslts[0] = await rtn.txExpRslts[0];
rtn.txExpRslts[1] = await rtn.txExpRslts[1];
// could commit using either one of the returned results
await rtn.txExpRslts[0].commit();
} catch (err) {
if (rtn.txExpRslts[0] && rtn.txExpRslts[0].rollback) {
// could rollback using either one of the returned results
await rtn.txExpRslts[0].rollback();
}
throw err;
}
}
async function preparedStatementUpdate(manager, connName, binds, rtn) {
rtn.psRslts = new Array(2); // don't exceed connection pool count
try {
for (let i = 0; i < rtn.psRslts.length; i++) {
// update with expanded name
binds.name = `TABLE: 1, ROW: ${i} (Prepared statement UPDATE)`;
// Using an implicit transcation (autoCommit defaults to true):
rtn.psRslts[i] = manager.db[connName].update.table1.rows({
name: `PS ${i} (UPDATE)`, // name is optional
// flag the SQL execution as a prepared statement
// this will cause the statement to be prepared
// and a dedicated connection to be allocated from
// the pool just before the first SQL executes
prepareStatement: true,
// include the bind parameters
binds
});
}
// wait for parallel executions to complete
for (let i = 0; i < rtn.psRslts.length; i++) {
rtn.psRslts[i] = await rtn.psRslts[i];
}
} finally {
// could call unprepare using any of the returned execution results
if (rtn.psRslts[0] && rtn.psRslts[0].unprepare) {
// since prepareStatement = true, we need to close the statement
// and release the statement connection back to the pool
await rtn.psRslts[0].unprepare();
}
}
}
async function preparedStatementExplicitTxUpdate(manager, connName, binds, rtn) {
rtn.txExpPsRslts = new Array(3); // don't exceed connection pool count
try {
// start a transaction
const txId = await manager.db[connName].beginTransaction();
for (let i = 0; i < rtn.txExpPsRslts.length; i++) {
// update with expanded name
binds.name += `TABLE: 1, ROW: ${i} (Prepared statement with txId "${txId}" UPDATE)`;
rtn.txExpPsRslts[i] = manager.db[connName].update.table1.rows({
name: `TX/PS ${i} (UPDATE)`, // name is optional
autoCommit: false, // don't auto-commit after execution
transactionId: txId, // ensure execution takes place within transaction
prepareStatement: true, // ensure a prepared statement is used
binds
});
}
// wait for parallel executions to complete
for (let i = 0; i < rtn.txExpPsRslts.length; i++) {
rtn.txExpPsRslts[i] = await rtn.txExpPsRslts[i];
}
// unprepare will be called when calling commit
// (alt, could have called unprepare before commit)
await rtn.txExpPsRslts[0].commit();
} catch (err) {
if (rtn.txExpPsRslts[0] && rtn.txExpPsRslts[0].rollback) {
// unprepare will be called when calling rollback
// (alt, could have called unprepare before rollback)
await rtn.txExpPsRslts[0].rollback();
}
throw err;
}
}
Delete Rows:
-- db/oracle/delete.table.rows.sql
-- When using a single statement BEGIN/END can be omitted
BEGIN
DELETE FROM TEST TST
WHERE TST.ID = :id;
END;
BEGIN
DELETE FROM TEST2 TST
WHERE TST.ID = :id2;
END;
END;
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
// Delete rows from multiple tables within a single ODBC execution
const rslt = await manager.db[connName].delete.table.rows({
binds: { id: 1, id2: 1 }
});
return rslt;
};
Delete Table:
-- db/oracle/setup/delete.tables.sql
-- When using a single statement only the statement inside the single quotes needs to exist in the SQL file
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE TEST';
EXECUTE IMMEDIATE 'DROP TABLE TEST2';
END;
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
// delete multiple tables in a single execution
const rslt = await manager.db[connName].setup.delete.tables();
return rslt;
};
Create:
-- db/oracle/hr/create.country.sql
INSERT INTO HR.COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
VALUES (:id, :name, :region)
async function runExample(conf) {
const mgr = new Manager(conf);
// initialize connections and set SQL functions
await mgr.init();
// begin tranaction
const txId = await mgr.db.oracle.beginTransaction();
let exec1, exec2;
try {
// set the transaction ID on the execution options
// so the SQL executions are invoked
// within the same transaction scope
// execute within a transaction scope
// (i.e. autoCommit === false and transactionId = txId)
exec1 = await mgr.db.oracle.hr.create.country({
autoCommit: false,
transactionId: txId,
binds: {
id: 1,
name: 'New Country 1',
region: 2
}
});
// execute within the same transaction scope
// and commit after the satement has executed
// (i.e. autoCommit === true and transactionId = txId)
exec2 = await mgr.db.oracle.hr.create.country({
autoCommit: false,
transactionId: txId,
binds: {
id: 2,
name: 'New Country 2',
region: 2
}
});
} catch (err) {
if (exec1) {
// can rollback using either exc1.rollback() or exc2.rollback()
await exc1.rollback();
}
}
// return the results
return {
manager: mgr,
result: {
country1: exec1 && exec1.rows,
country2: exec2 && exec2.rows
}
};
}