/ 
Teradata-BTET-0.02
River stage zero No dependents
/ Teradata::BTET

Name

Teradata::BTET - Perl interface to Teradata in BTET mode

Synopsis

use Teradata::BTET;
use Teradata::BTET qw(:all);  # Exports variables
$dbh = Teradata::BTET::connect(logonstring);
$sth = $dbh->prepare($request [,n]);
$sth->open();
$sth->fetchrow_list();
$sth->close();
$dbh->disconnect;
# And others. See below.

Description

Teradata::BTET is a Perl interface to Teradata SQL. It does not attempt to be a complete interface to Teradata -- for instance, it does not allow multiple sessions, 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::BTET::connect LOGONSTRING

Connect to Teradata. The argument is a standard Teradata logon string in the form "[server/]user,password[,'account']". This method returns a connection handle that must be used for future prepare and disconnect requests. If the connection fails, undef will be returned. Only one connection can be active at a time.

disconnect

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

prepare REQUEST [N]

Connection method. Prepares a request for execution. The first argument is the SQL request to be prepared. It can be a multi-statement request, i.e. contain multiple statements separated by semicolons. The second argument (optional) is an integer identifying the request; it must be 0, 1, or 2. The default is 0. This allows you to have up to three requests active at one time, each with its own cursor.

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

The second argument will always be forced to 0, 1, or 2. Consider this example:

$st0 = $dbh->prepare("select * from dw.employees", 0);
$st1 = $dbh->prepare("select * from dw.departments", 1);
$st2 = $dbh->prepare("select * from dw.assets", 2);
$st9 = $dbh->prepare("select * from dw.liabilities", 9);

The last statement specifies 9, which is invalid. This value will be forced to 2, and $st9 will be identical to $st2.

The prepared statement can include parameter markers ('?' in the place of variables or literals).

execute [ARGS]

Statement method. Executes the prepared statement. If the statement 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 statement does not return data. If data is to be returned, use open instead.

open [ARGS]

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

fetchrow_list

Statement method. Fetches the next row from the open cursor in list form; e.g.:

@row = $sth->fetchrow_list();
fetchrow_hash

Statement method. Fetches the next row from the open cursor in hash form; e.g.:

%row = $sth->fetchrow_hash();

The hash entries are those specified by PREPARE ... USING NAMES.

close

Statement method. Closes the cursor.

begin_tran

Connection method. Issues BEGIN TRANSACTION. This statement cannot be run via prepare.

end_tran

Connection method. Issues END TRANSACTION. This statement cannot be run via prepare.

abort

Connection method. Issues ABORT. No arguments are allowed. This statement cannot be run via prepare.

Example

# Connect and get a database handle.
$dbh = Teradata::BTET::connect("dbc/user,password")
  or die "Could not connect";
# Prepare a statement; read the results.
$sth = $dbh->prepare("sel * from edw.employees");
$sth->open;
while (@emp_row = $sth->fetchrow_list) {
   print "employee data: @emp_row\n";
}
$sth->close;
$dbh->disconnect;  # Note: $dbh, not $sth.

For more examples, see test.pl.

Variables

$Teradata::BTET::activcount

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

$Teradata::BTET::errorcode

The Teradata error code (not the SQLCODE) from the last SQL operation. This variable can be exported.

$Teradata::BTET::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::BTET qw(:all);
$Teradata::BTET::msglevel

By default, Teradata::BTET 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 in 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 statement.

A word of caution is in order about decimal fields. Decimal fields 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 fields with a higher precision (10-18 digits) 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 fields, you can add 0 to them, but values with 16 or more significant digits will lose precision. You have been warned!

Arguments passed to Teradata via open and execute 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 fields. At present, the only workaround for this situation would be to code a request without parameter markers and hard-code the nulls to be of the type you want. In other words, instead of this:

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

you would code this:

$sth = $dbh->prepare("insert into funkytown values
   (1, 'James Brown', cast(null as timestamp(0)) )");
$sth->execute();

Limitations

The maximum length of a request to be prepared is 65400 bytes. The maximum length of data to be returned is 65000 bytes. The maximum number of fields selected or returned by any statement is 500. Likewise, you can pass no more than 500 arguments to open or execute.

If these limitations are too strict, you can ask your Perl administrator to change their values in the module's header file and recompile the module.

The following Teradata features are not supported:

Partitions other than DBC/SQL (e.g. MONITOR or MLOAD)
Multiple sessions
Asynchronous requests
LOB data types
CHECKPOINT
DESCRIBE
ECHO
EXECUTE IMMEDIATE
POSITION
REWIND

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

Author

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