PHP 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 various scripting languages such as Perl, Python and PHP.

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 these languages 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
[APACHE] – The Apache Software Foundation www.apache.org
[DD] – DataDirect www.datadirect.com
[iODBC] - Independent Open Database Connectivity www.iodbc.org
[ODBC] Microsoft ODBC Specification http://msdn.microsoft.com/en-us/library/ms710252%28VS.85%29.aspx
[PHP] – PHP Group www.php.net
[PHP ODBC] – PHP ODBC Functions http://php.net/manual/en/ref.uodbc.php
[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
CGI Common Gateway Interface
DBMS Database Management System
DSN Database Source Name
FastCGI CGI extension
HTML HyperText Markup Language
IIS Internet Information Services
iODBC Independent Open Database Connectivity
ODBC Open Database Connectivity

2 PHP


PHP is a portable scripting language. Even though the language includes command line interface and client-side GUI capabilities, its primary objective is server-side scripting to provide dynamic content from a web server to a client. 

In the area of web programming, PHP is one of the most popular scripting languages because of its simplicity, speed, performance, cross platform portability, rich functionality and large set of built-in tools. Database interaction functionality, along with ODBC connectivity, is also built in. In order to communicate with ODBC data source PHP provides a set of ODBC functions (refer to [PHP ODBC] for details). 

2.1 Prerequisites

The components used throughout this tutorial:

Component Version URL
Teradata ODBC Driver 15.10 www.teradata.com/DownloadCenter
PHP 5.6 http://php.net/downloads.php
Apache HTTP Server 2.4 http://httpd.apache.org/
IIS 7  
Microsoft Visual C++ Runtime 9 http://www.microsoft.com/downloads/details.aspx?FamilyID=9B2DA534-3E03-4391-8A4D-074B9F2BC1BF&displa...

PHP can be deployed on most web servers and many operating systems. This tutorial will focus on IIS and Apache servers on Microsoft Windows and Apache server on UNIX. 

2.1.1 Microsoft Windows

This section guides you through the configuration of PHP on Microsoft Windows platform for IIS and Apache web servers. 

2.1.1.1 Install and Configure PHP

Download the archive of Microsoft Windows distribution of PHP. Unpack the content of the zip archive into a directory of your choice, for example c:\php. Note that depending on the platform and configuration, latest versions of the distribution might require additional installation of the Microsoft C++ Runtime. For more details refer to [PHP].

Update PATH environment variable to include the directory PHP was deployed into (c:\php for example): 

Verify the installation of PHP by executing the following command at the command prompt:

php-cgi –v

The result should yield the correct version of the PHP installed, similar to the following:

PHP 5.6.16 (cgi-fcgi) (built: Nov 25 2015 18:52:30)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies

The next command returns the configuration information of PHP environment in case of successful installation:

php-cgi –info

Make sure the command executes successfully, there is no need to verify details of configuration. The final verification is done later in this chapter. 

2.1.2 Configure Web Server

This section describes the details of Web server configuration, follow the section relevant for your system’s configuration.

2.1.2.1 IIS 

Common Gateway Interface (CGI) is a standard way of interfacing external applications with Web servers. CGI is supported as part of the IIS feature-set. FastCGI is a high performance alternative to the Common Gateway Interface (CGI). To enable both CGI and FastCGI:
  1. On the Start menu, click Control Panel;
  2. In Control Panel, click Programs and Features;
  3. Click Turn Windows Features on or off;
  4. Expand Internet Information Services and turn ON the services 

2.1.2.2 Apache Server

Download and install Apache Web Server (refer to [APACHE]). To make sure the server is operational navigate to http://localhost and verify the valid response from the server. Default clean installation of Apache should return a page similar to the following:


Figure 1: Apache's default page

Configuration of Apache server is controlled with httpd.conf file. The file is located at <APACHE_ROOT>\Conf folder. APACHE_ROOT is the root the server installation (default is C:\Program Files\Apache Software Foundation\Apache2.4). Update the file to add support for PHP scripts as follows:

In the root of the document (outside of any section), add the following line to enable PHP module:

LoadModule php5_module "C:/php/php5apache2_4.dll"

Inside <IfModule mime_module> section, add the following line to associate  PHP module with *.php extension:

AddType application/x-httpd-php .php

If your configuration requires execution of PHP scripts embedded within HTML code, add the following as well:

AddType application/x-httpd-php .htm .html

2.1.3 Verify PHP configuration

Create a text file that contains this PHP code:

<?php phpinfo(); ?>

Save the file as verify.php. Copy the file to the root of the Web server. By default, it is C:\inetpub\wwwroot for IIS and C:\Program Files\Apache Software Foundation\Apache2.4\htdocs for Apache.

<?php phpinfo(); ?>

Navigate the browser to http://localhost/verify.php and make sure the result presents details of PHP environment as illustrated in the next figure. 


Figure 2: phpinfo() result

2.1.4 UNIX

The section demonstrates the steps required to install and configure PHP with Apache Web Server on UNIX platform. 

2.1.4.1 Install Apache Server

Download source distribution of the server (refer to [APACHE]). Build the server:

tar -xzf httpd-2.4.18.tar.gz
cd httpd-2.4.18
./configure --prefix=/usr/local/apache2 --enable-so
make

Install as root:

make install

Start up the server:

/usr/local/apache2/bin/apachectl start

Verify the installation – navigate to http://localhost and verify the valid response from the server. Default clean installation of Apache should return a page similar to Figure 1: Apache's default page.

2.1.4.2 Install PHP

There are two common approaches to configure Apache to use PHP:

MOD_PHP – Apache loads PHP as Apache module. In this configuration PHP compiled as an Apache module and runs in the Apache process. Therefore, PHP module is shared among all the children processes spawned by Apache.

CGI – Apache executes PHP as CGI binary. In this configuration a CGI request, which is executed as a single process, creates its own PHP process. 

Each of the configurations has its own pros and cons, such as faster performance of MOD_PHP vs. stronger security of CGI, for more detailed information please refer to [APACHE] and [PHP].

Currently, there is a limitation in the Terada ODBC driver that prevents use of the driver in MOD_PHP configuration on certain UNIX like platforms. This is due to the following command executed by the driver during the connection handshake with Teradata Database:

readlink (“proc/self/exe” )

For instance, on Linux OS, the driver uses this command in order to identify the name of the executing process. This approach is very common on Linux OS platforms. However, typical configuration assumes that initially the server run as root user, then the server switches to a configured dedicated user. However, a non root user does not have access to “proc/self/exe” link that still belongs to a parent that spawned a child process. As a result, due to the “Permission Denied” error, the connection fails. 

There are several ways to solve this problem, such as initial execution of the server under a different non-root user (This approach has its drawbacks, one of which is the default restriction of the non-root user to binding ports higher than 1024. The port issue can be solved by web site redirection or security elevation of a non-root user).

To simplify the administration, this tutorial focuses on the CGI configuration of PHP. At the end of this chapter you will find the steps required for MOD_PHP deployment. 

It is important to build PHP against the DataDirect driver manager that is distributed with Teradata ODBC driver. For that reason make sure ODBC_HOME environment variable points to a correct folder: 

tar -xzf php-5.6.16.tar.gz
cd php-5.6.16whcih
export ODBC_HOME=/opt/teradata/client/ODBC_32
export CPPFLAGS="-I$ODBC_HOME/include"
export CUSTOM_ODBC_LIBS="-L$ODBC_HOME/lib -lodbc -lodbcinst"
./configure --prefix=/usr/local/php --enable-fastcgi --with-custom-odbc=$ODBC_HOME

Build:

make 

Install as root:

make install

Verify installation of PHP by executing the following command at the command prompt:

php-cgi –v

The result should yield the correct version of the PHP installed, similar to the following:

PHP 5.6.16 (cgi-fcgi) (built: Jan 4 2016 14:49:17)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies

The next command returns the configuration information of PHP environment in case of successful installation:

php-cgi –info

Make sure the command executes successfully, there is no need to verify details of the configuration. The final verification is done later in this chapter. 

Configuration of Apache server is controlled with httpd.conf file. Update the file to add support for PHP CGI scripts as follows: 

Inside <IfModule mime_module> section, add the following to link new MIME type with php-cgi:

AddType application/x-httpd-php .php
Action application/x-httpd-pophp "/php/php-cgi"

Inside <IfModule alias_module> section, add the following line to define a CGI script directory alias:

ScriptAlias /php/ "/usr/local/php/bin/"

In the root of the document (outside of any section) add this block:

<Directory "/usr/local/php/bin">
SetEnv ODBCINI "/ opt/teradata/client/ODBC_32/odbc.ini"
SetEnv ODBCINSTINI "/opt/teradata/client/ODBC_32/odbcinst.ini"
Options Indexes FollowSymlinks
AllowOverride None
Options None
Order allow,deny
Allow from all
</Directory>

Note the definition of ODBCINI and ODBCINST environment variables. Those variables are passed into PHP process during request handling. Alternatively, those variables can be defined within PHP script itself:

<?php
putenv("ODBCINI=/opt/teradata/client/ODBC_32/odbc.ini");
putenv("ODBCINSTINI=/opt/teradata/client/ODBC_32/odbcinst.ini");
?>

In addition, environment variables in Apache can be configured with envvars file. See an example below in the configuration of MOD_PHP.

Restart the server:

/usr/local/apache2/bin/apachectl restart

In the root folder of the web server (default is /usr/local/apache2/htdocs) create a text file that contains the following PHP code:

<?php phpinfo(); ?>

Save the file as verify.php. Navigate your browser to http://localhost/verify.php and make sure the result presents details of PHP environment similar to Figure 2: phpinfo() result.

Next steps briefly illustrate configuration of MOD_PHP. Configure:

tar -xzf php-5.6.16.tar.gz
cd php-5.6.16
export ODBC_HOME=/opt/teradata/client/ODBC_32
export CPPFLAGS="-I$ODBC_HOME/include"
export CUSTOM_ODBC_LIBS="-L$ODBC_HOME/lib -lodbc -lodbcinst"
./configure --with-apxs2=/usr/local/apache2/bin/apxs --prefix=/usr/local/php --with-custom-odbc=$ODBC_HOME

Build:

make 

Install as root:

make install

Update httpd.conf, make sure the following lines exist: 

LoadModule php5_module    modules/libphp5.so
AddType application/x-httpd-php .php

Also, make sure to update User and Group variables in the same file, such as:

User apache
Group apache

Depending on the platform and user configuration update envvars file (default location - /usr/local/apache2/bin/envvars) if necessary to include environment variables:

ODBCINI="/ opt/teradata/client/ODBC_32/odbc.ini "
export ODBCINI
ODBCINSTINI="/opt/teradata/client/ODBC_32/odbcinst.ini"
export ODBCINSTINI
LD_LIBRARY_PATH="/usr/local/apache2/lib:/opt/teradata/client/ODBC_32/lib:/opt/teradata/client/15.10/tdicu/lib:$LD_LIBRARY_PATH”
export LD_LIBRARY_PATH

2.2 Create Sample Data

This section examines a script that generates some sample Payroll data. 

As mentioned above, PHP provides a set of function to interact with ODBC data source. In general, the scenario of interaction consists of the following steps:

Establish connection with odbc_connect() function;

Execute SQL statement with odbc_exec() function;

Disconnect with odbc_close() function.

2.2.1 Connect

The odbc_connect() function is used to connect to an ODBC data source. The function takes four arguments: DSN name, user name, password and optional cursor type. The function returns the connection id, which is used by other ODBC functions or 0 (False) on error. In case of connection failure, odbc_error() and odbc_errormsg() functions can be used for diagnostics. odbc_error() returns a six-digit ODBC state; odbc_errormsg() returns a string containing the last error. For more detailed information about ODBC states and errors refer to [ODBC], [TD ODBC] and [PHP ODBC]. 

$connection=odbc_connect('testdsn','odbc','odbc');
if (!$connection){
exit("Connection Failed: ".odbc_error().":".odbc_errormsg());
}

2.2.2 Execute

odbc_exec() function is used to execute SQL statement. The arguments to the function are: connection id and the SQL statement string. The function returns an ODBC result identifier or 0 (False) in case the statement was not executed successfully. The result id is used for data fetching. More on data fetching can be found in Retrieve Data section below. As already mentioned, odbc_error() and odbc_errormsg() functions are used for diagnostics of failures. 

$result=odbc_exec($connection, "drop table employee");
if (!$result){
print("Execution Failed: ".odbc_error().":".odbc_errormsg());
}

2.2.3 Disconnect

The odbc_close() function is used to close an ODBC connection:

odbc_close($cn);

2.2.4 Complete Example

<?php 

// establish connection
$cn=odbc_connect('testdsn','odbc','odbc');
if (!$cn){
exit("Connection Failed: ".odbc_error().":".odbc_errormsg()."\n");
}

// drop employee table
if (!odbc_exec($cn, "drop table employee")) {
print("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

// create employee table
if (!odbc_exec($cn, "CREATE SET TABLE employee (employee_number INTEGER NOT NULL PRIMARY KEY, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL)")){
exit("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

// populate employee table with sample data
if (!odbc_exec($cn, "INSERT INTO employee (employee_number, last_name, first_name) VALUES (2, 'Olson', 'Chuck')")) {
print("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

if (!odbc_exec($cn, "INSERT INTO employee (employee_number, last_name, first_name) VALUES (3, 'Lee', 'Bill')")) {
print("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

if (!odbc_exec($cn, "INSERT INTO employee (employee_number, last_name, first_name) VALUES (4, 'Chapman', 'Lisa')")) {
print("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

if (!odbc_exec($cn, "INSERT INTO employee (employee_number, last_name, first_name) VALUES (1, 'Miller, 'Susan')")) {
print("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

// disconnect
odbc_close($cn);

?>

2.3 Retrieve Data

Once the connection is established, PHP application can execute a select SQL statement and then fetch data with one of the fetch ODBC functions, such as odbc_fetch_row(). The following sample fetches all the available rows. 

while (odbc_fetch_row($statement)) {
$firstName=odbc_result($statement,"first_name");
$lastName=odbc_result($statement,"last_name");
}

2.3.1 Complete example

<?php 

// establish connection
$connection=odbc_connect('testdsn','odbc','odbc');
if (!$connection){
exit("Connection Failed - ".odbc_error().":".odbc_errormsg()."\n");
}

// execute select statement
$statement=odbc_exec($connection, "select first_name, last_name from employee");
if (!$statement){
exit("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

// generate html code
echo "<html><head><title>Employees</title><head><body>";
echo "<table><tr>";
echo "<th>FIRST NAME</th>";
echo "<th>LAST NAME</th></tr>";

// fetch data
while (odbc_fetch_row($statement)) {
$firstName=odbc_result($statement,"first_name");
$lastName=odbc_result($statement,"last_name");

// output data as rows in html table
echo "<tr><td>$firstName</td>";
echo "<td>$lastName</td></tr>";
}

// close connection
odbc_close($connection);

// finalize html page code
echo "</body></table>";

?>

Note that the sample above generates HTML code from within a PHP script. It is also possible to embed PHP script within HTML: 

<html>
<head>
<title>Employees</title>
</head>
<body>
<table>
<tr>
<th>FIRST NAME</th>
<th>LAST NAME</th>
</tr>

<?php

// establish connection
$connection=odbc_connect('Payroll','','');
if (!$connection){
exit("Connection Failed - ".odbc_error().":".odbc_errormsg()."\n");
}

// execute select statement
$statement=odbc_exec($connection, "select first_name, last_name from employee");
if (!$statement){
exit("Execution failed - ".odbc_error().":".odbc_errormsg()."\n");
}

// fetch data and populate the table
while (odbc_fetch_row($statement)) {
$firstName=odbc_result($statement,"first_name");
$lastName=odbc_result($statement,"last_name");
echo "<tr><td>$firstName</td>";
echo "<td>$lastName</td></tr>";
}

// close connection
odbc_close($connection);

?>

</table>
</body>
</html>

Refer to Prerequisites section above for the details on how to add association of HTML files with PHP module in order to enabled embedded PHP scripts.

1 Comment

We are currently trying to connect to a TD database using PHP.  This page is very useful but I was wondering about the references to ODBC_32.  Can we not use the 64 version?  Also, we were trying to use PHP' PDO instead of the odbc_connect, etc. commands.  Is PDO not an option?  Thanks for any information that you can provide.