teradatasql v20.0.12

Teradata SQL Driver for Node.js

This package enables Node.js applications to connect to the Teradata Database.

This package requires 64-bit Node.js 12 or later, and runs on Windows, macOS, and Linux. 32-bit Node.js is not supported.

Please note that Node.js versions 14 and 16 are not currently supported for Linux and macOS. An issue (ref-napi#54) in Node.js versions 14 and 16 affects the node-ffi-napi library that the driver depends on. This issue is fixed in later versions of Node.js.

For community support, please visit Teradata Community.

For Teradata customer support, please visit Teradata Customer Service.

Please note, this driver may contain beta/preview features ("Beta Features"). As such, by downloading and/or using the driver, in addition to agreeing to the licensing terms below, you acknowledge that the Beta Features are experimental in nature and that the Beta Features are provided "AS IS" and may not be functional on any machine or in any environment.

Copyright 2024 Teradata. All Rights Reserved.

Table of Contents

• Features
• Limitations
• Installation
• License
• Documentation
• Sample Programs
• Using the Driver
• Connection Parameters
• COP Discovery
• Stored Password Protection
• Client Attributes
• User STARTUP SQL Request
• Transaction Mode
• Auto-Commit
• Data Types
• Null Values
• Undefined Values
• Character Export Width
• Module Constructors
• Module Exceptions
• Connection Attributes
• Connection Methods
• Cursor Attributes
• Cursor Methods
• Type Objects
• Escape Syntax
• FastLoad
• FastExport
• CSV Batch Inserts
• CSV Export Results
• Change Log

Features

At the present time, the driver offers the following features.

• Supported for use with Teradata database 16.20 and later releases.
• COP Discovery.
• Laddered Concurrent Connect.
• HTTPS/TLS connections with Teradata database 16.20.53.30 and later.
• Encrypted logon using the TD2, JWT, LDAP, KRB5 (Kerberos), or TDNEGO logon mechanisms.
• Data encryption governed by central administration, or enabled via the encryptdata connection parameter.
• Unicode character data transferred via the UTF8 session character set.
• Auto-commit for ANSI and TERA transaction modes.
• Result set row size up to 1 MB.
• Multi-statement requests that return multiple result sets.
• Most JDBC escape syntax.
• Parameterized SQL requests with question-mark parameter markers.
• Parameterized batch SQL requests with multiple rows of data bound to question-mark parameter markers.
• Auto-Generated Key Retrieval (AGKR) for identity column values and more.
• Large Object (LOB) support for the BLOB and CLOB data types.
• Complex data types such as XML, JSON, DATASET STORAGE FORMAT AVRO, and DATASET STORAGE FORMAT CSV.
• ElicitFile protocol support for DDL commands that create external UDFs or stored procedures and upload a file from client to database.
• CREATE PROCEDURE and REPLACE PROCEDURE commands.
• Stored Procedure Dynamic Result Sets.
• FastLoad and FastExport.
• Monitor partition.

Limitations

• The UTF8 session character set is always used. The charset connection parameter is not supported.
• No support yet for Recoverable Network Protocol and Redrive.

Installation

The driver depends on the ffi-napi, ref-napi, ref-array-di packages which are available from npmjs.com.

Use npm install teradatasql to download and install the driver and its dependencies automatically.

License

Use of the driver is governed by the License Agreement for the Teradata SQL Driver for Node.js.

When the driver is installed, the LICENSE and THIRDPARTYLICENSE files are placed in the teradatasql directory under your node_modules installation directory.

In addition to the license terms, the driver may contain beta/preview features ("Beta Features"). As such, by downloading and/or using the driver, in addition to the licensing terms, you acknowledge that the Beta Features are experimental in nature and that the Beta Features are provided "AS IS" and may not be functional on any machine or in any environment.

Documentation

When the driver is installed, the README.md file is placed in the teradatasql directory under your node_modules installation directory. This permits you to view the documentation offline, when you are not connected to the Internet.

The README.md file is a plain text file containing the documentation for the driver. While the file can be viewed with any text file viewer or editor, your viewing experience will be best with an editor that understands Markdown format.

Sample Programs

Sample programs are provided to demonstrate how to use the driver. When the driver is installed, the sample programs are placed in the teradatasql/samples directory under your node_modules installation directory.

The sample programs are coded with a fake database hostname whomooz, username guest, and password please. Substitute your actual database hostname and credentials before running a sample program.

Using the Driver

Example usage of establishing a connection:

    const teradatasql = require("teradatasql");

    const con = teradatasql.connect({
        host: 'whomooz',
        user: 'guest',
        password: 'please'
    });

You may specify connection parameters as a JavaScript object, as a JSON string, or using a combination of the two approaches. The TeradataConnection.connect function's first argument is a JavaScript object. The TeradataConnection.connect function's second arguments is an optional JSON string.

Connection parameters specified only as a JavaScript object:

con = teradataConnection.connect({host:"whomooz",user:"guest",password:"please"})

Connection parameters specified as a JSON string:

con = teradataConnection.connect({}, '{"host":"whomooz", "user":"guest", "password":"please"}')

Connection parameters specified using a combination:

con = teradataConnection.connect({host:"whomooz"}, '{"user":"guest", "password":"please"}')

When a combination of parameters are specified, connection parameters specified as a JSON string take precedence over same-named connection parameters specified in the JavaScript object.

Connection Parameters

The following table lists the connection parameters currently offered by the driver. Connection parameter values are case-sensitive unless stated otherwise.

Our goal is consistency for the connection parameters offered by this driver and the Teradata JDBC Driver, with respect to connection parameter names and functionality. For comparison, Teradata JDBC Driver connection parameters are documented here.

COP Discovery

The driver provides Communications Processor (COP) discovery behavior when the cop connection parameter is true or omitted. COP Discovery is turned off when the cop connection parameter is false.

A database system can be composed of multiple database nodes. One or more of the database nodes can be configured to run the database Gateway process. Each database node that runs the database Gateway process is termed a Communications Processor, or COP. COP Discovery refers to the procedure of identifying all the available COP hostnames and their IP addresses. COP hostnames can be defined in DNS, or can be defined in the client system's hosts file. Teradata strongly recommends that COP hostnames be defined in DNS, rather than the client system's hosts file. Defining COP hostnames in DNS provides centralized administration, and enables centralized changes to COP hostnames if and when the database is reconfigured.

The coplast connection parameter specifies how COP Discovery determines the last COP hostname.

• When coplast is false or omitted, or COP Discovery is turned off, then the driver will not perform a DNS lookup for the coplast hostname.
• When coplast is true, and COP Discovery is turned on, then the driver will first perform a DNS lookup for a coplast hostname to obtain the IP address of the last COP hostname before performing COP Discovery. Subsequently, during COP Discovery, the driver will stop searching for COP hostnames when either an unknown COP hostname is encountered, or a COP hostname is encountered whose IP address matches the IP address of the coplast hostname.

Specifying coplast as true can improve performance with DNS that is slow to respond for DNS lookup failures, and is necessary for DNS that never returns a DNS lookup failure.

When performing COP Discovery, the driver starts with cop1, which is appended to the database hostname, and then proceeds with cop2, cop3, ..., copN. The driver supports domain-name qualification for COP Discovery and the coplast hostname. Domain-name qualification is recommended, because it can improve performance by avoiding unnecessary DNS lookups for DNS search suffixes.

The following table illustrates the DNS lookups performed for a hypothetical three-node database system named "whomooz".

Round-robin DNS rotates the list of IP addresses automatically to provide load distribution. Round-robin is only possible with DNS, not with the client system hosts file.

The driver supports the definition of multiple IP addresses for COP hostnames and non-COP hostnames.

For the first connection to a particular database system, the driver generates a random number to index into the list of COPs. For each subsequent connection, the driver increments the saved index until it wraps around to the first position. This behavior provides load distribution across all discovered COPs.

The driver masks connection failures to down COPs, thereby hiding most connection failures from the client application. An exception is thrown to the application only when all the COPs are down for that database. If a COP is down, the next COP in the sequence (including a wrap-around to the first COP) receives extra connections that were originally destined for the down COP. When multiple IP addresses are defined in DNS for a COP, the driver will attempt to connect to each of the COP's IP addresses, and the COP is considered down only when connection attempts fail to all of the COP's IP addresses.

If COP Discovery is turned off, or no COP hostnames are defined in DNS, the driver connects directly to the hostname specified in the host connection parameter. This permits load distribution schemes other than the COP Discovery approach. For example, round-robin DNS or a TCP/IP load distribution product can be used. COP Discovery takes precedence over simple database hostname lookup. To use an alternative load distribution scheme, either ensure that no COP hostnames are defined in DNS, or turn off COP Discovery with cop as false.

Stored Password Protection

Overview

Stored Password Protection enables an application to provide a connection password in encrypted form to the driver.

An encrypted password may be specified in the following contexts:

• A login password specified as the password connection parameter.
• A login password specified within the logdata connection parameter.

If the password, however specified, begins with the prefix ENCRYPTED_PASSWORD( then the specified password must follow this format:

ENCRYPTED_PASSWORD(file:PasswordEncryptionKeyFileName,file:EncryptedPasswordFileName)

Each filename must be preceded by the file: prefix. The PasswordEncryptionKeyFileName must be separated from the EncryptedPasswordFileName by a single comma.

The PasswordEncryptionKeyFileName specifies the name of a file that contains the password encryption key and associated information. The EncryptedPasswordFileName specifies the name of a file that contains the encrypted password and associated information. The two files are described below.

Stored Password Protection is offered by this driver, the Teradata JDBC Driver, and the Teradata SQL Driver for R. These drivers use the same file format.

Program TJEncryptPassword.ts

TJEncryptPassword.ts is a sample program to create encrypted password files for use with Stored Password Protection. When the driver is installed, the sample programs are placed in the teradatasql/samples directory under your node_modules installation directory.

This program works in conjunction with Stored Password Protection offered by the driver. This program creates the files containing the password encryption key and encrypted password, which can be subsequently specified via the ENCRYPTED_PASSWORD( syntax.

You are not required to use this program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files. You may also use the TJEncryptPassword.java sample program that is available with the Teradata JDBC Driver Reference. The only requirement is that the files must match the format expected by the driver, which is documented below.

This program encrypts the password and then immediately decrypts the password, in order to verify that the password can be successfully decrypted. This program mimics the password decryption of the driver, and is intended to openly illustrate its operation and enable scrutiny by the community.

The encrypted password is only as safe as the two files. You are responsible for restricting access to the files containing the password encryption key and encrypted password. If an attacker obtains both files, the password can be decrypted. The operating system file permissions for the two files should be as limited and restrictive as possible, to ensure that only the intended operating system userid has access to the files.

The two files can be kept on separate physical volumes, to reduce the risk that both files might be lost at the same time. If either or both of the files are located on a network volume, then an encrypted wire protocol can be used to access the network volume, such as sshfs, encrypted NFSv4, or encrypted SMB 3.0.

This program accepts eight command-line arguments:

Example Commands

The TJEncryptPassword program uses the driver to log on to the specified database using the encrypted password, so the driver must have been installed with the npm install teradatasql command.

The following commands assume that the TJEncryptPassword.ts program file is located in the current directory. When the driver is installed, the sample programs are placed in the teradatasql/samples directory under your node_modules installation directory. Change your current directory to the teradatasql/samples directory under your node_modules installation directory.

The following example commands illustrate using a 256-bit AES key, and using the HmacSHA256 algorithm.

npx ts-node TJEncryptPassword.ts AES/CBC/NoPadding 256 HmacSHA256 PassKey.properties EncPass.properties whomooz guest please

Password Encryption Key File Format

You are not required to use the TJEncryptPassword.ts program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files, but the files must match the format expected by the driver.

The password encryption key file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

The TJEncryptPassword.ts program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

Encrypted Password File Format

The encrypted password file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

While params is technically optional, an initialization vector is required by all three block cipher modes CBC, CFB, and OFB that are supported by the driver. ECB (Electronic Codebook) does not require params, but ECB is not supported by the driver.

Transformation, Key Size, and MAC

A transformation is a string that describes the set of operations to be performed on the given input, to produce transformed output. A transformation specifies the name of a cryptographic algorithm such as AES, followed by a feedback mode and padding scheme.

The driver supports the following transformations and key sizes.

Stored Password Protection uses a symmetric encryption algorithm such as AES, in which the same secret key is used for encryption and decryption of the password. Stored Password Protection does not use an asymmetric encryption algorithm such as RSA, with separate public and private keys.

CBC (Cipher Block Chaining) is a block cipher encryption mode. With CBC, each ciphertext block is dependent on all plaintext blocks processed up to that point. CBC is suitable for encrypting data whose total byte count exceeds the algorithm's block size, and is therefore suitable for use with Stored Password Protection.

Stored Password Protection hides the password length in the encrypted password file by extending the length of the UTF8-encoded password with trailing null bytes. The length is extended to the next 512-byte boundary.

• A block cipher with no padding, such as AES/CBC/NoPadding, may only be used to encrypt data whose byte count after extension is a multiple of the algorithm's block size. The 512-byte boundary is compatible with many block ciphers. AES, for example, has a block size of 128 bits (16 bytes), and is therefore compatible with the 512-byte boundary.
• A block cipher with padding, such as AES/CBC/PKCS5Padding, can be used to encrypt data of any length. However, CBC with padding is vulnerable to a "padding oracle attack", so Stored Password Protection performs Encrypt-then-MAC for protection from a padding oracle attack. MAC algorithms HmacSHA1 and HmacSHA256 are supported.
• The driver does not support block ciphers used as byte-oriented ciphers via modes such as CFB8 or OFB8.

The strength of the encryption depends on your choice of cipher algorithm and key size.

• AES uses a 128-bit (16 byte), 192-bit (24 byte), or 256-bit (32 byte) key.

Sharing Files with the Teradata JDBC Driver

This driver and the Teradata JDBC Driver can share the files containing the password encryption key and encrypted password, if you use a transformation, key size, and MAC algorithm that is supported by both drivers.

• Recommended choices for compatibility are AES/CBC/NoPadding and HmacSHA256.
• Use a 256-bit key if your Java environment has the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files from Oracle.
• Use a 128-bit key if your Java environment does not have the Unlimited Strength Jurisdiction Policy Files.
• Use HmacSHA1 for compatibility with JDK 1.4.2.

File Locations

For the ENCRYPTED_PASSWORD( syntax of the driver, each filename must be preceded by the file: prefix. The PasswordEncryptionKeyFileName must be separated from the EncryptedPasswordFileName by a single comma. The files can be located in the current directory, specified with a relative path, or specified with an absolute path.

Example for files in the current directory:

ENCRYPTED_PASSWORD(file:JohnDoeKey.properties,file:JohnDoePass.properties)

Example with relative paths:

ENCRYPTED_PASSWORD(file:../dir1/JohnDoeKey.properties,file:../dir2/JohnDoePass.properties)

Example with absolute paths on Windows:

ENCRYPTED_PASSWORD(file:c:/dir1/JohnDoeKey.properties,file:c:/dir2/JohnDoePass.properties)

Example with absolute paths on Linux:

ENCRYPTED_PASSWORD(file:/dir1/JohnDoeKey.properties,file:/dir2/JohnDoePass.properties)

Processing Sequence

The two filenames specified for an encrypted password must be accessible to the driver and must conform to the properties file formats described above. The driver raises an exception if the file is not accessible, or the file does not conform to the required file format.

The driver verifies that the match values in the two files are present, and match each other. The driver raises an exception if the match values differ from each other. The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors. The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

Before decryption, the driver calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. The driver raises an exception if the calculated MAC differs from the expected MAC, to indicate that either or both of the files may have been tampered with.

Finally, the driver uses the decrypted password to log on to the database.

Client Attributes

Client Attributes record a variety of information about the client system and client software in the system tables DBC.SessionTbl and DBC.EventLog. Client Attributes are intended to be a replacement for the information recorded in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog.

The Client Attributes are recorded at session logon time. Subsequently, the system views DBC.SessionInfoV and DBC.LogOnOffV can be queried to obtain information about the client system and client software on a per-session basis. Client Attribute values may be recorded in the database in either mixed-case or in uppercase, depending on the session character set and other factors. Analysis of recorded Client Attributes must flexibly accommodate either mixed-case or uppercase values.

Warning: The information in this section is subject to change in future releases of the driver. Client Attributes can be "mined" for information about client system demographics; however, any applications that parse Client Attribute values must be changed if Client Attribute formats are changed in the future.

Client Attributes are not intended to be used for workload management. Instead, query bands are intended for workload management. Any use of Client Attributes for workload management may break if Client Attributes are changed, or augmented, in the future.

LogonSource Column

The LogonSource column is obsolete and has been superseded by Client Attributes. The LogonSource column may be deprecated and subsequently removed in future releases of the database.

When the driver establishes a connection to the database, the driver composes a string value that is stored in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog. The LogonSource column is included in system views such as DBC.SessionInfoV and DBC.LogOnOffV. All LogonSource values are recorded in the database in uppercase.

The driver follows the format documented in the Teradata Data Dictionary, section "System Views Columns Reference", for network-attached LogonSource values. Network-attached LogonSource values have eight fields, separated by whitespace. The database composes fields 1 through 3, and the driver composes fields 4 through 8.

User