Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

Connectivity
Enthusiast

Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

I created a User DSN that works with Terada SQL Assistant.  Tried connecting with the same DSN in PHP and got:

 

IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

 

Did some Google'ing and then tried a system DSN [identical except for the name].  This DSN is not seen by SQL Assistant, but PHP at least seems to see it, I get a new error:

 

IM004: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

 

I get the same error with the followign two bits of code:

$connection_string = 'Teradata';
$user = 'MyUsername';
$pass = 'MyPassword';

$conn = odbc_connect( $connection_string, $user, $pass );

 

$connection_string = 'DRIVER={Teradata};SERVER=<server>;DATABASE=<dB name>'; 

$user = 'MyUsername';
$pass = 'MyPassword';

$conn = odbc_connect( $connection_string, $user, $pass ); 

 

I subbed in SQL Server connection strings in each of those and both connect.

 

Thanks in advance for any info you can provide!

 

-Eric

 

9 REPLIES
Enthusiast

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

Just to add -- I'm not completely tied to PHP.  To date I've mostly done PHP/SQL Server reports as those were the tools I had at my disposal.  

 

I now, for the first time, have my own server with admin rights and such, so I'd be fairly free to install what I'd like, though with a budget of near $0 :(

 

If there are some good alternatives for building parameterized self-serve reports connecting to Teradata, I'm happy to learn somethin new as well :)

 

thanks again,

Eric

Enthusiast

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

Update:  Still can't connect to Teradata with PHP, but

* configured a UDL file and that connects OK

* Put together a quick perl script and that connects and returns data

 

Still hopeful that someone can identify some thing I'm doing is causing the problem specific to a PHP connection...

Teradata Employee

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

I am assuming that you are running TD ODBC Driver 15.10.00.00 or 15.10.01.00.

Are you using 32-bit or 64-bit PHP. You need to pick one, as they are not compatible.

 

Using the same ODBC DSN (32-bit used with SQLA) you created, can you connect

to teradata using the sample program "tdxodbc" located in the ODBC Driver install

bin directory. This is to test the connectivity to Teradata outside of PHP.

 

1. DSN-less connection:
$connection = odbc_connect("Driver={Teradata};Server=$server;Database=$database;", $user, $password);

Where $server = is the Teradata hostname.

2. DSN Connection

Create a new DSN with ODBC Driver 32-bit, say "NewDSN" and try the connection again:
$db->Connect('NewDSN','App_User_Id','User_Pass');

Enthusiast

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

I am assuming that you are running TD ODBC Driver 15.10.00.00 or 15.10.01.00.
15.10.01.00


Are you using 32-bit or 64-bit PHP. You need to pick one, as they are not compatible.
Not 100% sure; internet says, PHP_INT_SIZE=8 is 64-bit and PHP_INT_SIZE=4 is 32-bit, so presumably 64-bit


Using the same ODBC DSN (32-bit used with SQLA) you created, can you connect to teradata using the sample program "tdxodbc" located in the ODBC Driver install bin directory. This is to test the connectivity to Teradata outside of PHP.
In trying to get it to work and attempting different things I have three DSNs setup.  A 32/64-bit User DSN, 32-bit System DSN and 64-bit System DSN.
32/64-bit User DSN- ODBC connection successful
32-bit System DSN- ODBC connection successful
64-bit System DSN- The specified DSN contains an architecture mismatch between the driver and application


1. DSN-less connection: $connection = odbc_connect("Driver={Teradata};Server=$server;Database=$database;", $user, $password); Where $server = is the Teradata hostname.
connection failed: IM004 [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed


2. DSN Connection Create a new DSN with ODBC Driver 32-bit, say "NewDSN" and try the connection again: $db->Connect('NewDSN','App_User_Id','User_Pass');
32/64-bit User DSN- IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
32-bit System DSN- IM014: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
64-bit System DSN- IM004: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed.



Thank you for taking the time to look into this issue,
Eric R

Teradata Employee

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

 

  1. DSN-less connection: $connection = odbc_connect("Driver={Teradata};Server=$server;Database=$database;", $user, $password); Where $server = is the Teradata hostname.
    connection failed: IM004 [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

  2. [Response] ODBC 15.10.00.00 had implemented a new feature “User selectable Directories”. But the error reporting from the feature was improved in a later eFix. I think the error is because of a setup issue related to User Selectable Directores.

If you can upgrade to the latest version of the ODBC 15.10.01.04 driver(from TTU 15.10.10 suite), you will see a more meaningful error message.

2. DSN Connection Create a new DSN with ODBC Driver 32-bit, say "NewDSN" and try the connection again: $db->Connect('NewDSN','App_User_Id','User_Pass');
32/64-bit User DSN- IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

[Response]
32-bit System DSN- IM014: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.

 

[Response] seems like a 64bit DSN is being used by a 32bit application or vice versa


64-bit System DSN- IM004: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed.

 

[Response] ODBC 15.10.00.00 had implemented a new feature “User selectable Directories”. But the error reporting from the feature was improved in a later eFix. I think the error is because of a setup issue related to User Selectable Directories.

As recommended above, please upgrade to the latest TTU 15.10.10 suite, that includes

ODBC Driver 15.10.01.04.

 

Enthusiast

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

"If you can upgrade to the latest version of the ODBC 15.10.01.04 driver(from TTU 15.10.10 suite), you will see a more meaningful error message."

 

I went to the teradata download page and didn't see a TTU 15.10.10; 15.10.05 seemed to be the most recent one I could get, so downloaded that, uninstalled what I had and re-installed.

 

I'd note that the Terdata ODBC driver is  listed as 15.10.01.01... not **bleep**.04.  If I should be using an even newer driver, can you post a link to the right TTU download, as I can't seem to find it.

 

Using that tdxodbc program, my newly created three DSNs:

32/64-bit User DSN: Connects successfully.

32-bit System DSN: The specified DSN contains an architecture mismatch between the driver and application

64-bit System DSN: Connects successfully.

 

The last two either flip-flopped behavior from before, or I posted them backwards in the previous message.  I guess without reverting back to the previous driver I can't say.

 

With this driver I get:

DSN-less:  S1000

User DSN:  IM002  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

32-Bit System DSN:  IM014  [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

64-bit System DSN:  S1000

 

So, the middle two are the same as before, the outer two still have the same errors as each other but a new one.  I'm going to see if I can track down anything on that new error code -- hopefully tomorrow I'll have some good news on that front :)

 

Thanks again,
Eric R

Teradata Employee

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

I can't find the TTU 15.10.10 suite from the http://downloads.teradata.com/  either, but I do find the

the latest  ODBC_suite.16.00.01.zip. Please un-install all TTU 15.10.xx packages before installing

16.00.01 packages.

 

[Changes from ODBC 15.10 to ODBC 16.00]

ODBC Driver 16.00.00.xx Driver name has been changed from Teradata to Teradata ODBC Driver 16.00

, so the old ODBC DSN wouldn't work. You have to create a new ODBC Driver DSN for 16.00.

 

 

 

 

 

Enthusiast

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

Uninstalled and re-installed v 16.00 and recreated my DSNs

 

tdxodbc--- same results

32/64-bit User DSN: Connects successfully.

32-bit System DSN: The specified DSN contains an architecture mismatch between the driver and application

64-bit System DSN: Connects successfully.

 

In PHP, no improvement 

DSN-less:  IM002  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

User DSN:  IM002  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

32-Bit System DSN:  IM014  [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

64-bit System DSN:  S1000

 

---

Just to try something else- ran this Perl script:

 

use strict;
use DBI;
my $dbh = DBI->connect('dbi:ODBC:<DSN>', <LOGIN>, <PASSWORD>) or die " Connect failed: " . DBI->errstr;

my $sql = qq/select * from <TABLE>/; 
my $sth = $dbh->prepare($sql); 
$sth->execute(); 
my @row;
while (@row = $sth->fetchrow_array) { 
   print join(", ", @row), "\n";
}

$dbh->disconnect;

 

...that connects and returns data [using the 64-bit system DSN and 32/64-bit User DSN, but not the 32-bit system DSN].  Given that and the tdxodbc.exe results, feels safe to assume (1) I should probably stop bothering with the 32-bit System DSN, and (2) since at least two other things can connect using the ODBC DSNs, the fault is almost likely something specific to PHP/ODBC configuration.

 

I'll do some digging and see if I can find something wrong with my PHP/ODBC setup, though I can connect to SQL Server with the same PHP instance through an ODBC system DSN, so maybe that wouldn't necessarily be the issue.

 

Anything else you can think of that I should try or test would be much appreciated.

 

Thanks,
Eric R

 

Teradata Employee

Re: Error connecting to Teradata with PHP via ODBC [IIS / Server 2012]

My test reveals the same result.

I got 64-bit perl, and using perl script test, I can only connect to

Teradata via ODBC Driver using 64-bit ODBC DSN. When I

used the 32-bit DSN, I got this error:

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002)".

You have to match the bitness (32-bit or 64-bit) of your application with ODBC DSN.