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]

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'.

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.

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.

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;

Example

  # Connect and get a database handle.
  $dbh = Teradata::SQL::connect("dbc/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.

$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 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 request.

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 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 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:

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

you would code this:

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

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 fields 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
   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, B035-2418-115A (Nov. 2005).

Author

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