@ngrey5/odbc

Utility package based of the standard odbc package. This package is meant to provide easier typing and functionality that might be lacking from the base package.

Requirements

The setup below refers to the requirments details from the standard odbc package and are subject to change.

• unixODBC binaries and development libraries for module compilation
  • on Ubuntu/Debian sudo apt-get install unixodbc unixodbc-dev
  • on RedHat/CentOS sudo yum install unixODBC unixODBC-devel
  • on OSX
    • using macports.org sudo port unixODBC
    • using brew brew install unixODBC
  • on IBM i yum install unixODBC unixODBC-devel (requires yum)
• odbc drivers for target database
• properly configured odbc.ini and odbcinst.ini.

Installation

After insuring that all requirements are installed you may install by using the following npm command:

npm install @ngrey5/odbc

Documentation

ODBCConnection

• Creating a Connection
• connect
• query
• callProcedure
• createStatement
• tables
• columns
• cursor
• setIsolationLevel
• beginTransaction
• commit
• rollback
• close
• wrapConnection

ODBCPool

• Creating a Pool
• Pool Options
• init
• connect
• query
• close

ODBCCursor

• fetch
• noData
• read
• close

ODBCStatement

• prepare
• bind
• execute
• close

Results

• ODBCResult
• ODBCError

Modifiers

• ODBCConnectionModifier

Types

• ODBCQueryParameter
• ODBCQueryParameters
• ODBCQueryOptions
• ODBCCursorOptions
• ODBCCallProcedureOptions
• ODBCTablesOptions
• ODBCColumnsOptions
• ODBCResultPipe
• ODBCQueryPipe

ODBCConnection

Creates a connection to an ODBC data source

Creating a Connection

There are a multitude of ways to create an odbc connection:

1. Standard Class Constructor

const connection = new ODBCConnection();
await connection.connect(CONNECTION_STRING);

2. Async static method

const connection = await ODBCConnection.create(CONNECTION_STRING);

3. Helper function

const connection = await createODBCConnection(CONNECTION_STRING);

connect

Initializes the connection to the ODBC connection string provided

await connection.connect(CONNECTION_STRING);

query

Executes a query on the open odbc connection.

Parameters:

• statement: string - The sql statement
• parameters: ODBCQueryParameters - The parameters to bind to the statement
• options: ODBCQueryOptions - The options to apply to the query execution

All values passed to parameters will be bound to any ?'s found in the statement. The number of parameters must match the number of bound characters in the statement.

const query = `SELECT * FROM "Table" WHERE "column" = ?`;
const parameters = ["value"];
const result = await connection.query(query, parameters);

callProcedure

Calls a procedure defined on the database. Takes an ODBCCallProcedureOptions object

const result = await connection.callProcedure(options);

createStatement

Creates an ODBCStatement class from the connection

const statement = await connection.createStatement();

tables

Calls a procedure defined on the database. Takes an ODBCTablesOptions object

const result = await connection.tables(options);

columns

Calls a procedure defined on the database. Takes an ODBCColumnsOptions object

const result = await connection.columns(options);

cursor

Creates an ODBCCursor class from the connection

Parameters:

• statement: string - The statement to execute
• parameters: ODBCQueryParameters - The parameters to bind to the statement
• options: ODBCQueryOptions - the options to apply to the query exection

const cursor = await connection.cursor(`SELECT * FROM "Table"`);

setIsolationLevel

Sets the isolation level of the connection. Use ODBCIsolationLevel enum for name isolation levels

await connection.setIsolationLevel(ODBCIsolationLevel.Serializable);

beginTransaction

Opens a transaction on the current connection. Can be optionally passed an isolation level to set the isolation of the transaction

await connection.beginTransaction(ISOLATION_LEVEL);

commit

Commits the changes made on the open transaction. If no transaction is open this method will do nothing

await connection.commit();

rollback

Rolls back the changes made on the open transaction. If no transaction is open this method will do nothing

await connection.rollback();

close

Closes the current connection. If a transaction is in progress, it will automatically call rollback before closing

await connection.close();

wrapConnection

Wraps a connection created from the base odbc package inside this package's ODBCConnection class. This function is used by ODBCPool to return the connection class. It is being exposed for any possible use.

import odbc from "odbc";
const connection = await odbc.connect(CONNECTION_STRING);
const connectionClass = new ODBCConnection();
connectionClass.wrapConnection(connection);

ODBCPool

Creates a connection pool to an ODBC data source

Creating a Pool

There are a multitude of ways to create an odbc connection pool:

1. Standard Class Constructor

const pool = new ODBCPool();
await pool.init({
  connectionString: CONNECTION_STRING,
});

2. Async static method

const connection = await ODBCPool.create({
  connectionString: CONNECTION_STRING,
});

3. Helper function

const connection = await createODBCPool({
  connectionString: CONNECTION_STRING,
});

Pool Options

Options can be set on the connection pool

• connectionString: string - the connection string to the data source
• connectionTimeout: number - the number of seconds the connection should wait before a timeout
• loginTimeout: number - the number of seconds a login should wait before a timeout
• initialSize: number - the initial number of connections to be created by the pool
• incrementSize: number - the number of connection that the pool should add when all connections are currently in use
• maxSize: number - the max number of connection to allow within the pool
• shrink: boolean - if the pool should remove additional connections when they are not being used

init

Intializes the database pool with options

await pool.init(POOL_OPTIONS);

connect

Gets an ODBCConnection from the pool and returns it for use

const connection = await pool.connect();

query

Utility function to execute a query on an open connection in the pool. This method will get a connection, execute the query, return the result set, and return the connection to the pool

Refer to ODBCConnection.query for all available options to supply to this method. They are effectively the same invocation

const query = `SELECT * FROM "Table"`;
const result = await pool.query(query);

close

Closes the entire database pool. Any connection that are currently in progress will no be closed. When those connection are closed they will be discarded

await pool.close();

ODBCCursor

A cursor created by the ODBCConnection.cursor method. Used to retrieve partial data from an overarching dataset that would normally by returned from the entire query

fetch

Fetches a data of length specified by the fetchSize set on the cursor. If no data is remaining it will return an empty array

const result = await cursor.fetch();

noData

If the cursor has no data remaining to fetch. Cannot return a true value unless fetch has been called at least once

while (!cursor.noData()) {
  const result = await cursor.fetch();
}

read

Reads each result set with fetch and returns the result to the callback function provided.

cursor.read((result, error) => {
  if (error) {
    // react to an error
  }
  // do something with each result
});

close

Closes the cursor

await cursor.close();

ODBCStatement

A statement created from ODBCConnection.createStatement method. Allows preparing of a commonly used statement and binding parameters to it multiple times.

prepare

Prepares an SQL statement with or without parameters to bind to

const query = `SELECT * FROM "Table" WHERE "column" = ?`;
await statement.prepare(query);

bind

Binds an array of values to the parameters on the prepared SQL statement created with prepare. Cannot be called before prepare

const query = `SELECT * FROM "Table" WHERE "column" = ?`;
await statement.prepare(query);
await statement.bind(["column-value"]);

execute

Executes the prepared and optionally bound statement

const result = await statement.execute();

close

Closes the statement, freeing the statement handle. Calling methods after closing will result in an error

Note: this will not close the connection the statement was created on

await statement.close();

ODBCResult

All queries and calls invoked on a connection will return a result of this type.

Values:

• rows: array - the resulting rows from the database execution
• count: number - the number of rows affected by the execution. Note: this is not the length of the rows array
• fields: ColumnDefinition - the definitions of the fields returned by the execution
• statement: string the statement executed
• parameters: ODBCQueryParameters The array of parameters passed to the statement. For input/output or output parameters on a procedure, this value will reflect the output values of the procedure
• return: unknown The return value of some procedures. For many DBMS this will always be undefined
• length: number the length of the rows array

ODBCError

Any error thrown by this package will be this error class

Values:

• query: information pertaining to the query the attempted to execute
• errors: odbc errors provided directly from the standard odbc package
• name: the name of the error
• message: the message of the error
• stack: the error stack of the error

ODBCConnectionModifier

A modification to an ODBC connection. A modifier can be specific to a ODBCPool, ODBCConnection, ODBCCursor, or ODBCStatement

If a modifier is applied to an ODBCPool, it will be applied to all connections on that pool

If a modifier is applied to an ODBCConnection it will be applied to all actions on that connection. i.e cursors and statements

Takes a config object of type ODBCConnectionModifierOptions to supply transformations on the data received and returned from the connection

Note: any query pipes applied will not affect an ODBCStatement as it will always use the database's native functionality to bind parameters and cannot be overridden.

const options: ODBCConnectionModifierOptions = {
  resultPipes: [
    // pipes the data returned from calls to the database
  ],
  queryPipes: [
    // pipes the query and parameters supplied to any calls
  ],
};

pool.useConnectionModifier(new ODBCConnectionModifier(options));
connection.useConnectionModifier(new ODBCConnectionModifier(options));

Types

ODBCQueryParameter

Defined the types available to supply to a query parameter array

ODBCQueryParameters

Shorthand type for ODBCQueryParameter[]

ODBCQueryOptions

Options to supply to a .query method on a ODBCPool or ODBCConnection

ODBCCursorOptions

Options to set on an ODBCCursor

ODBCCallProcedureOptions

Options to set on a .callProcedure method on a ODBCConnection

ODBCTablesOptions

Options to set on a .tables method on a ODBCConnection

ODBCColumnsOptions

Options to set on a .columns method on a ODBCConnection

ODBCResultPipe

Function type to supply to the resultPipes array of a ODBCConnectionModifier

ODBCQueryPipe

Function type to supply to the queryPipes array of a ODBCConnectionModifier

Notes

This documentation is not very verbose. It is a small project intended for a very opinionated setup and implementation of the odbc package.

You may benefit from using the odbc package directly instead of this. However, feel free to use as you'd like.

License

Copyright (c) 2022 Nick Grey

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.