Solved

Problem with unixODBC on 64bit (CentOS) Linux Server

Posted on 2014-02-20
8
1,155 Views
Last Modified: 2014-02-27
Hello,

I'm having some trouble finding a solution to this so I thought I would give the experts a try. I am connecting to an ibm iSeries using an PHP odbc connection from CentOS 6.4 apache server, the problem is, any occurrence of a NULL value being returned causes the PHP script to stop processing (seriously Ive run it in debug and it just stops... doesn't cause a PDO error even...).

Anyhow, after looking into this I found the reason is because my unixODBC was compiled using the 8-byte (64bit) SQLULEN/SQLLEN (numeric length) which i have verified by running odbcinst -j inside linux. This IS the actual spec for a 64 bit build, but since the drivers are still using a 4-byte SQLLEN/SQLULEN (possibly even SQLINTEGER), it causes the script to fail, since NULL is actually being interpreted by the driver as 0xFFFFFFFF and the script doesn't know what to do with it.

Now I have gotten around this for the most part just making sure that columns selected with Outer joins use coalesce to replace the null value with '' but I have come across a query that this will not work for (its a right outer join that always needs one result returned so its joined on several fields OR field IS NULL)

Anyhow, I was hoping someone could explain to me how to change the unixODBC build on this system to a 4-byte SQLLEN/SQLULEN which was explained as one of the options for fixing this (The other I found was to find each driver that had problems and fix it so it uses the 8-byte SQLLEN/SQLULEN, but since this site does work on the ibm server it runs on currently I decided this is the better option -- fix one or find all and fix each one sounded unnecessary) Anyhow please help. Thank you.
0
Comment
Question by:front71
  • 4
  • 4
8 Comments
 
LVL 34

Assisted Solution

by:Duncan Roe
Duncan Roe earned 500 total points
ID: 39880095
You have done well to get as far  as you have. In case you  didn't already see it, here is the definition of SQLULEN
/*
 *  New Win64 datatypes
 */
#ifdef _WIN64
typedef INT64                   SQLLEN;
typedef UINT64                  SQLULEN;
typedef UINT64                  SQLSETPOSIROW;
#elif defined(STRICT_ODBC_TYPES)
typedef long                    SQLLEN;
typedef unsigned long           SQLULEN;
typedef unsigned short          SQLSETPOSIROW;
#else
#define SQLLEN                  long
#define SQLULEN                 unsigned long
#define SQLSETPOSIROW           unsigned short
#endif

Open in new window

from /usr/include/sqltypes.h which is provided by the libiodbc package which is "an Open Source platform independent implementation of both the ODBC and X/Open specifications".
Never mind that SQULEN may be a macro or a typedef, the real problem with its definition as unsigned long is that it will be 32 bits on a 32-bit system and 64 bits on a 64-bit system.
So I have to ask: where are the 32-length drivers that you have located? I mean, are they on 32-bit systems? Or perhaps they support databases that use 32-bit integers?
You can't "fix" something that isn't broken. If your problem is between 32 and 64 bit systems, then ODBC should be handling it, by exchanging architecture information. If it doesn't, that's a reportable defect.
0
 

Author Comment

by:front71
ID: 39882692
Hi Thank you for your response, i appreciate it. The drivers that are a problem are released by IBM (part of the ibm_db2 package) and the package you can download from ibm is called iSeriesAccess for 64bit linux (it is a pain to find the download page though... http://www-03.ibm.com/systems/power/software/i/access/linux_resources.html -- that page describes the drivers) Anyhow from what I've seen unixODBC is denying responsibility for the issue since their build follows the CURRENT spec for 64 bit support and IBM seems to ignore the people that have brought it to their attention (To support sales of their expensive commercial product Iseries connect). So there has to be something that can be done...
0
 

Author Comment

by:front71
ID: 39882716
0
 
LVL 34

Assisted Solution

by:Duncan Roe
Duncan Roe earned 500 total points
ID: 39883825
Yes it does look hard to download - as a non-customer can I get the software at all?
Anyway, I don't have a 64-bit system right now (but will have plenty of time on my hands starting next week). Does IBM let you download source?
Let's say we're stuck with the existing drivers. Do you think it safe to rebuild Unix ODBC with the shorter SQULEN? You need to ensure you have the source for all possibly-affected systems. Now you really have to replace all occurrences of unsigned long, both in the software and in the header file, with uint32_t, and all remaining occurrences of long with int32_t. You need the header <stdint.h>. You then need to resolve any conflicts with the prototypes of system functions. Modify the header file to unsure long can not be a #define. If you can get it to build, give it a try.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Assisted Solution

by:Duncan Roe
Duncan Roe earned 500 total points
ID: 39883855
Another possibility is to mask retrieved values down from 64 bits to 32. I'm surprised the system works at all without doing that actually. Because the architecture is little endian, the junk after the 32-bit value on the stack ends up as the most significant bytes which should then be discarded on rounding down, but somehow 0xffffffff00000000 becomes 0xffffffff.
Perhaps you could re-check this in the debugger
0
 

Author Comment

by:front71
ID: 39892287
Thank you Duncan you're the best. So after reading your response I decided the better solution was to, rather than changing the header files in a package that could be upgraded and overwritten at some point, I searched for an alternative driver.

I somehow came across a newer package (seems to have been released Dec 2013) called "IBM i Access ODBC Driver" (The other one was called iSeriesAccess) from IBM (man, Their site is so difficult to find anything) Anyhow it was listed as a solution to the same issue I was seeing from the previous package on a iPro developer page I came across.

Anyhow I downloaded and installed the package but now I see the driver (Both the 32 bit and 64 bit versions) segfaulting and exiting the script with no error (I do have PDO::ERRMODE_EXCEPTION set too so really it should be throwing the PDOException that I have coded to handle this type of thing... ugh)

It happens anytime I do an execute with parameters (which we do to help fight of SQL injection) If I hardcode the values into the query, it finishes fine (ex. Where this = $varVal) but if I pass a bound parameter
(ex.
Where this = ?
OR
Where this = :varVal)
and then bind it
(ex.
$result->bindParam(1, $domain, PDO::PARAM_STR)
$result->execute();
OR
$result->execute(array($domain))
) It fails on the execute line. If I pass the values the more risky way (ex. Where this = 24) it works just fine... How frustrating. I forced odbc logging and reviewing that really doesnt help but I will attach the file I can see the parameters have been set with SQLDescribeParam.c but when the SQLExecute.c goes to run it just stops

[ODBC][33019][1393519842.693971][__handles.c][460]
            Exit:[SQL_SUCCESS]
                  Environment = 0x7f55ebb8cd30
[ODBC][33019][1393519842.694022][SQLSetEnvAttr.c][189]
            Entry:
                  Environment = 0x7f55ebb8cd30
                  Attribute = SQL_ATTR_ODBC_VERSION
                  Value = 0x3
                  StrLen = 0
[ODBC][33019][1393519842.694043][SQLSetEnvAttr.c][364]
            Exit:[SQL_SUCCESS]
[ODBC][33019][1393519842.694062][SQLSetEnvAttr.c][189]
            Entry:
                  Environment = 0x7f55ebb8cd30
                  Attribute = SQL_ATTR_CP_MATCH
                  Value = (nil)
                  StrLen = 0
[ODBC][33019][1393519842.694079][SQLSetEnvAttr.c][364]
            Exit:[SQL_SUCCESS]
[ODBC][33019][1393519842.694097][SQLAllocHandle.c][375]
            Entry:
                  Handle Type = 2
                  Input Handle = 0x7f55ebb8cd30
[ODBC][33019][1393519842.694118][SQLAllocHandle.c][493]
            Exit:[SQL_SUCCESS]
                  Output Handle = 0x7f55eba541c0
[ODBC][33019][1393519842.694138][SQLSetConnectAttr.c][396]
            Entry:
                  Connection = 0x7f55eba541c0
                  Attribute = SQL_ATTR_AUTOCOMMIT
                  Value = 0x1
                  StrLen = -6
[ODBC][33019][1393519842.694156][SQLSetConnectAttr.c][671]
            Exit:[SQL_SUCCESS]
[ODBC][33019][1393519842.694188][SQLSetConnectAttr.c][396]
            Entry:
                  Connection = 0x7f55eba541c0
                  Attribute = SQL_ATTR_ODBC_CURSORS
                  Value = (nil)
                  StrLen = -6
[ODBC][33019][1393519842.694208][SQLSetConnectAttr.c][671]
            Exit:[SQL_SUCCESS]
[ODBC][33019][1393519842.694236][SQLDriverConnect.c][728]
            Entry:
                  Connection = 0x7f55eba541c0
                  Window Hdl = (nil)
                  Str In = [Driver={IBM i Access ODBC Driver};System=XXX;database=XXX;UID=XXX;PWD=**********][length = 95]
                  Str Out = 0x7fff4f9c8200
                  Str Out Max = 1023
                  Str Out Ptr = 0x7fff4f9c81fe
                  Completion = 0
            UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

[ODBC][33019][1393519842.713382][SQLDriverConnect.c][1695]
            Exit:[SQL_SUCCESS]
                  Connection Out [Driver={IBM i Access ODBC Driver};System=XXX;database=XXX...]
[ODBC][33019][1393519842.713984][SQLAllocHandle.c][540]
            Entry:
                  Handle Type = 3
                  Input Handle = 0x7f55eba541c0
[ODBC][33019][1393519842.714162][SQLAllocHandle.c][1081]
            Exit:[SQL_SUCCESS]
                  Output Handle = 0x7f55ebb8fff0
[ODBC][33019][1393519842.714209][SQLPrepare.c][196]
            Entry:
                  Statement = 0x7f55ebb8fff0
                  SQL = [SELECT  USRUSRIDN FROM ASWDLIB.MKWEBUSR WHERE  USRUSRSTS = 'A'  AND USRUSRDIS <> 'Y'  AND USRDMNCDE = ?  AND UCASE(USRUSRNAM) = ...][length = 219 (SQL_NTS)]
[ODBC][33019][1393519842.759947][SQLPrepare.c][371]
            Exit:[SQL_SUCCESS]
[ODBC][33019][1393519842.760031][SQLDescribeParam.c][185]
            Entry:
                  Statement = 0x7f55ebb8fff0
                  Parameter Number = 1
                  SQL Type = 0x7fff4f9c876e
                  Param Def = 0x7fff4f9c8764
                  Scale = 0x7fff4f9c876c
                  Nullable = 0x7fff4f9c876a
[ODBC][33019][1393519842.760099][SQLDescribeParam.c][335]
            Exit:[SQL_SUCCESS]                
                  SQL Type = 0x7fff4f9c8580                
                  Param Def = 0x7fff4f9c8490                
                  Scale = 0x7fff4f9c83a0                
                  Nullable = 0x7fff4f9c82b0
[ODBC][33019][1393519842.760125][SQLBindParameter.c][217]
            Entry:
                  Statement = 0x7f55ebb8fff0
                  Param Number = 1
                  Param Type = 1
                  C Type = 1 SQL_C_CHAR
                  SQL Type = 1 SQL_CHAR
                  Col Def = 10
                  Scale = 0
                  Rgb Value = 0x7f55eba76038
                  Value Max = 0
                  StrLen Or Ind = 0x7f55ebad4028
[ODBC][33019][1393519842.760215][SQLBindParameter.c][397]
            Exit:[SQL_SUCCESS]
[ODBC][33019][1393519842.760252][SQLExecute.c][187]
            Entry:
                  Statement = 0x7f55ebb8fff0


Any ideas?
0
 
LVL 34

Accepted Solution

by:
Duncan Roe earned 500 total points
ID: 39893074
I would report at least the segfault to the developer. If you could first rebuild the driver with debug (-g3 -ggdb) and submit a stack dump at segfault time, I'm sure the developer would appreciate it.
0
 

Author Closing Comment

by:front71
ID: 39893158
Thanks Duncan you were a huge help
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Using 'screen' for session sharing, The Simple Edition Step 1: user starts session with command: screen Step 2: other user (logged in with same user account) connects with command: screen -x Done. Both users are connected to the same CLI sessio…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now