Teradata::SQL - Perl interface to Teradata SQL
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.
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.
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.
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.
Connection method. Disconnects from Teradata. This method must be applied to an active connection handle.
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.
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.
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).
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.
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.
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().
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".
Request method. Closes the cursor. This should always be called after opening and fetching the results.
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;
# 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.
Activity count, i.e. the number of rows affected by the last SQL operation. This variable can be exported to your namespace.
The Teradata error code from the last SQL operation. This variable can be exported.
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);
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.
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();
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!
Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems, B035-2418-115A (Nov. 2005).
Geoffrey Rommel, GROMMEL [at] cpan [dot] org.
To install Teradata::SQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Teradata::SQL
CPAN shell
perl -MCPAN -e shell install Teradata::SQL
For more information on module installation, please visit the detailed CPAN module installation guide.