Paul,
I would like to know a few things:
1) What version of DBD::ODBC are you using?
2) What version of Access are you using? What version of the ODBC driver
for Access are you using? (see the ODBC control panel applet for this)
3) Does it always stop at the same point?
4) Do you see if memory consumption is going up through the loop?
5) Can you try to re-prepare the query inside the loop to see if that
resolves it?
Please post (at least to me) a *complete* trace (including the statement
prepare calls). I know it may be big but, it's worth it. Let's start at
level two, to get a feel for what's going on.
Regards,
Jeff
>
>
> Hi all,
>
> I have a strange problem. I have a script that runs a series of queries &
> writes the results to files. The core part of this script looks like:
>
> for (my $i = 1; $i < 7; $i++) {
> process(635, 'T', $i);
> process(635, 'C', $i);
> process(635, 'A', $i);
> process(532, 'T', $i);
> process(532, 'C', $i);
> process(532, 'A', $i);
> }
>
> Now, that sub function, process($$$) checks the input parameters and
> chooses the correct statement-handle based on that. For instance:
>
> if ($_[0] == 532) {
> if ($_[1] eq 'A') {
> $sth = $sth_crosstab_all;
> }
> else {
> $sth = $sth_crosstab;
> }
> }
>
> The sub then goes ahead and does gets the data from $sth.
>
> I should mention here that the queries being run are all crosstab queries
> (MS-Access queries that aggregate data by two variables, much like a pivot
> table in Excel).
>
> The strange part is this:
> - all queries work individually
> - if I set the program running, it works for one full loop and then dies
> midway through the second loop
>
> As a result, to accomplish my for loop 6 times, I have to run the program
> six different times!
>
> The error message is:
> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
> Operation is not supported for this type of object. (SQL-S1000)(DBD:
> st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
> DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
> Operation is not supported for this type of object. (SQL-S1000)(DBD:
> st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
>
> And to further add to my confusion, lines 204-206 are:
> if ($_[1] eq 'T') {
> $sth->execute($_[2], 0);
> }
>
> And the error is coming on line 204 -- at the start of the if-statement.
>
> Finally, let me repeat -- each query works perfectly individually.
> Further, if the entire program will successfully run through one complete
> iteration of the for {} loop each time, before crashing during the second
> iteration.
>
> Any suggestions, ideas, or guidance on this would be extremely welcome!
>
> One confused programmer,
> Paul
>
>
