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.
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
- using macports.org
- on IBM i
yum install unixODBC unixODBC-devel(requires yum)
- on Ubuntu/Debian
- odbc drivers for target database
- properly configured odbc.ini and odbcinst.ini.
After insuring that all requirements are installed you may install by using the following npm command:
npm install @ngrey5/odbc- Creating a Connection
- connect
- query
- callProcedure
- createStatement
- tables
- columns
- cursor
- setIsolationLevel
- beginTransaction
- commit
- rollback
- close
- wrapConnection
- ODBCQueryParameter
- ODBCQueryParameters
- ODBCQueryOptions
- ODBCCursorOptions
- ODBCCallProcedureOptions
- ODBCTablesOptions
- ODBCColumnsOptions
- ODBCResultPipe
- ODBCQueryPipe
Creates a connection to an ODBC data source
There are a multitude of ways to create an odbc connection:
const connection = new ODBCConnection();
await connection.connect(CONNECTION_STRING);const connection = await ODBCConnection.create(CONNECTION_STRING);const connection = await createODBCConnection(CONNECTION_STRING);Initializes the connection to the ODBC connection string provided
await connection.connect(CONNECTION_STRING);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);Calls a procedure defined on the database. Takes an ODBCCallProcedureOptions object
const result = await connection.callProcedure(options);Creates an ODBCStatement class from the connection
const statement = await connection.createStatement();Calls a procedure defined on the database. Takes an ODBCTablesOptions object
const result = await connection.tables(options);Calls a procedure defined on the database. Takes an ODBCColumnsOptions object
const result = await connection.columns(options);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"`);Sets the isolation level of the connection. Use ODBCIsolationLevel enum for name isolation levels
await connection.setIsolationLevel(ODBCIsolationLevel.Serializable);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);Commits the changes made on the open transaction. If no transaction is open this method will do nothing
await connection.commit();Rolls back the changes made on the open transaction. If no transaction is open this method will do nothing
await connection.rollback();Closes the current connection. If a transaction is in progress, it will automatically call rollback before closing
await connection.close();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);Creates a connection pool to an ODBC data source
There are a multitude of ways to create an odbc connection pool:
const pool = new ODBCPool();
await pool.init({
connectionString: CONNECTION_STRING,
});const connection = await ODBCPool.create({
connectionString: CONNECTION_STRING,
});const connection = await createODBCPool({
connectionString: CONNECTION_STRING,
});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
Intializes the database pool with options
await pool.init(POOL_OPTIONS);Gets an ODBCConnection from the pool and returns it for use
const connection = await pool.connect();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);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();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
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();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();
}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
});Closes the cursor
await cursor.close();A statement created from ODBCConnection.createStatement method. Allows preparing of a commonly used statement and binding parameters to it multiple times.
Prepares an SQL statement with or without parameters to bind to
const query = `SELECT * FROM "Table" WHERE "column" = ?`;
await statement.prepare(query);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"]);Executes the prepared and optionally bound statement
const result = await statement.execute();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();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:
stringthe statement executed - parameters:
ODBCQueryParametersThe 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:
unknownThe return value of some procedures. For many DBMS this will always be undefined - length:
numberthe length of the rows array
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
odbcpackage - name: the name of the error
- message: the message of the error
- stack: the error stack of the error
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));Defined the types available to supply to a query parameter array
Shorthand type for ODBCQueryParameter[]
Options to supply to a .query method on a ODBCPool or ODBCConnection
Options to set on an ODBCCursor
Options to set on a .callProcedure method on a ODBCConnection
Options to set on a .tables method on a ODBCConnection
Options to set on a .columns method on a ODBCConnection
Function type to supply to the resultPipes array of a ODBCConnectionModifier
Function type to supply to the queryPipes array of a ODBCConnectionModifier
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.
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.