Re: Strange happenings in ODBC

On 28/09/11 22:26, Martin J. Evans wrote:
> On 28/09/2011 22: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)
>
> ok, so we are getting somewhere in that we now know the Adjusted_Fiscal_year is actually the data returned from the database and not all those hashes above it. This is some progress ;-)
>> 4) the leading => marks inside dumper are my addition, to indicate which
>> column names differ between the two environments.
> ok, so we have the same code on both operating systems talking to the same database via 2 different (by virtue of platform) ODBC drivers, calling the same procedure which issues a select and yet you seem to be saying the result-set in one has different columns to the other.
>
> Using your notation:
>
> => 'LCTN_CODE' => '03',
> => 'PAY_ORDER_DATE' => '2011-09-22',
>
> indicates these columns are present in Windows and not on AIX - yes?
>
> and yet I'd say given your output:
>
> LCTN_CODE
> PAY_ORDER_DATE
> PRVDR_MMIS_IDNTFR
>
> are present in the Windows result but missing from the AIX result and:
>
> Line TCN
> Location
>
> are in the AIX result and not in the Windows result - there are more discrepancies than this in reality but I'm not working them all out.
>
> and yet there are 34 columns in each! This does not make sense. What is the schema for the query - i.e., in your select in the procedure look at the columns selected and compare the names with those you get - where are the discrepancies?
>
>> 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
> So, step back and compare the output on Windows and AIX with the select instead of comparing the output in AIX and Windows - what column names exist in either output that differ from what you selected.
>
> Martin

and check if you are using calculated columns you are giving the columns an alias in the select.

Martin

>> -----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
>>>
>