Not able to connect to teradata from php

[sankot]

thread434-1439713

Can anyone help me why I am getting below error

[05-Nov-2014 12:29:52] PHP Warning: odbc_connect(): SQL error: Failed to fetch error message, SQL state HY000 in SQLConnect in /var/

http://www/html/edspbj/application/controllers/AdminPbjController.php

on line 77
[05-Nov-2014 12:29:52] PHP Warning: odbc_exec() expects parameter 1 to be resource, boolean given in /var/

http://www/html/edspbj/application/controllers/AdminPbjController.php

on line 79

http://tek-tips.com/threadminder.cfm?pid=434&page=1

I am getting above error for the below statement

$con = odbc_connect("testdsn", "admin", "password");

 

[jpadie]

there is nothing wrong with the php here. the problem is in the odbc connection in the odbc manager.

try to connect directly through the snap-in. if that is working fine then check the following

1. make sure that the DSN is a system DSN.
2. make sure that the DSN is created in the 32 bit version of the snap-in if the driver is 32 bits.


or you could use a DSN-less connection string. the format would be something like this

$dsn = "Provider=Teradata;DBCName={$HostName};Database={$DBName};";
odbc_connect($dsn,$userName,$password);

 

[sankot]

Hi jpadie,

Thanks for your quick reply.

I am running my project in UNIX box,how can I set system env variable in UNIX irrespective of my profile?

or with your 2nd approach

Its giving me the error as

" PHP Warning: odbc_connect(): SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect "

with the following code.

$dsn = "Provider=Teradata;DBCName={testdsn};";
$con=odbc_connect($dsn,"admin","password");

In my /etc/odbc.ini I declared

[ODBC]
InstallDir=/opt/teradata/client/14.10/odbc_64
Trace=1
TraceDll=/opt/teradata/client/14.10/odbc_64/lib/odbctrac.so
TraceFile=/tmp/trace.log
TraceAutoStop=0

[ODBC Data Sources]
testdsn=Teradata

[testdsn]
Driver=/opt/teradata/client/ODBC_64/lib/tdata.so
Description=Teradata database
DBCName=172.29.1.71
LastUser=
Username=
Password=
Database=
DefaultDatabase=

Please let me know if I missed any configuration settings to connect to teradata like adding the environment variables like LD_LIBRARY_PATH etc ..

Thanks,

 

[jpadie]

Sounds like you don't have an odbc teradate driver installed properly. Or you are running php in 32 bit mode.

This is not a php problem however. You may get more knowledgable feedback in a teradata forum.

 

[sankot]

I am running php in 64 bit mode

odbc teradata installed properly ..why because when I run the below command in Linux ,odbc connection got successful ..

root@ledsdndbv01> /opt/teradata/client/14.10/odbc_64/bin/tdxodbc

Enter Data Source Name: testdsn_etc
Enter UserID: admin
Enter Password:

Connecting with SQLConnect(DSN=testdsn_etc,UID=admin,PWD=*)...

.....ODBC connection successful.

ODBC version = -03.52.0000-
DBMS name = -Teradata-
DBMS version = -13.10.0712 13.10.07.12-
Driver name = -tdata.so-
Driver version = -14.10.00.00-
Driver ODBC version = -03.51-

(type quit to terminate adhoc)
Enter SQL string : exit

So, I am not able to connect from PHP to teradata .
I am not sure what ODBC configurations or setting environmental variables or some settings in php.ini need to be set .
Can you please help me what and all need to be configurated for to connect to TERADATA from PHP .
your help would be greatly appreciated .

Thanks,

 

[jpadie]

The error you are getting in php is from odbc. Phone is working fine. It is not a php issue.

The problem is elsewhere. A message like 'data source not found' suggests that the dsn has not been properly set up. So you either need to fix that or use a dsn-less connection.

I cannot help with how to set up an odbc connection for teradata on Linux; or at least not within this forum which is for php issues. I suggest that a more specific forum may be able to provide you with more specific aid.

As an aside I note that you have curly braces around the dbname in the dsn-less connection attempt above. I would take them out. If you are copying my example you will note that I was using variables. Curly braces are used by oho in that context to distinguisb variables from the rest of the string.

 

[jpadie]

Note also that in a dsn-less connection the whole point is that you don't use a dsn. So referring to testdsn is pointless. You have to provide the hostname as dbcname.

But there is no reason why a proper dsn will not work and I always think that is better.

PS reference to 'phone' above is an autocorrect from my mobile client. It should of course be to php. As is the ref to oho in the last para.

Curly braces are also used in dsn-less connection strings of course. To escape spaces and other characters. So that may not be a relevant issue at all. Apologies - it is very early where I am!

 

[sankot]

$HostName='172.29.1.29';
$DBName='PRD_ETL';
$userName="XXXXXXXX";
$password="XXXXXXX";
$dsn = "Provider=Teradata;DBCName={$HostName};Database={$DBName};";
$con=odbc_connect($dsn,$userName,$password);
var_dump($con);

I followed the same steps as you given but still I am getting the below error

09:01:24] PHP Warning: odbc_connect(): SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in /var/

http://www/html/edspbj/application/controllers/AdminPbjController.php

on line 78

How can it recognise the provider= Teradata .. DO I need to declare or assign it somewhere ?

 

[jpadie]

you may then not be able to use DSN-less connection strings with SQLConnect on your version of the odbc library for php.

you will have to go back to creating the DSN through the ini files.

please consult the appropriate forum for linux odbc and the teradata database. the php aspects of this are working fine.

a quick google suggests that there are plenty of good resources around for this.

good luck!

 

[sankot]

Ok Thanks for your help.

And one more thing ..
When I ran phpinfo() I am getting

'--without-unixODBC' '--disable-pdo' '--disable-xmlreader' '--disable-xmlwriter' '--without-sqlite3' '--disable-phar' '--disable-fileinfo' '--disable-json' '--without-pspell' '--disable-wddx' '--without-curl' '--disable-posix' '--disable-sysvmsg' '--disable-sysvshm' '--disable-sysvsem'

As because am getting --without-unixODBC ,am I not able to connect to odbc ??if thats the case can you please help me how can I change to --with-unixODBC ?

Thanks