Re: ODBC Error - Invalid Cursor State (SQL-24000)
Hi Curt,
Curt Russell Crandall wrote:
I ran into a similar problem with Sybase. I had a statement prepared
using placeholders (a select statement) and I had to loop several times as
I processed transactions and each time through the loop I had to execute
the prepared statement. Perl and/or Sybase complained that I needed to
finish the statement handle.
maybe the reason for that was, that you had several data sets
returned by 1 statement and you had to use this way (described
in "perldoc DBD::Sybase"):
do {
while($d = $sth-fetch) {
... do something with the data
}
} while($sth-{syb_more_results});
Regards
Alex
Using finish doesn't mean you have to keep repreparing the statement
handle... according to the Cheetah book, it's a rarely used functions used
for "internal housekeeping". In my program, I prepare my frequently used
select statements in an init method and assign the statement handle to an
object attribute. I have no need to prepare the statement more than once
and explicitly finish()'ing a handle after a fetch* didn't cause any
noticable decrease in performance. So I don't think using finish is any
sort of a "nasty workaround". I would just try to remove the multiple
prepare()'s and prepare the statement up front, then just finish() after
each fetch*. Hope it helps.
Re: ODBC Error - Invalid Cursor State (SQL-24000)
No that wasn't the case here. Each select only produces 1 result row.
So, I just did a
$sth-execute() or die(...);
$href = $sth-fetchrow_hashref();
In the method... the method is executed in a loop within the caller.
With Sybase, it was necessary to insert a finish after the call to
fetchrow_hashref... from what I recall, the error happened at compile time
and didn't even attempt execution (I could be wrong about that, though).
So for me, there were 2 morals of the story here. First, in regards to
the original question, it's not necessary to keep doing a prepare just
because you call finish on the statement handle. Second, avoid Sybase
whenever possible. I've been doing most of my DBI development with
Informix and when I started working with Sybase it's been one problem
after the other... i.e. no placeholders in statements executing stored
procs, can't have multiple statement handles open on a db handle with
AutoCommit off, etc. etc. Some of this may be due to the fact that the
Sybase driver is not current (I have no choice), but the concensus I've
heard from people who've worked with Sybase and another db such as Oracle,
Informix, DB2 is that Sybase is garbage.
On Wed, 28 Mar 2001, Alexander Farber (EED) wrote:
Hi Curt,
Curt Russell Crandall wrote:
I ran into a similar problem with Sybase. I had a statement prepared
using placeholders (a select statement) and I had to loop several times as
I processed transactions and each time through the loop I had to execute
the prepared statement. Perl and/or Sybase complained that I needed to
finish the statement handle.
maybe the reason for that was, that you had several data sets
returned by 1 statement and you had to use this way (described
in "perldoc DBD::Sybase"):
do {
while($d = $sth-fetch) {
... do something with the data
}
} while($sth-{syb_more_results});
Regards
Alex
Using finish doesn't mean you have to keep repreparing the statement
handle... according to the Cheetah book, it's a rarely used functions used
for "internal housekeeping". In my program, I prepare my frequently used
select statements in an init method and assign the statement handle to an
object attribute. I have no need to prepare the statement more than once
and explicitly finish()'ing a handle after a fetch* didn't cause any
noticable decrease in performance. So I don't think using finish is any
sort of a "nasty workaround". I would just try to remove the multiple
prepare()'s and prepare the statement up front, then just finish() after
each fetch*. Hope it helps.
Re: ODBC Error - Invalid Cursor State (SQL-24000)
Curt Russell Crandall wrote: No that wasn't the case here. Each select only produces 1 result row. Are you really sure it was 1 row? So, I just did a $sth-execute() or die(...); $href = $sth-fetchrow_hashref(); I would try while ($href = $sth - fetchrow_hashref) instead. In the method... the method is executed in a loop within the caller. With Sybase, it was necessary to insert a finish after the call to fetchrow_hashref... I never seen that and will believe it only if I see some code. You don't have to call finish if you have fetched _all_ results. from what I recall, the error happened at compile time and didn't even attempt execution (I could be wrong about that, though). So for me, there were 2 morals of the story here. First, in regards to the original question, it's not necessary to keep doing a prepare just because you call finish on the statement handle. Second, avoid Sybase whenever possible. Sybase is a nice database, I don't understand why are you saying this. I've been doing most of my DBI development with Informix and when I started working with Sybase it's been one problem after the other... i.e. no placeholders in statements executing stored procs, can't have multiple statement handles open on a db handle with AutoCommit off, etc. etc. Some of this may be due to the fact that the Sybase driver is not current (I have no choice), What hinders you to install the current Perl module (DBD::Sybase or Sybperl)? You can always do that (for example in your home dir). but the concensus I've heard from people who've worked with Sybase and another db such as Oracle, Informix, DB2 is that Sybase is garbage.
Re: ODBC Error - Invalid Cursor State (SQL-24000)
On Wed, 28 Mar 2001, Alexander Farber (EED) wrote:
Curt Russell Crandall wrote:
No that wasn't the case here. Each select only produces 1 result row.
Are you really sure it was 1 row?
That has been verified. It is a small table containing only 7
rows... for now.
So, I just did a
$sth-execute() or die(...);
$href = $sth-fetchrow_hashref();
I would try
while ($href = $sth - fetchrow_hashref)
instead.
Well, there's no need for that with there being only one row... if there
is more than one row, then there is a problem because that would conflict
with a business rule.
If only one row is returned (which IS the case here), there is NO reason
why DBD::Sybase/Sybase should be issuing the warning:
DBD::Sybase::st execute failed: OpenClient message: LAYER = (1) ORIGIN =
(1) SEVERITY = (1) NUMBER = (16)
Message String: ct_param(): user api layer: external error: This routine
cannot be called while results are pending for a command that has been
setn to the server.
In the method... the method is executed in a loop within the caller.
With Sybase, it was necessary to insert a finish after the call to
fetchrow_hashref...
I never seen that and will believe it only if I see some code.
You don't have to call finish if you have fetched _all_ results.
The code is too large to post here and I'd have to clean out proprietary
names, etc. to post. It suffices to say that I prepare a statement
handle, then in a loop, that statement handle is executed and the single
row is fetched. Even when a single row is returned, the current
configuration of Sybase, Perl, and DB* causes me to something like adding
a call to finish or what you had suggested with some sort of a loop.
from what I recall, the error happened at compile time
and didn't even attempt execution (I could be wrong about that, though).
So for me, there were 2 morals of the story here. First, in regards to
the original question, it's not necessary to keep doing a prepare just
because you call finish on the statement handle. Second, avoid Sybase
whenever possible.
Sybase is a nice database, I don't understand why are you saying this.
EVERYONE I've worked with who has used Sybase dispises it. EVERY client I
have worked for that has used or is using Sybase is/has switch(ing) to
something like Oracle unless they have too much invested in Sybase to
economically switch to something else. My personal experience working
with Sybase after working predominantly with Informix has been rather
negative.
To quote my last PM who has worked with databases for 15+ years and with
DBI for 3+ years "Sybase sucks. Get a real database. Or a smarter client
:) Only suckers have fallen for Sybase in recent history; everyone else
wised up years ago." I wouldn't call anyone who uses Sybase a
"sucker" since obviously there are those that like it. MY EXPERIENCE has
been that most do not... but that's only my opinion.
I've been doing most of my DBI development with
Informix and when I started working with Sybase it's been one problem
after the other... i.e. no placeholders in statements executing stored
procs, can't have multiple statement handles open on a db handle with
AutoCommit off, etc. etc. Some of this may be due to the fact that the
Sybase driver is not current (I have no choice),
What hinders you to install the current Perl module (DBD::Sybase or
Sybperl)? You can always do that (for example in your home dir).
The client. Installing something my home directory is mostly a waste of
time since it cannot be used in production. As frustrating as it can be
not to have the latest and greatest of everything, the reality is that
many clients, particularly in the financial industry, do not (cannot
?) regularly upgrade compilers, interpreters, libraries, OS's etc.
Thanks for all of the input. I had forgotten about using do {} while
($sth-{syb_more_results} which will come in handy in my next app.
--Curt
Re: ODBC Error - Invalid Cursor State (SQL-24000)
Phil,
I ran into a similar problem with Sybase. I had a statement prepared
using placeholders (a select statement) and I had to loop several times as
I processed transactions and each time through the loop I had to execute
the prepared statement. Perl and/or Sybase complained that I needed to
finish the statement handle.
Using finish doesn't mean you have to keep repreparing the statement
handle... according to the Cheetah book, it's a rarely used functions used
for "internal housekeeping". In my program, I prepare my frequently used
select statements in an init method and assign the statement handle to an
object attribute. I have no need to prepare the statement more than once
and explicitly finish()'ing a handle after a fetch* didn't cause any
noticable decrease in performance. So I don't think using finish is any
sort of a "nasty workaround". I would just try to remove the multiple
prepare()'s and prepare the statement up front, then just finish() after
each fetch*. Hope it helps.
--Curt
On Mon, 26 Mar 2001, Phil R Lawrence wrote:
From the DBI archives I found a workaround for this error (one
which takes all the performance benefit out of placeholders and
prepared statements, however). Any ideas as to how we can lick
this and avoid the nasty workaround? Read on to see the
problem...
On 30 Jun 1998, Jeff Urlwin ([EMAIL PROTECTED]) wrote:
I am using the DBI:ODBC driver with an Access97 database. It
seems that
when calling 'execute' too many times I receive this error
"[Microsoft][ODBC Driver Manager] Invalid Cursor State
(SQL-24000) (DBD:
st_execute/SQL Execute err=-1)"
On 15 Oct 1998, Thomas Yengst ([EMAIL PROTECTED]) wrote:
This one dogged me for over a day...finally, a solution.
Just to add some experimental evidence to this problem with the
implementation or MS-SQL itself (it isn't a problem if you're
using DBI
with MySQL), if you do an explicit $sth-finish after
completing a set
of fetchrow_*, then the Invalid cursor state does not occur.
I recieve the same error in the following test script. A redo of
the script (also below) where I re-prepare the cursor every time
avoids the problem. BTW, I am using Access 2000, and my Perl and
DBI came new from ActiveState a few weeks ago.
Version with the problem:
--
use warnings;
use strict;
use DBI;
$| = 1;
DBI-trace(2,'trace.log');
my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=BLAH.mdb';
my $dbh = DBI-connect("dbi:ODBC:$DSN", '', '', {RaiseError =
1})
or die "Couldn't connect!";
my %results;
my $sth = $dbh-prepare("");
SELECT count(YOQsScheduledCode)
FROM Sites INNER JOIN
(
YOQs INNER JOIN tblYOQsScheduled
ON YOQs.[YOQ Key] =
tblYOQsScheduled.AdministeredYOQKey
)
ON Sites.[Site Key] = YOQs.[Site Key]
WHERE Sites.Identifier = '1019'
AND ScheduledDate = #2001-01-01#
AND ScheduledDate = #2001-03-26#
AND AdministeredYOQDaysVariance = ?
AND AdministeredYOQDaysVariance = ?
AND [Provider Key] = ?
my $idx = 0;
foreach (527) {
$idx++;
my $staff_num = 'Staff' . $idx;
$results{"${staff_num}ID"} = $_;
# On time count
$sth-execute(2,-2,$_);
$results{"${staff_num}OnTime"} = $sth-fetchrow_array;
# Very Early count
DIES HERE with Invalid Cursor State!
$sth-execute(-5,-365,$_);
##
$results{"${staff_num}VeryEarly"} = $sth-fetchrow_array;
# Early count
$sth-execute(-3,-4,$_);
$results{"${staff_num}Early"} = $sth-fetchrow_array;
# Late count
$sth-execute(4,3,$_);
$results{"${staff_num}Late"} = $sth-fetchrow_array;
# Very Late count
$sth-execute(365,5,$_);
$results{"${staff_num}Late"} = $sth-fetchrow_array;
}
$sth-finish;
$dbh-disconnect;
--
Version that doesn't complain:
--
use warnings;
use strict;
use DBI;
$| = 1;
DBI-trace(2,'trace.log');
my $DSN = 'driver=Microsoft Access Driver
(*.mdb);dbq=StudyManager_PHIL.mdb';
my $dbh = DBI-connect("dbi:ODBC:$DSN", '', '', {RaiseError =
1})
or die "Couldn't connect!";
my %results;
my $sql = "";
SELECT count(YOQsScheduledCode)
FROM Sites INNER JOIN
(
YOQs INNER JOIN tblYOQsScheduled
ON YOQs.[YOQ Key] =
tblYOQsScheduled.AdministeredYOQKey
)
ON Sites.[Site Key] = YOQs.[Site Key]
WHERE Sites.Identifier = '1019'
AND ScheduledDate = #2001-01-01#
AND ScheduledDate = #2001-03-26#
AND AdministeredYOQDaysVariance = ?
AND AdministeredYOQDaysVariance = ?
AND [Provider Key] = ?
my $sth;
my $idx = 0;
foreach (527) {
RE: ODBC Error - Invalid Cursor State (SQL-24000)
Phil, Because I and others use DBD::ODBC with other drivers. I have no such problem. That is why I ask the question. -Original Message- From: Phil R Lawrence [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 27, 2001 5:53 PM To: Neil Lunn; [EMAIL PROTECTED] Subject: Re: ODBC Error - Invalid Cursor State (SQL-24000) "Neil Lunn" [EMAIL PROTECTED] wrote: As the archive suggests, this is not a Perl-DBI problem but one with the Access product itself. Is it? I read the quoted messages a bit differently... and the blame seemed to remain unassigned between the DBD::ODBC implementation and MS-Access. If it's MS-Access, fine, but then why doesn't Jeff note this bug (inability to loop with a pre-prepared statement handle) in the perldoc? He is the one who originally reported the problem and it strikes me as a biggee. So What version of Access are you using? Sorry, this was buried down a few lines (MS-Access 2000). Perhaps Neil could comment if he remembers the circumstances of the issue from 1998. :-) Regards, Phil R Lawrence __ Please Note : Only the intended recipient is authorised to access or use this e-mail. If you are not the intended recipient, please delete this e-mail and notify the sender immediately. The contents of this e-mail are the writer's opinion and are not necessarily endorsed by the Gunz Companies unless expressly stated. We use virus scanning software but exclude all liability for viruses or similar in any attachment.
