InterSystems Cache

💡 InterSystems Cachè:

Setup:

Install the drivers from ftp.intersystems.com

Windows ODBC Data Source

Configure the ODBC driver via the ODBC Data Source Administrator:

UNIX /etc/odbc.ini unixODBC (Cachè ODBC Connection Parameters)

[ODBC Data Sources]
Lab=Lab

[Lab]
Driver=/usr/cachesys/bin/libcacheodbc35.so
Description=Lab
Host=example.com
Namespace=LAB
UID=lab
Password=labExamplePwd
Port=41000
Protocol=TCP
Query Timeout=1
Static Cusrsors=0
Trace=off
TraceFile=iodbctrace.log
Authentication Method=0
Security Level=2
Service Principal Name=example.com

[Default]
Driver=/usr/cachesys/bin/libcacheodbc35.so

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": {}
    }
  }
}

NOTE: univ.db.cache does not require any additional private properties since the Cachè ODBC driver only requires the DSN in the connection options

Connection Options Configuration:

{
  "db": {
    "dialects": {
      "odbc": "sqler-odbc"
    },
    "connections": [
      {
        "id": "cache",
        "name": "cache",
        "dir": "db/cache",
        "service": "Lab",
        "dialect": "odbc",
        "pool": {},
        "driverOptions": {
          "connection": {
            "DSN": "${service}"
          },
          "pool": {
            "shrink": true
          }
        }
      }
    ]
  }
}

NOTE: db.connections.driverOptions.connection.DSN interpolates into db.connections[].service

Test code that illustrates how to use Cachè + 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');
});

Read:

-- db/cache/site/read.lab.departments.sql
SELECT DP.ID AS "id", DP.LabDeptCode AS "code", DP.LabDeptName AS "name"
FROM SITE.MA_LabDept DP
WHERE UPPER(DP.LabDeptName) LIKE UPPER('%' || :labDeptName || '%')
ORDER BY DP.LabDeptName ASC

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // execute the SQL statement and capture the results
  const rslt = await manager.db[connName].site.read.lab.departments({
    binds: {
      labDeptName: 'Blood'
    }
  });

  return rslt;
};

Create:

-- db/cache/site/create.lab.departments.sql
INSERT INTO MA_LabDept (ID, LabDeptName, LabDeptCode)
VALUES (:id, :name, :code)

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // begin tranaction
  const txId = await manager.db[connName].beginTransaction();
  
  let exec1, exec2;
  try {
    // set the transaction ID on the execution options
    // so the SQL executions are invoked within the
    // same transaction scope from the connection pool

    // execute within a transaction scope, but don't commit
    // (i.e. autoCommit === false and transactionId = txId)
    exec1 = await manager.db[connName].site.create.lab.departments({
      autoCommit: false,
      transactionId: txId,
      binds: {
        id: 1,
        name: 'Blood Bank',
        code: 'BB'
      }
    });

    // execute within the same transaction scope
    // and commit after the satement has executed
    // (i.e. autoCommit === true and transactionId = txId)
    exec2 = await manager.db[connName].site.create.lab.departments({
      autoCommit: true,
      transactionId: txId,
      binds: {
        id: 2,
        name: 'Blood Bank Send Out',
        code: 'BBSO'
      }
    });
  } catch (err) {
    if (exec1) {
      // can rollback using either exc1.rollback() or exc2.rollback()
      await exc1.rollback();
    }
  }

  // return the results
  return {
    dept1: exec1 && exec1.rows,
    dept2: exec2 && exec2.rows
  };
};