Re^3: DBD::ODBC to Teradata

[mje]

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.

[DanEllison]

I am 'making' unixODBC the same regardless of version. I am using the same DB2 driver (which I believe is 64bit) with both versions successfully.

I have both 32bit and 64bit drivers for Teradata, and where the 32bit driver works with isql, the 64bit complains about the 'magic' number in 2.2.11. In 2.3.0, both 32bit and 64bit cannot open tdata.so and neither refer to the magic number.

As a side note my Teradata client is 13.0 and it comes with it's own libodbc.a, libodbcinst.a, and more. So, the order of Teradata to unixODBC in my LIBPATH also becomes important.

The last 50 of unixODBC.log follows:

[ODBC][749914][SQLDriverConnect.c][678]
                Entry:
                        Connection = 20539e08
                        Window Hdl = 0
                        Str In = [nhitest][length = 7]
                        Str Out = 2ff21c14
                        Str Out Max = 512
                        Str Out Ptr = 2ff21a10
                        Completion = 0
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE '
+UCS-2'

[ODBC][749914][SQLDriverConnect.c][1258]
                Exit:[SQL_ERROR]
[ODBC][749914][SQLError.c][424]
                Entry:
                        Connection = 20539e08
                        SQLState = 2ff217a8
                        Native = 2ff217a4
                        Message Text = 2ff217b0
                        Buffer Length = 511
                        Text Len Ptr = 2ff217a0
[ODBC][749914][SQLError.c][461]
                Exit:[SQL_NO_DATA]
[ODBC][749914][SQLConnect.c][3549]
                Entry:
                        Connection = 20539e08
                        Server Name = [nhitest][length = 7]
                        User Name = [delli28][length = 7]
                        Authentication = [********][length = 8]
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE '
+UCS-2'

[ODBC][749914][SQLConnect.c][4123]
                Exit:[SQL_SUCCESS]
[ODBC][749914][SQLSetConnectAttr.c][318]
                Entry:
                        Connection = 20539e08
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = 1
                        StrLen = 0
[ODBC][749914][SQLSetConnectAttr.c][671]
                Exit:[SQL_SUCCESS]
[ODBC][749914][SQLGetInfo.c][214]
                Entry:
                        Connection = 20539e08
                        Info Type = SQL_DRIVER_ODBC_VER (77)
                        Info Value = 20537b84
                        Buffer Length = 20
                        StrLen = 2ff21980
[ODBC][749914][SQLGetInfo.c][501]Error: IM001

[mje]

the 64bit complains about the 'magic' number in 2.2.11

To me that indicates the isql you are using with 2.2.11 is built 32bit.

In 2.3.0, both 32bit and 64bit cannot open tdata.so and neither refer to the magic number

I have no idea what tdata.so is. To my knowledge it is not something in unixODBC or DBD::ODBC.

my Teradata client is 13.0 and it comes with it's own libodbc.a, libodbcinst.a

But what are these archives? Are they teradata's builds of unixODBC?

The log shows SQLGetInfo failing with IM001 which is "Driver does not support this function". An ODBC driver cannot not support SQLGetInfo or how can any application find out anything about how the driver works. This would indicate some other problem and I think you are best going back to Teradata with this problem or posting to the unixodbc help mailing list as I'm told some Teradata people hang out there.

UPDATE: You can add "DisableGetFunctions = 1" to the driver entry in odbcinst.ini and that tells unixODBC to ignore SQLGetFunctions returned by the driver and just call the function. It may be Teradata here is saying it does not support SQLGetInfo when in fact it must.

UPDATE: You could also try rerunning your Perl code with PERL_DL_NONLAZY environment variable set to 1.

[DanEllison]

tdata.so is the Teradata driver. I don't understand why 2.3.0 wouldn't be able to open it, when 2.2.11 is obviously using it.

I find that the Teradata client ships with DataDirect ODBC with it. I found this document, http://web.datadirect.com/resources/odbc/perl/install-config.html, for configuring the DataDirect, but I get new errors. Still playing...

The DisableGetFunctions didn't seem to have any effect.

[DanEllison]

Okay! I got it going with the DataDirect ODBC that was shipped with the Teradata client. My first problem, although InstallDir pointed to a directory with my ini's and libs, it didn't have locale info which is why I wasn't getting my messages. Secondly, DataDirect apparently doesn't use the odbcinst.ini so I had to directly identify my driver in the .odbc.ini.

After getting it to work with DataDirect, I went back to see if I could get it working with unixODBX. Under 2.2.11, tdata.so was trying to open shared objects that existed in DataDirect and don't exist in unixODBC. 2.3.0 couldn't open the tdata.so driver at all.

To get the DBD-ODBC to compile with DataDirect, I had to make similar modifications to the Makefile.PL as were described in the document http://web.datadirect.com/resources/odbc/perl/config.html. My diff looks like:

diff Makefile.DataDirect Makefile.PL
448,450d447
<     $myodbc = 'intersolve'
<         if !$myodbc && -f "$odbchome/include/sqlunx.h";
<
463a461,463
>     $myodbc = 'intersolve'
>         if !$myodbc && -f "$odbchome/include/qeodbc.h";
>
627c627
<       #print {$sqlhfh} qq{#include <qeodbc.h>\n};
---
>       print {$sqlhfh} qq{#include <qeodbc.h>\n};
631,632c631
<           #print {$sqlhfh} qq{#include <sql.h>\n#include <sqltypes.h
+>\n#include <sqlext.h>\n};
<           print {$sqlhfh} qq{#include <sql.h>\n#include <sqltypes.h>
+\n#include <sqlext.h>\n#include <sqlucode.h>\n};
---
>           print {$sqlhfh} qq{#include <sql.h>\n#include <sqltypes.h>
+\n#include <sqlext.h>\n};

The Teradata client install builds a link /usr/odbc for ODBCHOME, but it didn't include includes or odbc_config so I found it better to set ODBCHOME directly to /opt/teradata/client/13.0/odbc_32 for building. InstallDir could still point to link /usr/odbc.