@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

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

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.

@ngrey5/odbc

@ngrey5/odbc

Requirements

Installation

Documentation

ODBCConnection

ODBCPool

ODBCCursor

ODBCStatement

Results

Modifiers

Types

ODBCConnection

Creating a Connection

1. Standard Class Constructor

2. Async static method

3. Helper function

connect

query

callProcedure

createStatement

tables

columns

cursor

setIsolationLevel

beginTransaction

commit

rollback

close

wrapConnection

ODBCPool

Creating a Pool

1. Standard Class Constructor

2. Async static method

3. Helper function

Pool Options

init

connect

query

close

ODBCCursor

fetch

noData

read

close

ODBCStatement

prepare

bind

execute

close

ODBCResult

ODBCError

ODBCConnectionModifier

Types

ODBCQueryParameter

ODBCQueryParameters

ODBCQueryOptions

ODBCCursorOptions

ODBCCallProcedureOptions

ODBCTablesOptions

ODBCColumnsOptions

ODBCResultPipe

ODBCQueryPipe

Notes

License

Readme

Keywords

Package Sidebar

Install

Repository

Homepage

DownloadsWeekly Downloads

Version

License

Unpacked Size

Total Files

Last publish

Collaborators

`connect`

`query`

`callProcedure`

`createStatement`

`tables`

`columns`

`cursor`

`setIsolationLevel`

`beginTransaction`

`commit`

`rollback`

`close`

`wrapConnection`

`init`

`connect`

`query`

`close`

`fetch`

`noData`

`read`

`close`

`prepare`

`bind`

`execute`

`close`

Weekly Downloads