RE: execute fails in loops only
1) What version of DBD::ODBC are you using?
I picked up off ActiveState -- the $id line is:
# $Id: ODBC.pm,v 1.12 1998/08/14 19:29:50 timbo Exp $
Actually, that's not the version, in ODBC.pm, there's a VERSION=. No
big deal, though, since you are getting from ActiveState, it's 0.28. Can
you please try to use the latest from MY repository, listed in the FAQ (see
my signature at the end).
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)
This is Access XP:
Microsoft Access 2002 (10.3409.3501) SP-1
Due to other software conflicts I cannot upgrade to SP-2, but I also
found this error on core Access 2002, without any Service Packs
ODBC Driver Version: 4.00.6019.00
The ODBC Core DLLs are all version: 3.520.9030.0
Except for:
Control Panel Device (odbccp32.cpl) 3.520.7713.0
Localized Resource DLL (odbcint.dll) 3.520.7713.0
This is newer than what I have.
3) Does it always stop at the same point?
Not quite. The core for-loop is:
for (my $i = 0; $i 7; $i++) {
process(635, 'T', $i);
process(532, 'T', $i);
process(635, 'C', $i);
process(532, 'C', $i);
process(635, 'A', $i);
process(532, 'A', $i);
}
If I run it like that, it *always* stops after 8 queries are processed
(i.e. one iteration of i and 2/6 queries on the second iteration of $i).
It doesn't matter if I start at $i = 0 or $i=5. It also doesn't matter
which order I place these queries in.
It's *always* the 9th query, no matter what order these are in the loop? In
the example above, process(635, 'C', $i) fails the second time around, every
time?
HOWEVER, if I restrict the loop to just two queries:
for (my $i = 0; $i 7; $i++) {
process(635, 'A', $i);
process(532, 'A', $i);
}
Then it will crash immediately following one loop iteration. So, in this
case it only runs *two* queries, not eight, before crashing.
Ok -- then, let's keep it simple and send me the trace (level 8) for this
one.
5) Can you try to re-prepare the query inside the loop to see if that
resolves it?
I will try post.
How did this go? Not what I want, but I'm just curious. If this works,
also post a level 8 trace (or e-mail it directly to me).
Thanks,
Jeff
=
See the DBI FAQ at www.xmlproj.com/cgi/fom.cgi
RE: execute fails in loops only
Hi Jeff,
Sorry for the slow replies, and thanks for your help here.
1) What version of DBD::ODBC are you using?
I picked up off ActiveState -- the $id line is:
# $Id: ODBC.pm,v 1.12 1998/08/14 19:29:50 timbo Exp $
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)
This is Access XP:
Microsoft Access 2002 (10.3409.3501) SP-1
Due to other software conflicts I cannot upgrade to SP-2, but I also
found this error on core Access 2002, without any Service Packs
ODBC Driver Version: 4.00.6019.00
The ODBC Core DLLs are all version: 3.520.9030.0
Except for:
Control Panel Device (odbccp32.cpl) 3.520.7713.0
Localized Resource DLL (odbcint.dll) 3.520.7713.0
3) Does it always stop at the same point?
Not quite. The core for-loop is:
for (my $i = 0; $i 7; $i++) {
process(635, 'T', $i);
process(532, 'T', $i);
process(635, 'C', $i);
process(532, 'C', $i);
process(635, 'A', $i);
process(532, 'A', $i);
}
If I run it like that, it *always* stops after 8 queries are processed
(i.e. one iteration of i and 2/6 queries on the second iteration of $i).
It doesn't matter if I start at $i = 0 or $i=5. It also doesn't matter
which order I place these queries in.
HOWEVER, if I restrict the loop to just two queries:
for (my $i = 0; $i 7; $i++) {
process(635, 'A', $i);
process(532, 'A', $i);
}
Then it will crash immediately following one loop iteration. So, in this
case it only runs *two* queries, not eight, before crashing.
4) Do you see if memory consumption is going up through the loop?
As best as I can tell, no. These are fairly lengthy queries so I'm not
100% sure. There is some memory change just because of the query itself,
but that seems to be pretty constant throughout the loop.
5) Can you try to re-prepare the query inside the loop to see if that
resolves it?
I will try post.
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.
I'm attaching traces for both single-dbh and multiple-dbh runs. I have
removed the code that would actually retrieve rows to minimize the
trace-size. The crash occurs whether or not they are present, but if they
would help just let me know. Trace is at level 2.
snip previous messages
DBI 1.28-nothread dispatch trace level set to 2
- prepare for DBD::ODBC::db (DBI::db=HASH(0x1afca68)~0x1afc990 '
TRANSFORM IIf( Avg(VIEW_GPR_DATA_MM_FILTERED.S532_MEDIAN) 0,
Avg(VIEW_GPR_DATA_MM_FILTERED.S532_MEDIAN),
NULL) AS AvgOfMedian
SELECT VIEW_GPR_DATA_MM_FILTERED.DAUGHTER
FROMVIEW_GPR_DATA_MM_FILTERED
WHERE VIEW_GPR_DATA_MM_FILTERED.BARCODE IN (
SELECT OCI_ARRAYS.BARCODE
FROMOCI_ARRAYS,
QRY_ARRAYS_TO_ANALYZE
WHERE OCI_ARRAYS.BARCODE = QRY_ARRAYS_TO_ANALYZE.BARCODE
AND ...')
dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql f28973256
TRANSFORM IIf( Avg(VIEW_GPR_DATA_MM_FILTERED.S532_MEDIAN) 0,
Avg(VIEW_GPR_DATA_MM_FILTERED.S532_MEDIAN),
NULL) AS AvgOfMedian
SELECT VIEW_GPR_DATA_MM_FILTERED.DAUGHTER
FROMVIEW_GPR_DATA_MM_FILTERED
WHERE VIEW_GPR_DATA_MM_FILTERED.BARCODE IN (
SELECT OCI_ARRAYS.BARCODE
FROMOCI_ARRAYS,
QRY_ARRAYS_TO_ANALYZE
WHERE OCI_ARRAYS.BARCODE = QRY_ARRAYS_TO_ANALYZE.BARCODE
AND OCI_ARRAYS.STRAIN_ID = ?
AND OCI_ARRAYS.IS_CONTROL = ?)
GROUP BYVIEW_GPR_DATA_MM_FILTERED.DAUGHTER
PIVOT VIEW_GPR_DATA_MM_FILTERED.BARCODE
- prepare= DBI::st=HASH(0x1afca80) at errortest.pl line 78
- prepare for DBD::ODBC::db (DBI::db=HASH(0x1afca68)~0x1afc990 '
TRANSFORM IIf( Avg(VIEW_GPR_DATA_MM_FILTERED.S635_MEDIAN) 0,
Avg(VIEW_GPR_DATA_MM_FILTERED.S635_MEDIAN),
NULL) AS AvgOfMedian
SELECT VIEW_GPR_DATA_MM_FILTERED.DAUGHTER
FROMVIEW_GPR_DATA_MM_FILTERED
WHERE VIEW_GPR_DATA_MM_FILTERED.BARCODE IN (
SELECT OCI_ARRAYS.BARCODE
FROMOCI_ARRAYS,
QRY_ARRAYS_TO_ANALYZE
WHERE OCI_ARRAYS.BARCODE = QRY_ARRAYS_TO_ANALYZE.BARCODE
AND ...')
dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql f28974288
TRANSFORM IIf( Avg(VIEW_GPR_DATA_MM_FILTERED.S635_MEDIAN) 0,
Avg(VIEW_GPR_DATA_MM_FILTERED.S635_MEDIAN),
RE: execute fails in loops only
I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. It shouldn't matter, though, as, from looking at how he's using it, the implicit finish should be called, thus only one statement at a time would be running. Indeed, using multiple $dbh's didn't make any difference. Also, the DBI documentation lends the impression that -finish() shouldn't generally be called explicitly. I have found it necessary for statements where you know just one row will be returned (i.e. count(*)) and aren't looping through the entire $sth. Otherwise, I thought I shouldn't be using it, though?
Re: execute fails in loops only
On Fri, 22 Nov 2002 14:21:48 -0500 (EST) Paul Boutros [EMAIL PROTECTED] wrote: Indeed, using multiple $dbh's didn't make any difference. Also, the DBI documentation lends the impression that -finish() shouldn't generally be called explicitly. I have found it necessary for statements where you know just one row will be returned (i.e. count(*)) and aren't looping through the entire $sth. Otherwise, I thought I shouldn't be using it, though? Too bad that multiple database handles didn't help. It was kind of a shot in the dark since I don't use ODBC or Access. Fetching a single row is one of the cases where finish() is useful. Unless you attempt to fetch a row after the last one, DBI doesn't know the statement is finished unless you tell it. In that case it thinks the handle is still active when you disconnect. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
execute fails in loops only
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
Re: execute fails in loops only
On Mon, 18 Nov 2002 12:44:44 -0500 (EST), Paul Boutros wrote:
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.
Just an aside: you could use a hash containing the statement handles.
For example:
$sth{532}{A} = $sth_crosstab_all;
Since you seem to want a fallback, you could do:
$sth{532}{'*'} = $sth_crosstab;
Thus:
my $sth = $sth{0+$_[0]}{$_[1]} || $sth{0+$_[0]}{'*'};
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
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.
And to further add to my confusion, lines 204-206 are:
if ($_[1] eq 'T') {
$sth-execute($_[2], 0);
}
Could it be that Access doesn't like two such queries running at the
same time? Thus, perhaps you should do
$sth-finish;
at the end of your loop.
--
Bart.
Re: execute fails in loops only
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
Re: execute fails in loops only
On Mon, 18 Nov 2002 13:49:27 -0500 (EST) Paul Boutros [EMAIL PROTECTED] wrote: 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); I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); 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; The finish() shouldn't be necessary since you are looping until all rows are fetched. finish() is basically a hint to DBI that you won't be fetching more rows unless you execute() the handle again. $sth = $sth_a just assigns the same handle to another variable. It is perfectly legitimate, both variables contain the same value which is a reference to the statement information. Since $sth goes out of scope as soon as the procedure returns, it has no impact outside the procedure. Any operations performed on the statement through it are indistinguishable from those done using $sth_a. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: execute fails in loops only
On Mon, 18 Nov 2002 11:11:47 -0800 (PST), Michael A Chase wrote: I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. -- Bart.
Re: execute fails in loops only
On Mon, 18 Nov 2002 20:46:09 +0100 Bart Lateur [EMAIL PROTECTED] wrote: On Mon, 18 Nov 2002 11:11:47 -0800 (PST), Michael A Chase wrote: I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. Why? Database connections aren't that expensive and keeping the statement handles avoids having to re-prepare() the SQL. (We still haven't heard whether using multiple database handles helped.) -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: execute fails in loops only
On Mon, 18 Nov 2002 11:11:47 -0800 (PST), Michael A Chase wrote: I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. It shouldn't matter, though, as, from looking at how he's using it, the implicit finish should be called, thus only one statement at a time would be running. Regards, Jeff = See the DBI FAQ at www.xmlproj.com/cgi/fom.cgi
RE: execute fails in loops only
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
