Paul, I don't know ODBC, and I haven't given this a lot of thought,
but are you calling "finish" before trying to reuse a statement
handle?
After you run one of your queries, assuming these are SELECT
queries and you will reuse the same statement handle on a
subsequent loop iteration, you should be calling "finish" to
clean up and free up the statement handle so it can be used
on the next loop iteration.
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
Paul Boutros [[EMAIL PROTECTED]] wrote:
> 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
