teradata-consulting-services

This document is a high-level tutorial that describes how to connect to Teradata Database using ODBC Driver for Teradata from the Perl scripting language.

Scripting languages are quickly becoming a common language for the implementation in many areas, especially, where the development time is more critical than the execution time. Moreover, in the areas where the execution time is important, languages and environment are adjusted and optimized to boost performance. Scripting languages make possible a variety of different scenarios and configurations.

Increasingly, the languages themselves are used as a full basic instrumental platform. For example, many large commercial Internet applications are now developed with Perl, Python or PHP.

This document is designed to demonstrate the ease with which applications written in Perl can interact with the Teradata database. Along with the sample scripts for accessing data, the user is also presented with the detailed procedures of environment configuration on different platforms. 

When an application tries to connect to a data source using the connection methods of the ODBC API, the Driver Manager (DM) determines which driver is required and loads it into memory. The Driver Manager then simply takes any incoming function call from the application and calls the function of the same name in the driver. It also performs other functions such as error checking to ensure that function are called in the right order, arguments contain valid values and unloading the driver from memory.

The ODBC driver manager acts as the mediator between the application and the database thereby creating a layer of abstraction. It manages the interactions between application programs and drivers. It has the capability to manage multiple applications and multiple drivers simultaneously. 

There are different Driver Managers which can be used. Some of the most commonly used Driver Managers are:

The ODBC Driver for Teradata works with iODBC on Mac OS X and with the Microsoft ODBC Driver Manager on Windows OS. On all other supported platforms, it is shipped with DataDirect’s Driver Manager. 

The following terms are used in this document and may not be familiar to all readers:

Perl is a portable, interpreted programming language, ideally suited for many text processing applications. Like most high-level programming languages, Perl supports a structured programmatic design and offers a wealth of integrated features.

One such feature is Perl DBI. DBI is a standard database independent interface module for Perl programming language. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used. DBI is built based on a modular architecture – for each database, there is a driver that implements the specifics of work with a particular database. DBI allows Perl applications to connect to a database using a particular database driver – DBD module. DBD modules handle the complex details of interaction with various databases. For more information about Perl DBI please refer to [PERL DBI]. 

DBD::ODBC is the ODBC driver for DBI. It is used to connect to an ODBC based data sources. In order to verify that DBI and DBD::ODBC modules are properly installed and operational, execute the following script line:

The response should include DBD::ODBC module and its version similar to the following example:

The same approach is valid for UNIX like platforms as well. However, DBD::OBDC module included in standard distributions is usually built against iODBC driver manager [iODBC]. Since Teradata ODBC driver is distributed with DataDirect driver manager, it is required to replace DBD::ODBC module with one built against DataDirect driver manager. The following snippets illustrate how to rebuild and replace DBD::ODBC module in ActivePerl distribution. 

For more information on DataDirect driver manager please refer to [DD].

2.2 Rebuild DBD::ODBC on UNIX

In UNIX-like platforms, modify and rebuild DBD::ODBC:

cd /opt/ActivePerl-5.16/lib/auto/DBD
mkdir ODBC.original
mv ODBC/* ODBC.original
cd /tmp
tar -xvf DBD-ODBC-1.52.tar
cd DBD-ODBC-1.52

In order to build against DataDirect driver manager some changes in the building scripts are required. The makefile for the module is generated with Perl script in Makefile.PL file.  

Update the lines in Makefile.PL:

$myodbc = 'intersolve'
if !$myodbc && -f "$odbchome/include/qeodbc.h";

As follows:

$myodbc = 'intersolve'
if !$myodbc && -f "$odbchome/include/sqlunx.h";

Move the updated lines before this line:

($myodbc, $odbclibdir) = find_iodbc($odbchome) if !$myodbc;

In addition, replace the following block:

print {$sqlhfh} qq{#include <qeodbc.h>\n};
if (-f "$odbcincdir/sql.h") { print "You seem to have the official header files.\n"; $opts{INC} .= " -I$odbcincdir"; print {$sqlhfh} qq{#include <sql.h>\n#include <sqltypes.h>\n#include <sqlext.h>\n
};

With this update:

$opts{DEFINE} = "";
if (-f "$odbchome/include/sql.h") { print "You seem to have the official DataDirect header files.\n"; $opts{INC} .= " -I$odbchome/include"; print {$sqlhfh} qq{#include <sql.h>\n#include <sqltypes.h>\n#include <sqlext.h>\n#include <sqlucode.h>\n
};

Makefile.PL tries very hard to find any valid ODBC driver using the system tools odbc_config, and iodbc_config. We don’t want it to try too hard, we want to force it to use DirectData driver manager. Comment out the following lines by prepending with the comment ‘#’ character:

# # try and find unixODBC's odbc_config binary
# if (!$myodbc) {
# ($myodbc, $myodbc_version, $odbchome, $odbcincdir, $odbclibdir) =
# unixodbc_config($odbchome);
# }
# if (!$myodbc) {
# # try and find iODBC's iodbc_config binary
# ($myodbc, $myodbc_version, $odbchome,
# $odbcincdir, $odbclibdir) = iodbc_config($odbchome);
# }

Prepare the environment variables:

export ODBCHOME=/opt/teradata/client/15.10
export DBI_DSN=dbi:ODBC:Payroll
export DBI_USER=user
export DBI_PASS=password

(Note: The environment variable, ODBCHOME, tells Makefile.PL to look there for Teradata header files and DataDirect driver manager (libodbc.so)

 

Generate the makefile:

/opt/ActivePerl-5.16/bin/perl Makefile.PL

Finally, build and install:

/opt/ActivePerl-5.16/bin/perl Makefile.PL
Make
make test ### optional
make install

Check that /opt/ActivePerl-5.16/lib/auto/DBD/ has as a newly built ODBC.so library file. If not, manually copy /tmp/DBD-ODBC-1.52/blib/arch/auto/DBD/ODBC/ODBC.so to /opt/ActivePerl-5.16/lib/auto/DBD/.

2.3 Create Sample Data

This section examines the script that generates the sample data in Payroll DSN.

General DBI workflow of the interaction with the database is very similar to a generic ODBC application workflow, and can be described with the following steps:

  1. Connect to the database using connect() function;
  2. Prepare a request for execution with prepare() function;
  3. Run a query using execute() function;
  4. Retrieve data (for queries containing SQL select command) with fetch() function;
  5. Finalize the work with the database with finish() and disconnect() functions.

 

2.3.1 Connect

First, in order to connect to a database, DBI module needs to be loaded with use DBI directive.

use DBI;

Then, the connection is established with the connect() function. The first argument describes the data source. In this example, the connection is made to an ODBC data source, named Payroll.  Other two arguments hold user name and password. If the connection succeeds, DBI returns a database handle that represents a database connection. DBI->errstr returns the error, in case of connection failures.

my $connection = DBI->connect('dbi:ODBC:Payroll', '', '')
or die "Connect failed: " . DBI->errstr;

 

2.3.2 Execute SQL statement

The function do() is mainly used to carry out the statements that do not return data sets, such as insert, update and delete. The number of rows affected by the statement is returned as a result of execution. This function is used on the database handle. Here’s an example that drops employee table:

$connection->do('drop table employee');

To execute SQL statements in DBI, there are two methods – the use of chain prepare-execute; and the function do(), that was demonstrated earlier.

A set of functions prepare and execute usually used to execute select statements, which return data sets or parameterized queries. First, the statement descriptor is created with prepare() function. This descriptor can be used to execute the statements using execute() method. Those functions also can be used if the statement contains parameters:

my $statement = $connection->prepare("INSERT INTO employee (employee_number, last_name, first_name) VALUES (?,?,?)")
or die "Couldn't prepare statement: " . $connection->errstr;

The prepare() call prepares a query to be executed by the database. The argument for this function is a SQL statement string. Successful preparation returns a statement handle. In case of failure, $connection->errstr holds the error string. 

 

Once the statement was successfully prepared, it can be executed. execute() returns a true value if it succeeds and a false value otherwise. In case of failure, $statement->errstr  holds the details of the error. The example below illustrates execution of the prepared statement multiple times with different parameter values.

$statement->execute(2, 'Olson', 'Chuck')
or die "Couldn't execute statement: " . $statement->errstr; $statement->execute(3, 'Lee', 'Bill')
or die "Couldn't execute statement: " . $statement->errstr; $statement->execute(4, 'Chapman', 'Lisa')
or die "Couldn't execute statement: " . $statement->errstr; $statement->execute(1, 'Miller', 'Susan')
or die "Couldn't execute statement: " . $statement->errstr;

 

For non-select statements, execute() returns number of rows affected. If no rows were affected, a special value “0E0” is returned which Perl will treat as 0 but will regard as true. For select statement, successful execution simply returns a true value.  

 

2.3.3 Disconnect

Finally, the statement is closed with finish() function and the connection is closed with disconnect():

$statement->finish;
$connection->disconnect;

 

2.3.4 Complete Example

use strict;
use DBI; # open connection handle
my $connection = DBI->connect('dbi:ODBC:Payroll', '', '')
or die " Connect failed: " . DBI->errstr; # drop employee table
$connection->do('drop table employee'); # create employee table
$connection->do('CREATE SET TABLE employee (
employee_number INTEGER NOT NULL PRIMARY KEY,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL)')
or die "Execute failed: " . DBI->errstr; # populate employee table with sample data 
my $statement = $connection->prepare("INSERT INTO employee (employee_number, last_name, first_name) VALUES (?,?,?)")
or die "Prepare failed: " . $connection->errstr; $statement->execute(2, 'Olson', 'Chuck')
or die "Execute failed: " . $statement->errstr; $statement->execute(3, 'Lee', 'Bill')
or die "Execute failed: " . $statement->errstr; $statement->execute(4, 'Chapman', 'Lisa')
or die "Execute failed: " . $statement->errstr; $statement->execute(1, 'Miller', 'Susan')
or die "Execute failed: " . $statement->errstr; # clean up
$statement->finish;
$connection->disconnect;

 

2.4 Retrieve Data

To retrieve data the application uses one of the fetch() functions, provided by DBI, on the executed statement. 

 

2.4.1 Execute SQL statement

As previously explained, the prepare call prepares a query to be executed by the database.

my $statement = $connection->prepare('select first_name, last_name from employee')
or die " Prepare failed: " . $connection->errstr;

Then, the statement is executed:

$statement->execute()
or die "Execute failed: " . $statement->errstr;

 

2.4.2 Retrieve Data

fetchrow_array function returns one of the selected rows from the database. Each returned row is an array of cells that constitutes the result of executed SQL statement. The following snippet demonstrates how to retrieve and print all rows from employee table. 

my @row;
while (@row = $statement->fetchrow_array()) {
print "$row[0] $row[1]\n";
}

DBI also provides other methods to retrieve data using hash and references with fetchrow_arrayref() fetchrow_hashref()functions. For more information refer to [PERL DBI].

 

2.4.3 Disconnect

Finally, the statement is closed with finish function and the connection is closed with disconnect:

$statement->finish;
$connection->disconnect;

 

2.4.4 Complete Example

use strict;
use DBI; #open connection handle
my $connection = DBI->connect('dbi:ODBC:Payroll', '', '')
or die "Couldn't connect to database: " . DBI->errstr; # Print driver name and version.
my $driver_name = $connection->get_info( 6 );
print "SQL_DRIVER_NAME=$driver_name\n";
my $driver_ver = $connection->get_info( 7 );
print "SQL_DRIVER_VER=$driver_ver\n"; #prepare select statement
my $statement = $connection->prepare('select first_name, last_name from employee')
or die "Prepare failed: " . $connection->errstr; #execute the statement 
$statement->execute()
or die "Execute failed: " . $statement->errstr; #retrieve and print every row
my @row;
while (@row = $statement->fetchrow_array()) {
print "$row[0] $row[1]\n";
} #clean up
$statement->finish;
$connection->disconnect;

 

Similar Posts