RE: Strange happenings in ODBC

On Wed, September 28, 2011 14:10, Jeff Tate wrote:
> 1) the versions of DBI and ODBC are the same on Windows and AIX
> 2) the program is identical on Windows and AIX. I develop on Windows and
> move to AIX for production. The two output fragments are from an identical
> call to the same stored procedure on the same database with the same
> parameters
> 3) the dumper output is nested in memory. The SP returns variant record
> types in a common column set (sorry, I have been saying tags for what are
> actually columns in the result set) and I have to juggle and nest them as
> I
> can't do the value-added work till I have all records returned. The actual
> columns returned are the hash that starts with 'Adjusted_Fiscal_Year' => '
> ' (I have removed certain sensitive data)
> 4) the leading => marks inside dumper are my addition, to indicate which
> column names differ between the two environments.
> 5) I am happy to share the code, but I am not sure what to send. At the
> point that these hashes are created, all that has run is the
> 'dbi->connect'
> and the 'sth->fetchrow_hashref' loop
> 6) the SP itself does a lot of complicated filters, case statements, joins
> across a very large set of transaction records.
>
> 7) not sure what you mean by a reproducible problem - it happens every
> time
> I run the program in the pair of environments, but I'm sure that's not
> what
> you mean.
>
> Thanx
>
> -----Original Message-----
> From: Martin J. Evans [mailto:[email protected]]
> Sent: Wednesday, September 28, 2011 4:51 PM
> To: [email protected]
> Cc: Jeff Tate
> Subject: Re: Strange happenings in ODBC
>
> On 28/09/2011 21:07, Jeff Tate wrote:
>> Thanx for the offer. What additional information would aid you in aiding
> me?
>> (PS - did the attachment make it through - it showed the specific tag
>> sets (columns really) returned.
>>
>> Jeff Tate
> Jeff, please keep things on list as other might have input too.
>
> Can you confirm each of the following:
>
> o you are using the same version of DBI and DBD::ODBC on Windows and AIX?
> o you are calling a procedure in the same database from each platform with
> the same arguments o the procedure issues the same select from AIX and
> Windows o the data being queried has not changed between the call to the
> procedure from AIX and Windows o you are getting different result-sets on
> each platform
>
> Then I have an issue with the Dumper output you sent as it has it is not
> of
> the form you'd typically get back from a database i.e., it is a hashref
> whose keys are hashrefs whose keys are hashrefs whose keys are hashrefs
> whose keys are values. How can this be when a result-set has rows and
> columns (2):
>
> #
> # Windows ODBC
> #
> $VAR1 = {
>    'SUMMARY' => {
>      '2' => {
>        '0004' => {
>           'Adjusted_Fiscal_Year' => '         ',
>
> Can you show us the Perl code your running, give us some idea of the
> procedure (what it does and what you expect it to return or cursors it
> opens) and what you passed to Dumper.
>
> Then there is:
>
>                                    '0004' => {
>                                                'Adjusted_Fiscal_Year' =>
> '         ',
>                                                'Adjustment_Count' => '3',
>                                                'Billing_Provider_ID' =>
> '',
>                                                'Billing_Provider_Type'
> => '',
>                                                'CLAIM_LINE_TCN' => '',
>                                                'COUNTY_NAME' =>
> 'KALAMAZOO',
>                                                'City' => 'KALAMAZOO',
>                                                'Credit_Dollars' => '0.00',
>                                                'Debit_Dollars' =>
> '2515.23',
>                                                'EMailAddress' => undef,
>                                                'FAX' => undef,
>                                                'Fiscal_Years' =>
> '2010-2011',
>                                                'GA_MIP_Total' => '0.00',
>                                                'GA_Non_MIP_Total' =>
> '0.00',
>                                                'GA_Reason' => 'Old
> Invoices',
>                                                'GA_Total' => '0.00',
>                                                'Indx' => '     ',
>                                            =>  'LCTN_CODE' => '03',
>                                            =>  'PAY_ORDER_DATE' =>
> '2011-09-22',
>
> which does not even seem to be valid Data::Dumper output to me.
>
> Also you keep referring to tags and I've no idea what you mean by that.
>
> Can you reduce the problem and describe it in a way which I/we can help
> you?
> That is, reduce the problem to something we don't need inside knowledge to
> understand and even better into a reproducible problem (although I
> appreciate this can be difficult and I've not got teradata anyway).
>
> Martin
>
>> -----Original Message-----
>> From: Martin J. Evans [mailto:[email protected]]
>> Sent: Wednesday, September 28, 2011 3:50 PM
>> To: Jeff Tate
>> Cc: [email protected]
>> Subject: Re: Strange happenings in ODBC
>>
>> On 28/09/2011 20:41, Jeff Tate wrote:
>>> I am developing an app that pulls data from a Teradata data-server
>>> through
>> DBI, DBD::ODBC. It is developed on a Win32 platform, but targeted for
>> an AIX platform.
>>> In debugging a difference between the two platforms (after rigorous
>>> code
>> identity policing) I used Data::Dumper to print the in-memory image of
>> the working data after running a stored procedure and then running
>> 'fetchrow_hashref'. I have attached an edited file that shows:
>>> .One tag retrieved only on AIX
>>>
>>> .Two tags named differently across the Win and AIX instances
>>>
>>> I have looked at obvious sources and not seen any warnings about
>>> this. Can
>> anybody tell me if this is a known issue, and if so whether there is a
>> canonical way of handling it.
>>> Thanx
>>>
>>>
>> I'm maintain DBD::ODBC and I'm happy to try and help but with all
>> respect I don't even know where to start with the information you have
>> provided. You are calling a procedure which generates some sort of
>> result-set and it returns different results when run to the same
>> database with 2 different ODBC drivers on Windows and AIX at the same
>> time? The possibilities are endless. If you care to narrow the problem
>> down to something more specific someone who knows nothing about your
>> systems can look in to I'll take another look.
>>
>> Martin
>>
I'm coming in late, but one thing to remember going from Windows to a
"Unix like" environment is that the case of table names matters in Unix
and its variants.  This can cause issues.
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154