Re: DBI FETCH called on non-referenced attribute

On 07/10/10 10:22, Tim Bunce wrote:
> On Wed, Oct 06, 2010 at 07:15:03PM +0100, Martin J. Evans wrote:
>>  Hi,
>>
>> Hoping someone can help me identify what might be going on here but
>> it looks to me like something in DBI has changed.
>>
>> use strict;
>> use DBI;
>>
>> my $attrs = { RaiseError => 1, PrintError => 0, AutoCommit => 1 };
>> my $h = DBI->connect("dbi:ODBC:DSN=xxx","xx","xx",
>> {odbc_SQL_ROWSET_SIZE => 2});
>>
>> This code used to work fine but now it fails with:
>>
>> Option type out of range (SQL-HY092) at
>> /home/martin/perl5/lib/perl5/i486-linux-gnu-thread-multi/DBI.pm line
>> 720.
> 
>     while ( my ($a, $v) = each %$apply) {
>         eval { $dbh->{$a} = $v } or $@ && warn $@;
>     }
> 
> Doesn't look like a FETCH to me. Umm, then again, perhaps it is.
> Try this:
> 
> -       eval { $dbh->{$a} = $v } or $@ && warn $@;
> +       eval { $dbh->{$a} = $v; 1 } or $@ && warn $@;

Stops FETCH happening (thanks) but has side effects - see below.

>> It appears FETCH is being called on odbc_SQL_ROWSET_SIZE (just my
>> example, could just as easily have been 9 for the actual ODBC
>> SQL_ROWSET_SIZE attribute) and this ends up calling
>> SQLGetConnectOption which says you cannot fetch SQL_ROWSET_SIZE. It
>> is permissible to set SQL_ROWSET_SIZE with SQLSetConnectOption but
>> not to retrieve it back.
> 
>> Why does DBI do a FETCH on the attribute when it is never retrieved
>> in the script? I don't think it used to (perhaps a long time ago).
> 
> What does trace show?

Before the change above it shows:

    >> FETCH       DISPATCH (DBI::db=HASH(0x95d2998) rc2/1 @2 g0 ima404 pid#18278) at /home/martin/perl5/lib/perl5/i486-linux-gnu-thread-multi/DBI.pm line 720
    -> FETCH for DBD::ODBC::db (DBI::db=HASH(0x95d2998)~INNER 'odbc_SQL_ROWSET_SIZE') thr#9478008
    FETCH odbc_SQL_ROWSET_SIZE
    !!dbd_error2(err_rc=-1, what=db_FETCH/SQLGetConnectOption, handles=(958a3a0,95d51f0,0)
    !SQLError(958a3a0,95d51f0,0) = (HY092, 0, [unixODBC][Easysoft][SQL Server Driver][SQL Server]Option type out of range)
    !!SQLGetConnectOption=-1 in dbd_db_FETCH
    .. FETCH DBI::db=HASH(0x95d2998) 'odbc_SQL_ROWSET_SIZE' = undef
    !! ERROR: 1 '[unixODBC][Easysoft][SQL Server Driver][SQL Server]Changed language setting to us_english. (SQL-01000)
[unixODBC][Easysoft][SQL Server Driver][SQL Server]Changed database context to 'master'. (SQL-01000) [state was 01000 now HY092]
[unixODBC][Easysoft][SQL Server Driver][SQL Server]Option type out of range (SQL-HY092)' (err#1)
    <- FETCH= undef at /home/martin/perl5/lib/perl5/i486-linux-gnu-thread-multi/DBI.pm line 720

In fact, I've known about this for ages but forgot about it - from DBD::ODBC Changes file:

 Why does level 15 tracing of any DBD::ODBC script show alot of these:
   !!DBD::ODBC unsupported attribute passed (PrintError)
   !!DBD::ODBC unsupported attribute passed (Username)
   !!DBD::ODBC unsupported attribute passed (dbi_connect_closure)
   !!DBD::ODBC unsupported attribute passed (LongReadLen)

I know see the following with a simple connect:
    !!DBD::ODBC unsupported attribute passed (PrintError)
    !!DBD::ODBC unsupported attribute passed (Username)
    !!DBD::ODBC unsupported attribute passed (dbi_connect_closure)

With your amendment the trace output still contains the "unsupported" messages from DBD::ODBC but there are no FETCH calls at all - so result.

Except make test fails with:

t/40profile.t .............. 1/60 
#   Failed test at t/40profile.t line 180.
#     Structures begin differing at:
#          $got->{Data}{}[0] = '6'   # MJE I think this is count of samples in Data->{''}
#     $expected->{Data}{}[0] = '7'

#   Failed test 'should have 9 nodes'
#   at t/40profile.t line 250.
#          got: '8'
#     expected: '9'

#   Failed test at t/40profile.t line 253.
#     Structures begin differing at:
#          $got->[1][0][0] = '2'
#     $expected->[1][0][0] = '1'
t/40profile.t .............. 44/60 
#   Failed test at t/40profile.t line 354.
#     Structures begin differing at:
#          $got->{foo}{bar}{baz}[0] = '11'
#     $expected->{foo}{bar}{baz}[0] = '12'

#   Failed test at t/40profile.t line 357.
#     Structures begin differing at:
#          $got->{foo}{ping}{pong}[0] = '11'
#     $expected->{foo}{ping}{pong}[0] = '12'

#   Failed test '$_ should contain statement'
#   at t/40profile.t line 364.
#     Structures begin differing at:
#          $got->{}[0] = '6'
#     $expected->{}[0] = '7'

#   Failed test at t/40profile.t line 423.
#          got: 'foo > DESTROY > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > STORE > baz: 0.0s / 5 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > connected > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > execute > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > fetchrow_hashref > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > finish > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > prepare > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# '
#     expected: 'foo > DESTROY > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > FETCH > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > STORE > baz: 0.0s / 5 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > connected > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > execute > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > fetchrow_hashref > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > finish > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# foo > prepare > baz: 0.0s / 1 = 0.0s avg (first 0.0s, min 0.0s, max 0.0s)
# '
# Looks like you failed 7 tests of 60.
t/40profile.t .............. Dubious, test returned 7 (wstat 1792, 0x700)
Failed 7/60 subtests 

so there is a side effect. It appears there is 1 less count each time.

> Tim.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com