Re: execute fails in loops only

[Paul Boutros]

Hi,

Thanks for your reply.  I turned on trace(2, "trace.txt") on the advice of
one responder.  Let me summarize my code a little more clearly now -- this
is a simpler fragment than I posted previously, and still displays the
error.

sub process($$$);

$sql_a = "";
$sql_b = "";
$sth_a = $dbh->prepare($sql_a);
$sth_b = $dbh->prepare($sql_b);

for (my $i = 1; $i < 7; $i++) {
        process(532, 'A', $i);
        process(635, 'A', $i);
        }

sub process($$$) {
        my $sth;

        if ($_[0] == 532) {
                $sth = $sth_a;
                }
        else {
                $sth = $sth_b;
                }

        $sth->execute($_[0], $_[1], $_[2]);

        while (my @row = $sth->fetchrow_array() ) {
                }

        $sth->finish();

        }

So, I both finish the statement-handle at the end of the sub, and I do
instantiate a new sth variable with function-level scope each time.  I
worry about the idea of *assigning* one sth to another -- in other words
I'm not sure it's legitimate to be doing:
$sth = $sth_a;

Either way, here is my trace output from the last successfully fetched row
onwards:

    <- fetchrow_array= ( 'Rossant 9' '22.0' '37.0' '61.0' undef undef
'90.0' undef undef undef undef undef '55.0' ) [13 items] row11127 at
get_all_mm_data.pl line 231
    -> fetchrow_array for DBD::ODBC::st
(DBI::st=HASH(0x1afce68)~0x1afa32c)
    <- fetchrow_array= ( ) [0 items] row11127 at get_all_mm_data.pl line
231
    -> finish for DBD::ODBC::st (DBI::st=HASH(0x1afce68)~0x1afa32c)
    <- finish= 1 at get_all_mm_data.pl line 265
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x1afcda8)~0x1afcde4 5)
bind 1 <== '5' (attribs: )
bind 1 <== 5 (size 1/2/1, ptype 6, otype 1)
    bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=1.
    dbd_st_execute (for sql f28975368 after)...
st_execute/SQLExecute error -1 recorded: [Microsoft][ODBC Microsoft Access
Driver] Operation is not supported for this type of object.
(SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
    !! ERROR: -1 '[Microsoft][ODBC Microsoft Access Driver] Operation is
not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1)'
    <- execute= undef at get_all_mm_data.pl line 207
    -- DBI::END
    -> disconnect_all for DBD::ODBC::dr
(DBI::dr=HASH(0x1b7119c)~0x1afcc10)
    <- disconnect_all= '' at DBI.pm line 552
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1afcde4)~INNER)
    <- DESTROY= undef during global destruction
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1afa32c)~INNER)
    <- DESTROY= undef during global destruction
    -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x1afcafc)~INNER)
    <- DESTROY= undef during global destruction
    -> DESTROY in DBD::_::common for DBD::ODBC::dr
(DBI::dr=HASH(0x1afcc10)~INNER)
    <- DESTROY= undef during global destruction
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x183f214)~INNER)
    <- DESTROY= undef during global destruction
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1afcd0c)~INNER)
    <- DESTROY= undef during global destruction

And, by way of comparison here is the appropriate trace-output from one of
the queries that *did* work:

bind 1 <== '4' (attribs: )
bind 1 <== 4 (size 1/2/0, ptype 6, otype 1)
    bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=1.
    dbd_st_execute (for sql f28975368 after)...

Thanks to everybody who has replied so far -- help is greatly appreciated.
Still-confused,
Paul