Perl with Teradata ODBC

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

1 Introduction


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. 

1.1 Driver Manager

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:

unixODBC DM: an open source Driver Manager for various platforms

iODBC DM: an open source Driver Manager shipped with Mac OS X

Microsoft ODBC DM: Driver Manager shipped with Microsoft Windows OS 

DataDirect DM: Driver Manager built by DataDirect 

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. 

1.2 References

[ACTIVE PERL] – ActivePerl distribution www.activestate.com/activeperl
[DD] – DataDirect www.datadirect.com
[ODBC] Microsoft ODBC Specification http://msdn.microsoft.com/en-us/library/ms710252%28VS.85%29.aspx
[PERL] –Perl Programming Language www.perl.org
[PERL DBI] – Perl’s Database Interface dbi.perl.org
[TD ODBC] – ODBC Driver for Teradata User Guide www.info.teradata.com
[TD DOWNLOAD] - Teradata Download Center http://www.teradata.com/downloadcenter

1.3 Glossary

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

Term Definition
ActivePerl Distribution of Perl from ActiveState
DBMS Database Management System
DSN Data Source Name
iODBC Independent Open Database Connectivity
ODBC Open Database Connectivity
Perl DBI Perl’s database interface

2 Configuring Perl


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:

perl -MDBI -e "DBI->installed_versions"

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

DBD::ODBC       : 1.52
DBD::Gofer : 0.011565
DBD::File : 0.35
DBD::DBM : 0.03
DBD::CSV : 0.22

2.1 Prerequisites

Download and install the latest versions of the following components:

Component Version URL
Teradata ODBC Driver 15 www.teradata.com/DownloadCenter
ActivePerl 5.16 http://www.activestate.com/activeperl
DBD::ODBC 1.52 http://search.cpan.org/~mjevans/DBD-ODBC-1.52/ODBC.pm

The most common approach for getting Perl on Windows platform is the installation of one of the available Win32 Perl distributions such, as ActivePerl (refer to [ACTIVE PERL]).


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;

 

7 Comments
Teradata Employee

Hi,

I’ve trouble with configuration of Perl (5.22) DBD::ODBC (1.52) and TTU 15.10 on Solaris 11.2 . 

Description of issue

 TTU installation of ODBC is OK:

 export ODBCINI=/ggadmin/TD/teradata/teradata/client/ODBC_64/odbc.ini (v priloze)

root@mysolaris:/ggadmin/TD/teradata/teradata/client/15.10/bin# ./tdxodbc64 -C SQLConnect -d testdsn -u dbc -p   .....ODBC connection successful. 

root@mysolaris:/ggadmin/TD/teradata/teradata/client/15.10/bin# ./tdxodbc64 -c SQLDriverConnect -C "DRIVER={Teradata};DBCName=192.168.112.133;UID=dbc;PWD=dbc;" -t  .....ODBC connection successful.

environment variables:

ODBCHOME=/ggadmin/TD/teradata/teradata/client/15.10

ODBCINI=/ggadmin/TD/teradata/teradata/client/ODBC_64/odbc.ini

TTU installdir: /ggadmin/TD/teradata/teradata/

If I follow instruction in video  - http://teradatau.teradata.com/video/Support_Academy/Client/dbdodbc-perl/dbdodbc-perl.html with DBD::ODBC (1.48)  - compilation is fine, but test fails with following error:

make test

PERL_DL_NONLAZY=1 "/ggadmin/TD/perl/bin/perl" "-MExtUtils::Command::MM" "-MTest::Harness" "-e" "undef *Test::Harness::Switches; test_harness(0, 'blib/lib', 'blib/arch')" t/*.t

t/01base.t ................... ok  

t/02simple.t ................. 1/65 Bailout called.  Further testing stopped:  Unable to connect to the database ([Teradata][ODBC Teradata Driver] Not enough information to log on (SQL-28000))

DBI connect('testdsn','dbc',...) failed: [Teradata][ODBC Teradata Driver] Not enough information to log on (SQL-28000) at t/02simple.t line 32.

 

Do you have any experience with this kind of error?

It’s the same if I go through classic installation process:

perl MakeFile.pl

make test

make install

 

According to ODBC Driver for Teradata 15.10 guide -  Setting the runtime environment variables SHLIB_PATH and LD_LIBRARY_PATH are no longer required. The appropriate library references, using symbolic links, have been placed inthe system library directories.

  • If I check linkage of compiled ldd -d blib/arch/auto/DBD/ODBC/ODBC.so with/without application of instruction in the video -> they refer to the same file (ldd -d blib/arch/auto/DBD/ODBC/ODBC.so)< >with appl - libodbc.so =>         /ggadmin/TD/teradata/teradata/client/15.10/lib64/libodbc.sowithout appl - libodbc.so =>         /usr/lib/64/libodbc.so – this is link to /ggadmin/TD/teradata/teradata/client/15.10/lib64/libodbc.so

There is this article which I think is inspired by the video, but if I apply changes on Makefile.PL it ends with this error:

install_driver(ODBC) failed: Can't load '/tmp/install/DBD-ODBC-1.52/blib/arch/auto/DBD/ODBC/ODBC.so' for module DBD::ODBC: ld.so.1: perl: fatal: relocation error: file /tmp/install/DBD-ODBC-1.52/blib/arch/auto/DBD/ODBC/ODBC.so: symbol SQLGetFunctions: referenced symbol not found at /ggadmin/TD/perl/lib/5.22.1/i86pc-solaris-thread-multi-64/DynaLoader.pm line 193.

It think is due to this missing line (compare to your video instructions):

print "You seem to have the official header files.\n";

     $opts{INC} .= " -I$odbchome/include";

$opts{LIBS} = " -L$odbclibdir -lodbc";

print {$sqlhfh} qq{#include <sql.h>\n#include <sqltypes.h>\n#include <sqlext.h>\n#include <sqlucode.h>\n};

Thks,

                Milan

Teradata Employee

Hi Milan,

At the bottom of your post, you said "I think this is due to this missing line...". Did you make the changes to Makefile.PL as described in the article? Did you succeed or did you continue to have errors?

Thanks.

ODBCTeam

Teradata Employee

Is it really necessary to use the DataDirect DM? I have had success using the standard unixODBC DM...without altering the Makefile. 

Teradata Employee

Hi Brandon,

You are correct, it is not necessary to use DataDirect DM. However, it is the only ODBC Driver Manager on Linux/Unix supported by Teradata. We do not support unixODBC DM at this point.

Regards,

ODBCTeam.

Teradata Employee

Hi,

I tried to do this tutorial, on a freshly installed CentOS 7, and when I try to connect to Teradata from a perl script, I got the following: 

perl: symbol lookup error: /opt/ActivePerl-5.20/site/lib/auto/DBD/ODBC/ODBC.so: undefined symbol: SQLAllocHandle

Otherwise, the ODBC connection seems to be OK, I can connect to TD from command line. 

What could case this problem? 

Br,

Mate

Teradata Employee
Hi,

I've communicated the issue with CS support. 

After several not sucessfull attemps we found out the root cause - libtdgss.so in TTU 15.10 is not compiled with libCstd library.

It will be fixed in 15.10.02.01 efix.

Notes about issue:

- It's Solaris specific : combination Perl 5.22 + Perl ODBC module + TTU 15.10 + Linux should works fine.

- It's TTU 15.10 specific: combination Perl 5.22 + Perl ODBC module + TTU 14.10 + Solaris works fine.

(In TTU 15.xx the way how system library are referenced has changed - SHLIB_PATH and LD_LIBRARY_PATH are no longer required.)

Regarding to pmatthew isssue - in chapter 2.2 is described how to replace one block of code in Makefile.PL and there is missing one line:

$opts{DEFINE} = "";

if (-f "$odbchome/include/sql.h") {

print "You seem to have the official DataDirect header files.\n";

$opts{INC} .= " -I$odbchome/include";

$opts{LIBS} = "-L$odbclibdir -lodbc"; #//**MISSING LINE in GUIDE***//

print {$sqlhfh} qq{#include <sql.h>\n#include <sqltypes.h>\n#include <sqlext.h>\n#include <sqlucode.h>\n};

}

Rgds,

Milan

Enthusiast

Hello ODBC Team,

Does DBD::ODBC allows other utilities, Especially the multiload? We use multiload with DBD::Teradata and we are planning to move away from DBD::Teradata to DBD::ODBC, is that a good choice or any known issues with that approach? 

Really appreciate your help. 

 

Thanks

Siva