Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

DBD::ODBC to Teradata

by DanEllison (Beadle)
on Nov 05, 2010 at 17:38 UTC ( #869708=perlquestion: print w/replies, xml ) Need Help??
DanEllison has asked for the wisdom of the Perl Monks concerning the following question:

I've developed some tools on windows that work with Teradata using DBD::ODBC. However, when I port them to UNIX, they fail to connect. I'm using unixODBC and unixODBC's isql connects successfully on UNIX using my ODBC configuration so it looks like the ODBC is okay. But when I try to do the DBI->connect, it fails with 'Illegal instruction', and traces make it look like it is failing when it tries to turn autocommit on. A trace of isql does not show this step.

Replies are listed 'Best First'.
Re: DBD::ODBC to Teradata
by kcott (Chancellor) on Nov 05, 2010 at 18:12 UTC

    Perhaps you could show us some code, output, error messages - that sort of thing. :-)

    -- Ken

      My script looks as follows:

      #! /bin/perl use strict; use DBI; use DBD::ODBC; DBI->trace(DBD::ODBC->parse_trace_flag('odbcconnection')); my $dbh = DBI->connect('dbi:ODBC:nhitest','delli28','XXXXXXX'); my $sth = $dbh->prepare("SELECT * FROM dbc.databases"); $sth->execute; while (my $row = $sth->fetchrow_hashref) { print "Name: $$row{DatabaseName}, Owner: $$row{OwnerName}\n"; #print "Name: $$row{TABNAME}, Owner: $$row{OWNER}\n"; } $dbh->disconnect;

      The results look as follows:

      delli28@apsa9012:/u/delli28> non-Unicode login6 dbd_db_login6 SQLDriverConnect 'nhitest', 'delli28', 'xxxx' SQLConnect 'nhitest', 'delli28' Turning autocommit on Illegal instruction

      isql output looks as follows:

      delli28@apsa9012:/u/delli28> isql -v nhitest delli28 XXXXXXX +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select DatabaseName from DBC.Databases +-------------------------------+ | DatabaseName | +-------------------------------+ | gkopf | ...

        Here's some troubleshooting procedures you can try.

        Make these changes to your code:

        #DBI->trace(DBD::ODBC->parse_trace_flag('odbcconnection')); # Note: parse_trace_flags ('s' at the end) here: DBI->trace(DBD::ODBC->parse_trace_flags('5|odbcconnection|odbcunicode' +)); #my $dbh = DBI->connect('dbi:ODBC:nhitest','delli28','XXXXXXX'); my $connect_attrs = { PrintError => 0, RaiseError => 1, AutoCommit => +1 }; my $dbh = DBI->connect('dbi:ODBC:nhitest','delli28','XXXXXXX', $connec +t_attrs);

        Here's references to the documentation for those changes:

        You can try running with AutoCommit => 1 and AutoCommit => 0.

        You can increase the trace level from 5 up to 15.

        -- Ken

Re: DBD::ODBC to Teradata
by ig (Vicar) on Nov 06, 2010 at 18:21 UTC

    Maybe Teradata doesn't support autocommit? See ODBC without autocommit for discussion of this problem with another database.

Re: DBD::ODBC to Teradata
by mje (Curate) on Nov 09, 2010 at 10:48 UTC

    Did you get anywhere with this or are you still having problems? I maintain DBD::ODBC and can help. What UNIX are you using and what are the versions of unixODBC, DBI and DBD::ODBC?

      No, nowhere. I am not an SA on my host so I am working out of local directories.

      AIX unixODBC 2.2.11 DBI 1.615 DBD-ODBC 1.25 DBD-Teradata 12.001

      I've also tried building unixODBC 2.3.0, but where isql works with 2.2.11, under 2.3.0 it complains it can't open

        On the upgrade to unixODBC 2.3.0 that sounds like your 2.2.11 is perhaps built 32bit and the 2.3.0 was built 64bit. Could that be the case?

        I notice there is a v13 of the Teradata driver (at least for Windows). I've spoken to Nick (of unixODBC) and he said he has had some contact with Teradata in the recent past and they were certainly interested in getting it working with unixODBC.

        The autocommit trace entry is interesting as it would normally be followed with a trace line at the same trace level showing the driver's DRIVER_ODBC_VER string. So it would seem we've probably narrowed the problem down to a hand full of lines in DBD::ODBC or SQLSetConnectAttr (for SQL_AUTOCOMMIT in the driver of unixODBC) or SQLGetInfo (for DRIVER_ODBC_VER in the driver or unixODBC). We could probably narrow it down more if you got a unixODBC trace.

        To get unixODBC trace you need to locate your odbcinst.ini file (odbcinst -j will tell you if you installed odbcinst). Otherwise it is probably in /etc or /usr/local/etc. At the top of this file add the following lines:

        [ODBC] Trace = Yes TraceFile = /tmp/unixodbc.log

        then run the failing code once and post the last 50 or so lines from /tmp/unixodbc.log.

        However, the most likely reason for the problem is you've built a rather old unixODBC 64bit and there is a conflict between what the driver and unixODBC/DBD::ODBC are using for SQLLEN and SQLULEN sizes. This is going to be difficult to ascertain unless you can contact Teradata.

        Provide the log I've asked for but I strongly suggest you a) go to Teradata and ask which unixODBC you should be using with their driver or b) get 2.3.0 working because 2.2.11 is so old and pre dates SQLLEN/SQLULEN.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://869708]
Approved by kcott
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (7)
As of 2017-04-27 10:00 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (502 votes). Check out past polls.