@teradataprebuilt/januspreview
v20.0.0-a
Published
Teradata SQL Driver for Node.js
Downloads
13
Readme
Teradata SQL Driver for Node.js
This package enables Node.js applications to connect to the Teradata Database.
This package closely implements the PEP-249 Python Database API Specification 2.0.
This package requires Node.js 12.x or later, and runs on Windows, macOS, and Linux. 32-bit Node.js is not supported.
:information_source: Please note Node.js versions 14 and 16 are not currently supported for Linux and macOS. There is an issue in Node.js that affects
node-ffi-napi
library that the driver depends on: node-ffi-napi/ref-napi#54. Newer versions of Node.js fixed the issue.
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 2023 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 Globals
- 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
The Teradata SQL Driver for Node.js is a DBAPI Driver that enables Node.js applications to connect to the Teradata Database. The driver closely implements the PEP-249 Python Database API Specification 2.0.
The driver is a young product that offers a basic feature set. We are working diligently to add features to the driver, and our goal is feature parity with the Teradata JDBC Driver.
At the present time, the driver offers the following features.
- Supported for use with Teradata Database 16.10 and later releases.
- COP Discovery.
- Laddered Concurrent Connect.
- HTTPS/TLS connections with Teradata SQL Engine 16.20.53.30 and later.
- Encrypted logon using the
TD2
,JWT
,LDAP
,KRB5
(Kerberos), orTDNEGO
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.
- 1 MB rows supported with Teradata Database 16.0 and later.
- 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
, andDATASET 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
andREPLACE PROCEDURE
commands.- Stored Procedure Dynamic Result Sets.
- FastLoad and FastExport.
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.
- Monitor partition support is not available yet.
Installation
The driver depends on the ffi-napi
, ref-napi
, ref-array-di
packages which is available from npmjs.com.
Use npm install teradatasql
to download and install the driver and its dependencies automatically.
When upgrading to a new version of the driver, you may need to use pip install's --force
option to force the download of the new version.
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.
Program | Purpose
--------------------------------------------------------------------------------------------------------------------------------- | ---
AGKRBatchInsert.ts | Demonstrates how to insert a batch of rows with Auto-Generated Key Retrieval (AGKR)
AGKRInsertSelect.ts | Demonstrates Insert/Select with Auto-Generated Key Retrieval (AGKR)
BatchInsert.ts | Demonstrates how to insert a batch of rows
BatchInsertCSV.ts | Demonstrates how to insert a batch of rows from a CSV file
BatchInsPerf.ts | Measures time to insert one million rows
CharPadding.ts | Demonstrates the database's Character Export Width behavior
CommitRollback.ts | Demonstrates commit and rollback methods with auto-commit off.
DecimalDigits.ts | Demonstrates how to format decimal.Decimal values.
DriverDatabaseVersion.ts | Displays the driver version and database version
ElicitFile.ts | Demonstrates C source file upload to create a User-Defined Function (UDF)
ExportCSVResult.ts | Demonstrates how to export a query result set to a CSV file
ExportCSVResults.ts | Demonstrates how to export multiple query result sets to CSV files
FakeExportCSVResults.ts | Demonstrates how to export multiple query result sets with the metadata to CSV files
FakeResultSetCon.ts | Demonstrates connection parameter for fake result sets
FakeResultSetEsc.ts | Demonstrates escape function for fake result sets
FastExportCSV.ts | Demonstrates how to FastExport rows from a table to a CSV file
FastExportTable.ts | Demonstrates how to FastExport rows from a table
FastLoadBatch.ts | Demonstrates how to FastLoad batches of rows
FastLoadCSV.ts | Demonstrates how to FastLoad batches of rows from a CSV file
HelpSession.ts | Displays session information
IgnoreErrors.ts | Demonstrates how to ignore errors
InsertLob.ts | Demonstrates how to insert BLOB and CLOB values
InsertXML.ts | Demonstrates how to insert and retrieve XML values
LoadCSVFile.ts | Demonstrates how to load data from a CSV file into a table
LobLocators.ts | Demonstrates how to use LOB locators
MetadataFromPrepare.ts | Demonstrates how to prepare a SQL request and obtain SQL statement metadata
ParamDataTypes.ts | Demonstrates how to specify data types for parameter marker bind values
ShowCommand.ts | Displays the results from the SHOW
command
StoredProc.ts | Demonstrates how to create and call a SQL stored procedure
TJEncryptPassword.ts | Creates encrypted password files
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.
Parameter | Default | Type | Description
----------------------- | ----------- | -------------- | ---
account
| | string | Specifies the database account. Equivalent to the Teradata JDBC Driver ACCOUNT
connection parameter.
browser
| | string | Specifies the command to open the browser for Browser Authentication, when logmech
is BROWSER
. Browser Authentication is supported for Windows and macOS. Equivalent to the Teradata JDBC Driver BROWSER
connection parameter.The specified command must include a placeholder token, literally specified as PLACEHOLDER
, which the driver will replace with the Identity Provider authorization endpoint URL. The PLACEHOLDER
token is case-sensitive and must be specified in uppercase.• On Windows, the default command is cmd /c start "title" "PLACEHOLDER"
. Windows command syntax requires the quoted title to precede the quoted URL.• On macOS, the default command is open PLACEHOLDER
. macOS command syntax does not allow the URL to be quoted.
browser_tab_timeout
| "5"
| quoted integer | Specifies the number of seconds to wait before closing the browser tab after Browser Authentication is completed. The default is 5 seconds. The behavior is under the browser's control, and not all browsers support automatic closing of browser tabs. Typically, the tab used to log on will remain open indefinitely, but the second and subsequent tabs will be automatically closed. Specify 0
(zero) to close the tab immediately. Specify -1
to turn off automatic closing of browser tabs. Browser Authentication is supported for Windows and macOS. Equivalent to the Teradata JDBC Driver BROWSER_TAB_TIMEOUT
connection parameter.
browser_timeout
| "180"
| quoted integer | Specifies the number of seconds that the driver will wait for Browser Authentication to complete. The default is 180 seconds (3 minutes). Browser Authentication is supported for Windows and macOS. Equivalent to the Teradata JDBC Driver BROWSER_TIMEOUT
connection parameter.
column_name
| "false"
| quoted boolean | Controls the behavior of cursor .description
sequence name
items. Equivalent to the Teradata JDBC Driver COLUMN_NAME
connection parameter. False specifies that a cursor .description
sequence name
item provides the AS-clause name if available, or the column name if available, or the column title. True specifies that a cursor .description
sequence name
item provides the column name if available, but has no effect when StatementInfo parcel support is unavailable.
connect_failure_ttl
| "0"
| quoted integer | Specifies the time-to-live in seconds to remember the most recent connection failure for each IP address/port combination. The driver subsequently skips connection attempts to that IP address/port for the duration of the time-to-live. The default value of zero disables this feature. The recommended value is half the database restart time. Equivalent to the Teradata JDBC Driver CONNECT_FAILURE_TTL
connection parameter.
connect_function
| "0"
| quoted integer | Specifies whether the database should allocate a Logon Sequence Number (LSN) for this session, or associate this session with an existing LSN. Specify 0
for a session with no LSN (the default). Specify 1
to allocate a new LSN for the session. Specify 2
to associate the session with the existing LSN identified by the logon_sequence_number
connection parameter. The database only permits sessions for the same user to share an LSN. Equivalent to the Teradata JDBC Driver CONNECT_FUNCTION
connection parameter.
connect_timeout
| "10000"
| quoted integer | Specifies the timeout in milliseconds for establishing a TCP socket connection. Specify 0
for no timeout. The default is 10 seconds (10000 milliseconds).
cop
| "true"
| quoted boolean | Specifies whether COP Discovery is performed. Equivalent to the Teradata JDBC Driver COP
connection parameter.
coplast
| "false"
| quoted boolean | Specifies how COP Discovery determines the last COP hostname. Equivalent to the Teradata JDBC Driver COPLAST
connection parameter. When coplast
is false
or omitted, or COP Discovery is turned off, then no DNS lookup occurs for the coplast hostname. When coplast
is true
, and COP Discovery is turned on, then a DNS lookup occurs for a coplast hostname.
database
| | string | Specifies the initial database to use after logon, instead of the user's default database. Equivalent to the Teradata JDBC Driver DATABASE
connection parameter.
dbs_port
| "1025"
| quoted integer | Specifies the database port number. Equivalent to the Teradata JDBC Driver DBS_PORT
connection parameter.
encryptdata
| "false"
| quoted boolean | Controls encryption of data exchanged between the driver and the database. Equivalent to the Teradata JDBC Driver ENCRYPTDATA
connection parameter.
error_query_count
| "21"
| quoted integer | Specifies how many times the driver will attempt to query FastLoad Error Table 1 after a FastLoad operation. Equivalent to the Teradata JDBC Driver ERROR_QUERY_COUNT
connection parameter.
error_query_interval
| "500"
| quoted integer | Specifies how many milliseconds the driver will wait between attempts to query FastLoad Error Table 1. Equivalent to the Teradata JDBC Driver ERROR_QUERY_INTERVAL
connection parameter.
error_table_1_suffix
| "_ERR_1"
| string | Specifies the suffix for the name of FastLoad Error Table 1. Equivalent to the Teradata JDBC Driver ERROR_TABLE_1_SUFFIX
connection parameter.
error_table_2_suffix
| "_ERR_2"
| string | Specifies the suffix for the name of FastLoad Error Table 2. Equivalent to the Teradata JDBC Driver ERROR_TABLE_2_SUFFIX
connection parameter.
error_table_database
| | string | Specifies the database name for the FastLoad error tables. By default, FastLoad error tables reside in the same database as the destination table being loaded. Equivalent to the Teradata JDBC Driver ERROR_TABLE_DATABASE
connection parameter.
fake_result_sets
| "false"
| quoted boolean | Controls whether a fake result set containing statement metadata precedes each real result set.
field_quote
| "\""
| string | Specifies a single character string used to quote fields in a CSV file.
field_sep
| ","
| string | Specifies a single character string used to separate fields in a CSV file. Equivalent to the Teradata JDBC Driver FIELD_SEP
connection parameter.
govern
| "true"
| quoted boolean | Controls FastLoad and FastExport throttling by Teradata workload management rules. When set to true
(the default), workload management rules may delay a FastLoad or FastExport. When set to false
, workload management rules will reject rather than delay a FastLoad or FastExport. Equivalent to the Teradata JDBC Driver GOVERN
connection parameter.
host
| | string | Specifies the database hostname.
https_port
| "443"
| quoted integer | Specifies the database port number for HTTPS/TLS connections. Equivalent to the Teradata JDBC Driver HTTPS_PORT
connection parameter.
lob_support
| "true"
| quoted boolean | Controls LOB support. Equivalent to the Teradata JDBC Driver LOB_SUPPORT
connection parameter.
log
| "0"
| quoted integer | Controls debug logging. Somewhat equivalent to the Teradata JDBC Driver LOG
connection parameter. This parameter's behavior is subject to change in the future. This parameter's value is currently defined as an integer in which the 1-bit governs function and method tracing, the 2-bit governs debug logging, the 4-bit governs transmit and receive message hex dumps, and the 8-bit governs timing. Compose the value by adding together 1, 2, 4, and/or 8.
logdata
| | string | Specifies extra data for the chosen logon authentication method. Equivalent to the Teradata JDBC Driver LOGDATA
connection parameter.
logmech
| "TD2"
| string | Specifies the logon authentication method. Equivalent to the Teradata JDBC Driver LOGMECH
connection parameter. Possible case-insensitive values are TD2
(the default), BROWSER
, JWT
, LDAP
, KRB5
for Kerberos, or TDNEGO
. Browser Authentication is supported for Windows and macOS.
logon_sequence_number
| | quoted integer | Associates this session with an existing Logon Sequence Number (LSN) when connect_function
is 2
. The database only permits sessions for the same user to share an LSN. An LSN groups multiple sessions together for workload management. Using an LSN is a three-step process. First, establish a control session with connect_function
as 1
, which allocates a new LSN. Second, obtain the LSN from the control session using the escape function {fn teradata_logon_sequence_number}
. Third, establish an associated session with connect_function
as 2
and the logon sequence number. Equivalent to the Teradata JDBC Driver LOGON_SEQUENCE_NUMBER
connection parameter.
logon_timeout
| "0"
| quoted integer | Specifies the logon timeout in seconds. Zero means no timeout.
manage_error_tables
| "true"
| quoted boolean | Controls whether the driver manages the FastLoad error tables.
max_message_body
| "2097000"
| quoted integer | Specifies the maximum Response Message size in bytes. Equivalent to the Teradata JDBC Driver MAX_MESSAGE_BODY
connection parameter.
oidc_scope
| "openid"
| string | Specifies the OpenID Connect (OIDC) scope to use for Browser Authentication. Beginning with Teradata Database 17.20.03.11, the default scope can be specified in the database's TdgssUserConfigFile.xml
file, using the IdPConfig
element's Scope
attribute. Browser Authentication is supported for Windows and macOS. Equivalent to the Teradata JDBC Driver OIDC_SCOPE
connection parameter.
oidc_token
| "access_token"
| string | Specifies the kind of OIDC token to use for Browser Authentication. Specify id_token
to use the id_token instead of the access_token. Browser Authentication is supported for Windows and macOS. Equivalent to the Teradata JDBC Driver OIDC_TOKEN
connection parameter.
partition
| "DBC/SQL"
| string | Specifies the database partition. Equivalent to the Teradata JDBC Driver PARTITION
connection parameter.
password
| | string | Specifies the database password. Equivalent to the Teradata JDBC Driver PASSWORD
connection parameter.
request_timeout
| "0"
| quoted integer | Specifies the timeout for executing each SQL request. Zero means no timeout.
runstartup
| "false"
| quoted boolean | Controls whether the user's STARTUP
SQL request is executed after logon. For more information, refer to User STARTUP SQL Request. Equivalent to the Teradata JDBC Driver RUNSTARTUP
connection parameter.
sessions
| | quoted integer | Specifies the number of data transfer connections for FastLoad or FastExport. The default (recommended) lets the database choose the appropriate number of connections. Equivalent to the Teradata JDBC Driver SESSIONS
connection parameter.
sip_support
| "true"
| quoted boolean | Controls whether StatementInfo parcel is used. Equivalent to the Teradata JDBC Driver SIP_SUPPORT
connection parameter.
sp_spl
| "true"
| quoted boolean | Controls whether stored procedure source code is saved in the database when a SQL stored procedure is created. Equivalent to the Teradata JDBC Driver SP_SPL
connection parameter.
sslca
| | string | Specifies the file name of a PEM file that contains Certificate Authority (CA) certificates for use with sslmode
values VERIFY-CA
or VERIFY-FULL
. Equivalent to the Teradata JDBC Driver SSLCA
connection parameter.
sslcapath
| | string | Specifies a directory of PEM files that contain Certificate Authority (CA) certificates for use with sslmode
values VERIFY-CA
or VERIFY-FULL
. Only files with an extension of .pem
are used. Other files in the specified directory are not used. Equivalent to the Teradata JDBC Driver SSLCAPATH
connection parameter.
sslcipher
| | string | Specifies the TLS cipher for HTTPS/TLS connections. Equivalent to the Teradata JDBC Driver SSLCIPHER
connection parameter.
sslcrc
| "ALLOW"
| string | Controls TLS certificate revocation checking for HTTPS/TLS connections when sslmode
is VERIFY-FULL
. This parameter is ignored unless sslmode
is VERIFY-FULL
. Equivalent to the Teradata JDBC Driver SSLCRC
connection parameter. Values are case-insensitive.• ALLOW
provides "soft fail" behavior such that communication failures are ignored during certificate revocation checking.• REQUIRE
mandates that certificate revocation checking must succeed.
sslmode
| "PREFER"
| string | Specifies the mode for connections to the database. Equivalent to the Teradata JDBC Driver SSLMODE
connection parameter. Values are case-insensitive.• DISABLE
disables HTTPS/TLS connections and uses only non-TLS connections.• ALLOW
uses non-TLS connections unless the database requires HTTPS/TLS connections.• PREFER
uses HTTPS/TLS connections unless the database does not offer HTTPS/TLS connections.• REQUIRE
uses only HTTPS/TLS connections.• VERIFY-CA
uses only HTTPS/TLS connections and verifies that the server certificate is valid and trusted.• VERIFY-FULL
uses only HTTPS/TLS connections, verifies that the server certificate is valid and trusted, and verifies that the server certificate matches the database hostname.
sslprotocol
| "TLSv1.2"
| string | Specifies the TLS protocol for HTTPS/TLS connections. Equivalent to the Teradata JDBC Driver SSLPROTOCOL
connection parameter.
teradata_values
| "true"
| quoted boolean | Controls whether str
or a more specific JavaScript data type is used for certain result set column value types. Refer to the Data Types table below for details.
tmode
| "DEFAULT"
| string | Specifies the transaction mode. Equivalent to the Teradata JDBC Driver TMODE
connection parameter. Possible values are DEFAULT
(the default), ANSI
, or TERA
.
user
| | string | Specifies the database username. Equivalent to the Teradata JDBC Driver USER
connection parameter.
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
isfalse
or omitted, or COP Discovery is turned off, then the driver will not perform a DNS lookup for the coplast hostname. - When
coplast
istrue
, 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".
| No domain name qualification | With domain name qualification(Recommended)
------ | ---------------------------- | ---
Application-specifieddatabase hostname | whomooz
| whomooz.domain.com
Default: COP Discovery turned on, and coplast
is false
or omitted,perform DNS lookups until unknown COP hostname is encountered | whomoozcop1
→10.0.0.1
whomoozcop2
→10.0.0.2
whomoozcop3
→10.0.0.3
whomoozcop4
→undefined | whomoozcop1.domain.com
→10.0.0.1
whomoozcop2.domain.com
→10.0.0.2
whomoozcop3.domain.com
→10.0.0.3
whomoozcop4.domain.com
→undefined
COP Discovery turned on, and coplast
is true
,perform DNS lookups until COP hostname is found whose IP address matches the coplast hostname, or unknown COP hostname is encountered | whomoozcoplast
→10.0.0.3
whomoozcop1
→10.0.0.1
whomoozcop2
→10.0.0.2
whomoozcop3
→10.0.0.3
| whomoozcoplast.domain.com
→10.0.0.3
whomoozcop1.domain.com
→10.0.0.1
whomoozcop2.domain.com
→10.0.0.2
whomoozcop3.domain.com
→10.0.0.3
COP Discovery turned off and round-robin DNS,perform one DNS lookup that returns multiple IP addresses | whomooz
→10.0.0.1
, 10.0.0.2
, 10.0.0.3
| whomooz.domain.com
→10.0.0.1
, 10.0.0.2
, 10.0.0.3
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 EncryptPassword.js
TJEncryptPassword.js
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:
Argument | Example | Description
----------------------------- | -------------------- | ---
Transformation | AES/CBC/NoPadding
| Specifies the transformation in the form Algorithm/
Mode/
Padding. Supported transformations are listed in a table below.
KeySizeInBits | 256
| Specifies the algorithm key size, which governs the encryption strength.
MAC | HmacSHA256
| Specifies the message authentication code (MAC) algorithm HmacSHA1
or HmacSHA256
.
PasswordEncryptionKeyFileName | PassKey.properties
| Specifies a filename in the current directory, a relative pathname, or an absolute pathname. The file is created by this program. If the file already exists, it will be overwritten by the new file.
EncryptedPasswordFileName | EncPass.properties
| Specifies a filename in the current directory, a relative pathname, or an absolute pathname. The filename or pathname that must differ from the PasswordEncryptionKeyFileName. The file is created by this program. If the file already exists, it will be overwritten by the new file.
Hostname | whomooz
| Specifies the database hostname.
Username | guest
| Specifies the database username.
Password | please
| Specifies the database password to be encrypted. Unicode characters in the password can be specified with the \u
XXXX escape sequence.
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.js
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.
node TJEncryptPassword.js 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.js 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:
Property | Description
------------------------------------------------- | ---
version=1
| The version number must be 1
. This property is required.
transformation=
Algorithm/
Mode/
Padding | Specifies the transformation in the form Algorithm/
Mode/
Padding. Supported transformations are listed in a table below. This property is required.
algorithm=
Algorithm | This value must correspond to the Algorithm portion of the transformation. This property is required.
match=
MatchValue | The password encryption key and encrypted password files must contain the same match value. 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. This property is required.
key=
HexDigits | This value is the password encryption key, encoded as hex digits. This property is required.
mac=
MACAlgorithm | Specifies the message authentication code (MAC) algorithm HmacSHA1
or HmacSHA256
. Stored Password Protection performs Encrypt-then-MAC for protection from a padding oracle attack. This property is required.
mackey=
HexDigits | This value is the MAC key, encoded as hex digits. This property is required.
The TJEncryptPassword.js 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:
Property | Description
------------------------------------------------- | ---
version=1
| The version number must be 1
. This property is required.
match=
MatchValue | The password encryption key and encrypted password files must contain the same match value. 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. This property is required.
password=
HexDigits | This value is the encrypted password, encoded as hex digits. This property is required.
params=
HexDigits | This value contains the cipher algorithm parameters, if any, encoded as hex digits. Some ciphers need algorithm parameters that cannot be derived from the key, such as an initialization vector. This property is optional, depending on whether the cipher algorithm has associated parameters.
hash=
HexDigits | This value is the expected message authentication code (MAC), encoded as hex digits. After encryption, the expected MAC is calculated using the ciphertext, transformation name, and algorithm parameters if any. 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. If the calculated MAC differs from the expected MAC, then either or both of the files may have been tampered with. This property is required.
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 DES or AES, followed by a feedback mode and padding scheme.
The driver supports the following transformations and key sizes.
Transformation | Key Size
--------------------------- | ---
DES/CBC/NoPadding
| 64
DES/CBC/PKCS5Padding
| 64
DES/CFB/NoPadding
| 64
DES/CFB/PKCS5Padding
| 64
DES/OFB/NoPadding
| 64
DES/OFB/PKCS5Padding
| 64
DESede/CBC/NoPadding
| 192
DESede/CBC/PKCS5Padding
| 192
DESede/CFB/NoPadding
| 192
DESede/CFB/PKCS5Padding
| 192
DESede/OFB/NoPadding
| 192
DESede/OFB/PKCS5Padding
| 192
AES/CBC/NoPadding
| 128
AES/CBC/NoPadding
| 192
AES/CBC/NoPadding
| 256
AES/CBC/PKCS5Padding
| 128
AES/CBC/PKCS5Padding
| 192
AES/CBC/PKCS5Padding
| 256
AES/CFB/NoPadding
| 128
AES/CFB/NoPadding
| 192
AES/CFB/NoPadding
| 256
AES/CFB/PKCS5Padding
| 128
AES/CFB/PKCS5Padding
| 192
AES/CFB/PKCS5Padding
| 256
AES/OFB/NoPadding
| 128
AES/OFB/NoPadding
| 192
AES/OFB/NoPadding
| 256
AES/OFB/PKCS5Padding
| 128
AES/OFB/PKCS5Padding
| 192
AES/OFB/PKCS5Padding
| 256
Stored Password Protection uses a symmetric encryption algorithm such as DES or 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 algorithmsHmacSHA1
andHmacSHA256
are supported. - The driver does not support block ciphers used as byte-oriented ciphers via modes such as
CFB8
orOFB8
.
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.
- DESede uses a 192-bit (24 byte) key. The driver does not support a 128-bit (16 byte) key for DESede.
- DES uses a 64-bit (8 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
andHmacSHA256
. - 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.
Client Attribute | Source | Description
--------------------------- | -------- | ---
MechanismName
| database | The connection's logon mechanism; for example, TD2, LDAP, etc.
ClientIpAddress
| database | The client IP address, as determined by the database
ClientTcpPortNumber
| database | The connection's client TCP port number, as determined by the database
ClientIPAddrByClient
| driver | The client IP address, as determined by the driver
ClientPortByClient
| driver | The connection's client TCP port number, as determined by the driver
ClientProgramName
| driver | The client program name, followed by a streamlined call stack
ClientSystemUserId
| driver | The client user name
ClientOsName
| driver | The client operating system name
ClientProcThreadId
| driver | The client process ID
ClientVmName
| driver | Node.js runtime information
ClientTdHostName
| driver | The database hostname as specified by the application, without any COP suffix
ClientCOPSuffixedHostName
| driver | The COP-suffixed database hostname chosen by the driver
ServerIPAddrByClient
| driver | The database node's IP address, as determined by the driver
ServerPortByClient
| driver | The destination port number of the TCP connection to the database node, as determined by the driver
ClientConfType
| driver | The confidentiality type, as determined by the driverV
- TLS used for encryption, with full certificate verificationC
- TLS used for encryption, with Certificate Authority (CA) verificationR
- TLS used for encryption, with no certificate verificationE
- TLS was not attempted, and TDGSS used for encryptionU
- TLS was not attempted, and TDGSS encryption depends on central administrationF
- TLS was attempted, but the TLS handshake failed, so this is a fallback to using TDGSS for encryptionH
- SSLMODE was set to PREFER, but a non-TLS connection was made, and TDGSS encryption depends on central administration
ServerConfType
| database | The confidentiality type, as determined by the databaseT
- TLS used for encryptionE
- TDGSS used for encryptionU
- Data transfer is unencrypted
ClientConfVersion
| database | The TLS version as determined by the database, if this is an HTTPS/TLS connection
ClientConfCipherSuite
| database | The TLS cipher as determined by the database, if this is an HTTPS/TLS connection
ClientAttributesEx
| driver | Additional Client Attributes are available in this column as a list of name=value pairs, each terminated by a semicolon. Individual values can be accessed using the NVP
system function.NODEJS
- The Node.js versionGO
- The Go versionSCS
- The session character setCCS
- The client character setLOB
- Y/N indicator for LOB supportSIP
- Y/N indicator for StatementInfo parcel supportTM
- The transaction mode indicator A (ANSI) or T (TERA)ENC
- Y/N indicator for encryptdata
connection parameterDP
- The dbs_port
connection parameterHP
- The https_port
connection parameterSSL
- Numeric level corresponding to sslmode
SSLM
- The sslmode
connection parameterCERT
- The TLS certificate statusBA
- Y/N indicator for Browser AuthenticationThe CERT
attribute indicates the TLS certificate status for an HTTPS/TLS connection. When the CERT
attribute indicates the TLS certificate is valid (V
) or invalid (I
), then additional TLS certificate status details are provided as a series of comma-separated two-letter codes.U
- the TLS certificate status is unavailableV
- the TLS certificate status is validI
- the TLS certificate status is invalidPU
- sslca PEM file is unavailable for server certificate verificationPA
- server certificate was verified using sslca PEM filePR
- server certificate was rejected using sslca PEM fileDU
- sslcapath PEM directory is unavailable for server certificate verificationDA
- server certificate was verified using sslcapath PEM directoryDR
- server certificate was rejected using sslcapath PEM directorySA
- server certificate was verified by the systemSR
- server certificate was rejected by the systemCY
- server certificate passed VERIFY-CA checkCN
- server certificate failed VERIFY-CA checkHU
- server hostname is unavailable for server certificate matching, because database IP address was specifiedHY
- server hostname matches server certificateHN
- server hostname does not match server certificateRU
- resolved server hostname is unavailable for server certificate matching, because database IP address was specifiedRY
- resolved server hostname matches server certificateRN
- resolved server hostname does not match server certificateIY
- IP address matches server certificateIN
- IP address does not match server certificateFY
- server certificate passed VERIFY-FULL checkFN
- server certificate failed VERIFY-FULL check
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.
Field | Source | Description
----- | -------- | ---
1 | database | The string (TCP/IP)
to indicate the connection type
2 | database | The connection's client TCP port number, in hexadecimal
3 | database | The client IP address, as determined by the database
4 | driver | The database hostname as specified by the application, without any COP suffix
5 | driver | The client process ID
6 | driver | The client user name
7 | driver | The client program name
8 | driver | The string 01 LSS
to indicate the LogonSource
string version 01
User STARTUP SQL Request
CREATE USER
and MODIFY USER
commands provide STARTUP
clauses for specifying SQL commands to establish initial session settings. The following table lists several of the SQL commands that may be used to establish initial session settings.
Category | SQL command
------------------------ | ---
Diagnostic settings | DIAGNOSTIC
... FOR SESSION
Session query band | SET QUERY_BAND
... FOR SESSION
Unicode Pass Through | SET SESSION CHARACTER SET UNICODE PASS THROUGH ON
Transaction isolation | SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
Collation sequence | SET SESSION COLLATION
Temporal qualifier | SET SESSION CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME
Date format | SET SESSION DATEFORM
Function tracing | SET SESSION FUNCTION TRACE
Session time zone | SET TIME ZONE
For example, the following command sets a STARTUP
SQL request for user susan
to establish read-uncommitted transaction isolation after logon.
MODIFY USER susan AS STARTUP='SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL RU'
The driver's runstartup
connection parameter must be true
to execute the user's STARTUP
SQL request after logon. The default for runstartup
is false
. If the runstartup
connection parameter is omitted or false
, then the user's STARTUP
SQL request will not be executed.
Transaction Mode
The tmode
connection parameter enables an application to specify the transaction mode for the connection.
"tmode":"ANSI"
provides American National Standards Institute (ANSI) transaction semantics. This mode is recommended."tmode":"TERA"
provides legacy Teradata transaction semantics. This mode is only recommended for legacy applications that require Teradata transaction semantics."tmode":"DEFAULT"
provides the default transaction mode configured for the database, which may be either ANSI or TERA mode."tmode":"DEFAULT"
is the default when thetmode
connection parameter is omitted.
While ANSI mode is generally recommended, please note that every application is different, and some applications may need to use TERA mode. The following differences between ANSI and TERA mode might affect a typical user or application:
- Silent truncation of inserted data occurs in TERA mode, but not ANSI mode. In ANSI mode, the database returns an error instead of truncating data.
- Tables created in ANSI mode are
MULTISET
by default. Tables created in TERA mode areSET
tables by default. - For tables created in ANSI mode, character columns are
CASESPECIFIC
by default. For tables created in TERA mode, character columns areNOT CASESPECIFIC
by default. - In ANSI mode, character literals are
CASESPECIFIC
. In TERA mode, character literals areNOT CASESPECIFIC
.
The last two behavior differences, taken together, may cause character data comparisons (such as in WHERE
clause conditions) to be case-insensitive in TERA mode, but case-sensitive in ANSI mode. This, in turn, can produce different query results in ANSI mode versus TERA mode. Comparing two NOT CASESPECIFIC
expressions is case-insensitive regardless of mode, and comparing a CASESPECIFIC
expression to another expression of any kind is case-sensitive regardless of mode. You may explicitly CAST
an expression to be CASESPECIFIC
or NOT CASESPECIFIC
to obtain the character data comparison required by your application.
The Teradata Reference / SQL Request and Transaction Processing recommends that ANSI mode be used for all new applications. The primary benefit of using ANSI mode is that inadvertent data truncation is avoided. In contrast, when using TERA mode, silent data truncation can occur when data is inserted, because silent data truncation is a feature of TERA mode.
A drawback of using ANSI mode is that you can only call stored procedures that were created using ANSI mode, and you cannot call stored procedures that were created using TERA mode. It may not be possible to switch over to ANSI mode exclusively, because you may have some legacy applications that require TERA mode to work properly. You can work around this drawback by creating your stored procedures twice, in two different users/databases, once using ANSI mode, and once using TERA mode.
Refer to the Teradata Reference / SQL Request and Transaction Processing for complete information regarding the differences between ANSI and TERA transaction modes.
Auto-Commit
The driver provides auto-commit on and off functionality for both ANSI and TERA mode.
When a connection is first established, it begins with the default auto-commit setting, which is on. When auto-commit is on, the driver is solely responsible for managing transactions, and the driver commits each SQL request that is successfully executed. An application should not execute any transaction management SQL commands when auto-commit is on. An application should not call the commit
method or the rollback
method when auto-commit is on.
An application can manage transactions itself by setting the connection's .autocommit
attribute to false
to turn off auto-commit.
con.autocommit = false
When auto-commit is off, the driver leaves the current transaction open after each SQL request is executed, and the application is responsible for committing or rolling back the transaction by calling the commit
or the rollback
method, respectively.
Auto-commit remains turned off until the application turns it back on by setting the connection's .autocommit
attribute to True
.
con.autocommit = true
Best practices recommend that an application avoid executing database-vendor-specifi