node-oracledb

4. Connection Class

A Connection object is obtained by a Pool class getConnection() or Oracledb class getConnection() call.

The connection is used to access an Oracle database.

4.1 Connection Properties

The properties of a Connection object are listed below.

4.1.1 action

writeonly String action

The action attribute for end-to-end application tracing.

This is a write-only property. Displaying a Connection object will
show a value of null for this attribute. See End-to-end Tracing, Mid-tier Authentication, and Auditing.

4.1.2 clientId

writeonly String clientId

The client identifier for end-to-end application tracing, use with mid-tier authentication, and with Virtual Private Databases.

This is a write-only property. Displaying a Connection object will show a value of null for this attribute. See End-to-end Tracing, Mid-tier Authentication, and Auditing.

4.1.3 module

writeonly String module

The module attribute for end-to-end application tracing.

This is a write-only property. Displaying a Connection object will show a value of null for this attribute. See End-to-end Tracing, Mid-tier Authentication, and Auditing.

4.1.4 oracleServerVersion

readonly Number oracleServerVersion

This readonly property gives a numeric representation of the Oracle database version.
For version a.b.c.d.e, this property gives the number: (100000000 * a) + (1000000 * b) + (10000 * c) + (100 * d) + e

4.1.5 stmtCacheSize

readonly Number stmtCacheSize

The number of statements to be cached in the statement cache of the connection. The default value is the stmtCacheSize property in effect in the Pool object when the connection is created in the pool.

4.2 Connection Methods

4.2.1 break()

Prototype

Callback:

break(function(Error error){});

Promise:

promise = break();

Description

This call stops the currently running operation on the connection.

If there is no operation in progress or the operation has completed by the time the break is issued, the break() is effectively a no-op.

If the running asynchronous operation is interrupted, its callback will return an error.

Parameters

function(Error error)

The parameters of the callback function are:

4.2.2 close()

Prototype

Callback:

close(function(Error error){});

Promise:

promise = close();

Description

Releases a connection. If the connection was obtained from the pool, the connection is returned to the pool and is available for reuse.

Note: calling close() when connections are no longer required is strongly encouraged. Releasing helps avoid resource leakage and can improve system efficiency.

When a connection is released, any ongoing transaction on the connection is rolled back.

After releasing a connection to a pool, there is no guarantee a subsequent getConnection() call gets back the same database connection. The application must redo any ALTER SESSION statements on the new connection object, as required.

Parameters

function(Error error)

The parameters of the callback function are:

4.2.3 commit()

Prototype

Callback:

commit(function(Error error){});

Promise:

promise = commit();

Description

This call commits the current transaction in progress on the connection.

Parameters

function(Error error)

The parameters of the callback function are:

4.2.4 execute()

Prototype

Callback:

execute(String sql, [Object bindParams, [Object options,]] function(Error error, [Object result]){});

Promise:

promise = execute(String sql, [Object bindParams, [Object options]]);

Description

This call executes a SQL or PL/SQL statement. See SQL Execution for examples.

The statement to be executed may contain IN binds, OUT or IN OUT bind values or variables, which are bound using either an object or an array.

A callback function returns a result object, containing any fetched rows, the values of any OUT and IN OUT bind variables, and the number of rows affected by the execution of DML statements.

Parameters

The parameters are discussed in the next sections.

4.2.4.1 execute(): SQL Statement

String sql

The SQL or PL/SQL statement that execute() executes. The statement
may contain bind variables.

4.2.4.2 execute(): Bind Parameters

Object bindParams

This execute() function parameter is needed if there are bind variables in the statement, or if options are used. It can be either an object that associates values or JavaScript variables to the statement's bind variables by name, or an array of values or JavaScript variables that associate to the statement's bind variables by their relative positions. See
Bind Parameters for Prepared Statements for more details on binding.

If a bind value is an object it may have the following properties:

The maximum size of a BUFFER type is 2000 bytes, unless you are using Oracle Database 12c and the database initialization parameter MAX_STRING_SIZE has a value of EXTENDED. In this case the maximum size of a BUFFER is 32767.

With OUT binds, where the type cannot be inferred by node-oracledb because there is no input data value, the type defaults to STRING whenever type is not specified.

Note CURSOR bind variables can only be used for PL/SQL OUT binds.

See Bind Parameters for Prepared Statements for usage and examples.

4.2.4.3 execute(): Options

Object options

This is an optional parameter to execute() that may be used to control statement execution.

If there are no bind variables in the SQL statement, then a null bindParams, for example {}, must be specified before options otherwise you will get an error like ORA-01036: Illegal variable name/number or NJS-012: encountered invalid bind datatype.

The following properties can be set or overridden for the execution of a statement.

4.2.4.3.1 autoCommit

Boolean autoCommit

Overrides Oracledb autoCommit.

4.2.4.3.2 extendedMetaData

Boolean extendedMetaData

Overrides Oracledb extendedMetaData.

4.2.4.3.3 fetchInfo

Object fetchInfo

Object defining how query column data should be represented in JavaScript.

The fetchInfo property can be used to indicate that number or date columns in a query should be returned as strings instead of their native format. The property can be used in conjunction with, or instead of, the global setting fetchAsString.

For example:

fetchInfo:
{
  "HIRE_DATE":      { type : oracledb.STRING },  // return the date as a string
  "COMMISSION_PCT": { type : oracledb.DEFAULT }  // override Oracledb.fetchAsString
}

Each column is specified by name, using Oracle's standard naming convention.

The valid values for type are STRING and DEFAULT. The former indicates that the given column should be returned as a string. The latter can be used to override any global mapping given by fetchAsString and allow the column data for this query to be returned in native format.

The maximum length of a string created by type mapping is 200 bytes. However, if a database column that is already of type STRING is specified in fetchInfo, then the actual database metadata will be used to determine the maximum length.

Columns fetched from REF CURSORS are not mapped by fetchInfo settings in the execute() call. Use the global fetchAsString instead.

See Result Type Mapping for more information on query type mapping.

4.2.4.3.4 maxRows

Number maxRows

Overrides Oracledb maxRows.

4.2.4.3.5 outFormat

String outFormat

Overrides Oracledb outFormat.

4.2.4.3.6 prefetchRows

Number prefetchRows

Overrides Oracledb prefetchRows.

4.2.4.3.7 resultSet

Boolean resultSet

Determines whether query results should be returned as a ResultSet object or directly. The default is false.

4.2.4.4 execute(): Callback Function

function(Error error, [Object result])

The parameters of the execute() callback function are:

Result Object Properties

The properties of result object from the execute() callback are described below.

4.2.4.4.1 metaData

Array metaData

For SELECT statements, this contains an array of objects describing details of columns for the select list. For non queries, this property is undefined.

Each column's name is always given. If the Oracledb extendedMetaData or execute() option extendedMetaData are true then additional information is included.

• name: The column name follows Oracle's standard name-casing rules. It will commonly be uppercase, since most applications create tables using unquoted, case-insensitive names.
• fetchType: one of the Node-oracledb Type Constant values.
• dbType: one of the Oracle Database Type Constant values.
• byteSize: the database byte size. This is only set for DB_TYPE_VARCHAR, DB_TYPE_CHAR and DB_TYPE_RAW column types.
• precision: set only for DB_TYPE_NUMBER, DB_TYPE_TIMESTAMP, DB_TYPE_TIMESTAMP_TZ and DB_TYPE_TIMESTAMP_LTZ columns.
• scale: set only for DB_TYPE_NUMBER columns.
• nullable: indicates whether NULL values are permitted for this column.

For numeric columns: when precision is 0, then the column is simply a NUMBER. If precision is nonzero and scale is -127, then the column is a FLOAT. Otherwise, it is a NUMBER(precision, scale).

Metadata for Result Sets and REF CURSORS is available in a ResultSet property. For Lobs, a Lob type property also indicates whether the object is a BLOB or CLOB.

See Query Column Metadata for examples.

4.2.4.4.2 outBinds

Array/object outBinds

This is either an array or an object containing OUT and IN OUT bind values. If bindParams is passed as an array, then outBinds is returned as an array. If bindParams is passed as an object, then outBinds is returned as an object.

4.2.4.4.3 resultSet

Object resultSet

For SELECT statements when the resultSet option is true, use the resultSet object to fetch rows. See ResultSet Class.

4.2.4.4.4 rows

Array rows

For SELECT statements where the resultSet option is false or unspecified, rows contains an array of fetched rows. It will be NULL if there is an error or the SQL statement was not a SELECT statement. By default, the rows are in an array of column value arrays, but this can be changed to arrays of objects by setting outFormat to OBJECT. If a single row is fetched, then rows is an array that contains one single row. The number of rows returned is limited to the maxRows configuration property of the Oracledb object, although this may be overridden in any execute() call.

4.2.4.4.5 rowsAffected

Number rowsAffected

For DML statements (including SELECT FOR UPDATE) this contains the number of rows affected, for example the number of rows inserted. For non-DML statements such as queries, or if no rows are affected, then rowsAffected will be zero.

4.2.5 queryStream()

Prototype

queryStream(String sql, [Object bindParams, [Object options]]);

Return Value

This method will return a Readable Stream for queries.

Description

This function provides query streaming support. The parameters are the same as execute() except a callback is not used. Instead this function returns a stream used to fetch data.

Each row is returned as a data event. Query metadata is available via a metadata event. The end event indicates the end of the query results.

Query results must be fetched to completion to avoid resource leaks.

The connection must remain open until the stream is completely read.

For tuning purposes the oracledb.maxRows property can be used to size an internal buffer used by queryStream(). Note it does not limit the number of rows returned by the stream. The oracledb.prefetchRows value will also affect performance.

See Streaming Query Results for more information.

Parameters

See execute().

4.2.6 release()

An alias for connection.close().

4.2.7 rollback()

Prototype

Callback:

rollback(function(Error error){});

Promise:

promise = rollback();

Description

This call rolls back the current transaction in progress on the connection.

Parameters

function(Error error)

The parameters of the callback function are: