/ 
Teradata-SQL-0.14
River stage zero No dependents
/ Teradata::SQL

Name

Teradata::SQL - Perl interface to Teradata SQL

Synopsis

use Teradata::SQL;
use Teradata::SQL qw(:all);  # Exports variables
$dbh = Teradata::SQL::connect(logonstring [,tranmode]);
$dbh->execute($request);
$rh = $dbh->open($request);
$rh->fetchrow_list();
$rh->close();
$dbh->disconnect;
# And others. See below.

Description

Teradata::SQL is a Perl interface to Teradata SQL. It does not attempt to be a complete interface to Teradata -- for instance, it does not allow asynchronous requests or PM/API connections -- but it should be sufficient for many applications.

Methods

This is an object-oriented module; no methods are exported by default. The connect method must be called with its full name; other methods are called with object handles.

Most methods return a true value when they succeed and FALSE upon failure. The fetch methods, however, return the data to be fetched. If there is no row to be fetched, they return an empty list.

Teradata::SQL::connect LOGONSTRING [CHARSET] [TRANMODE] [HASH]

Connects to Teradata. The first argument is a standard Teradata logon string in the form "[server/]user,password[,'account']". The second argument (optional) is the client character set for the session, 'ASCII' by default. The most common character sets besides ASCII are 'UTF8' and 'UTF16'. The third argument (optional) is the session transaction mode, either 'BTET' (the default) or 'ANSI'.

The fourth argument is a hash that can contain additional parameters. The only parameter currently supported is 'logmech', for specifying a logon mechanism. Example:

$dbh = Teradata::SQL::connect($TDLOGON, 'ASCII','BTET',
 {'logmech'=>'ldap'} )   or die "Could not connect";

This method returns a connection handle that must be used for future requests. If the connection fails, undef will be returned. Many connections (sessions) can be active at a time.

disconnect

Connection method. Disconnects from Teradata. This method must be applied to an active connection handle.

execute REQUEST

Connection method. Executes a single request without input variables. The argument is the SQL request to be run. It can be a multi-statement request, i.e. contain multiple statements separated by semicolons.

This method should be used only when the request does not return data. If data is to be returned, use open instead.

open REQUEST

Connection method. Opens a request for execution. The argument is the SQL request to be prepared. It can be a multi-statement request, i.e. contain multiple statements separated by semicolons. The WITH clause (to add subtotals and totals) is not supported.

You can have as many requests open at a time as you wish, but be aware that each one allocates additional memory.

The request cannot include parameter markers ('?' in the place of variables or literals). If you need parameter markers, use prepare instead.

open returns a request handle or, if the open fails, undef.

An open/fetch/close sequence fetches all the rows from a request. If there are multiple statements in the request, it will fetch all rows from all result sets. In this case, the activity count will show the number of rows in each result set, not in all of them combined. Its value will change at the beginning of each result set (see "Variables").

After fetching all the rows, be sure to close() the cursor.

prepare REQUEST

Connection method. Opens a request for execution. The arguments are the same as for open, and prepare also returns a request handle or, if the prepare fails, undef. The difference is that a prepared request can include parameter markers ('?' in the place of variables or literals).

executep [ARGS]

Request method. Executes the prepared request. If the request includes parameter markers, arguments can be supplied to take the place of the markers. For more information, see "Data Types".

This method should be used only when the request does not return data. If data is to be returned, use openp instead.

openp [ARGS]

Request method. Executes the prepared request and opens a cursor to contain the results. If the request includes parameter markers, arguments can be supplied to take the place of the markers.

After fetching all the rows, be sure to close() the cursor.

fetchrow_list

Request method. Returns the next row from the open cursor in list form, or an empty list if no more rows are available; e.g.:

@row = $rh->fetchrow_list();

This works with cursors opened by open() or by openp().

fetchrow_hash

Request method. Returns the next row from the open cursor in hash form, or an empty hash if no more rows are available; e.g.:

%row = $rh->fetchrow_hash();

This works with cursors opened by open() or by openp(). The hash entries are those specified by ColumnName, not ColumnTitle. See the CLIv2 Reference, s.v. "PrepInfo Parcel".

close

Request method. Closes the cursor. This should always be called after opening and fetching the results.

openseg REQUEST [SAVE_SPL]

Connection method. Opens a request for execution in segments. This method can be used only for compiling stored procedures. Currently it is limited to only one segment, so the entire text of the procedure must be passed at once. Be sure that the length of the text is within the limit returned by QEPIDMSS (item 11; see "server_info").

The optional second argument is 'Y' if you wish to save the stored procedure text in the database, 'N' otherwise. The default is 'Y'.

The same rules apply as for open(). If the request succeeds, no rows will be returned, but if it fails error messages will be returned. You should therefore run fetchrow_list() and close() as usual after issuing the request.

dec_digits N

Connection method. Sets the maximum number of decimal digits to be returned to N. This is similar to the DECIMALDIGITS command in BTEQ. See, however, the section "Data Types" for notes about large decimal values.

abort

Connection method. Aborts the currently active request for the session. Note that this is an asynchronous ABORT (like the .ABORT command in BTEQ), not a ROLLBACK. Ordinarily it would have to be called from a signal handler; for example:

sub abort_req {
 $dbh->abort;
 print "Request has been aborted.\n";
 $dbh->disconnect;
 exit;
}
$SIG{'INT'} = \&abort_req;

Function

server_info TDP ITEM

This is an ordinary function, not a method. It is independent of connections and requests. It can be exported into your namespace.

This is a partial implementation of the DBCHQE call, which queries various attributes of the server. You need not be connected to the server to run this function. The first argument is the server name or "TDP ID"; for instance, for "mrbigcop1", it would be "mrbig". The second argument is the number of the item to be queried; these numbers are given in the CLIv2 manual. The one of most interest is probably 34 (QEPIDBR), which returns the DBS release and version information as a 2-element list.

Items that require a connection (session) ID are not implemented, and not all session-independent items are implemented. If an item is not implemented, it will return undef.

Example

# Connect and get a database handle.
$dbh = Teradata::SQL::connect("mrbig/user,password")
  or die "Could not connect";
# Prepare a request; read the results.
$rh = $dbh->open("sel * from edw.employees");
while (@emp_row = $rh->fetchrow_list) {
   print "employee data: @emp_row\n";
}
$rh->close;
#
# Prepare, then insert some rows.
$rh = $dbh->prepare("insert into edw.departments (?,?,?,?)");
while (<DATA>) {
   chomp;  @incoming = split;
   $rh->executep(@incoming);
}
# All finished.
$dbh->disconnect;  # Note: $dbh, not $rh.

For more examples, see test.pl.

Variables

$Teradata::SQL::activcount

Activity count, i.e. the number of rows affected by the last SQL operation. This variable can be exported to your namespace.

If a multi-statement request has been processed, the activity count will take on multiple values, one for each statement. These values can be seen as each row is fetched from the result sets.

$Teradata::SQL::errorcode

The Teradata error code from the last SQL operation. This variable can be exported.

$Teradata::SQL::errormsg

The Teradata error message from the last SQL operation. This variable can be exported.

These three variables can be exported to your namespace all at once by this means:

use Teradata::SQL qw(:all);
$Teradata::SQL::msglevel

By default, Teradata::SQL will display error codes and messages from Teradata on stderr. Setting this variable to 0 will suppress these messages. The default value is 1. The module will honor changes to the value of this variable at any point during your program.

Data Types

Perl uses only three data types: integers, double-precision floating point, and byte strings. The data returned from Teradata will be converted to one of these types and will look like ordinary Perl values.

Dates are returned in either integer form (e.g., 1020815 for 15 August 2002) or ANSI character form (e.g., '2002-08-15'), depending on the default for your system, the session characteristics, and whether you have issued a SET SESSION DATEFORM request. If you want dates returned in some other form, you must explicitly cast them, e.g. like this:

cast(cast(sale_dt as format 'MM/DD/YYYY') as char(10))

By default, times and timestamps are returned as character strings in their default formats. Again, you can cast them as you wish in your select request.

A word of caution is in order about decimal columns, bigints, and number columns. Decimal columns with a precision of 9 or lower will be converted to doubles (numeric) and will behave more or less as expected, with the usual caveats about floating-point arithmetic. Decimal columns with a higher precision (10-18 digits), as well as bigints, will be converted to character strings. This has the advantage of preserving their full precision, but it means that Perl will not treat them as numeric. To convert them to numeric columns, you can add 0 to them, but values with 16 or more significant digits will lose precision. You have been warned!

Decimal columns of more than 18 digits are not supported. If they are returned from the database, the module will issue a warning and substitute a 0 in their place. (This warning will not appear if msglevel is 0.) You should instead ask the database to convert them to strings, e.g. like this:

cast(cast(large_dec as format '-(30)9.99') as varchar(40))

Similar cautions apply to NUMBER columns: those with mantissas of 1-56 bits will be converted to numeric, those with mantissas of 64 bits will be converted to character strings, and larger numbers are not supported.

Arguments passed to Teradata via openp and executep will be passed in Perl internal form (integer, double, or byte string). You can pass undefs to become nulls in the database, but there are limitations. Since all undefs look the same to the module, it coerces them all to integers. This works for most data types, but Teradata will not allow integer nulls to be placed in BYTE, TIME, or TIMESTAMP columns. At present, the only workaround for this situation would be to eschew parameter markers for the nulls and hard-code the nulls to be of the type you want. In other words, instead of this:

$rh = $dbh->prepare("insert into funkytown values (?,?,?)");
$rh->executep(1, "James Brown", undef);

you could code this:

$rh = $dbh->prepare("insert into funkytown values (?,?,
  cast(null as timestamp(0)))");
$rh->executep(1, "James Brown");

Limitations

The maximum length of a request to be prepared is 64 Kbytes. The maximum length of data to be returned is 65400 bytes. These limits cannot be relaxed without rewriting the module.

The maximum number of columns selected or returned by any request is 520. Likewise, you can pass no more than 520 arguments to openp or executep. If these limitations are too strict, you can ask your Perl administrator to change the value of MAX_FIELDS in the module's header file and recompile the module.

Multiple sessions are supported. This feature would be most useful when connecting to multiple servers; multiple sessions on a single server are of little use without support for asynchronous requests.

CLI applications can use a different client character set for each request, but this module sets it only at the session level.

The following Teradata features are not supported:

Partitions other than DBC/SQL (e.g. MONITOR or MLOAD)
Asynchronous requests
WITH clause
LOB data types
Arrays
CHECKPOINT
DESCRIBE
ECHO
POSITION
REWIND

If you would like some features added, write to the author at the address shown below. No guarantees!

Reference

Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems, Release 13, B035-2418-088A (Aug. 2008).

Author

Geoffrey Rommel, GROMMEL [at] cpan [dot] org.