On Fri, 13 Dec 2002 12:10:50 -0500 Josh Bailey <[EMAIL PROTECTED]> wrote:
All four of you emails arrived at the same time. There probably was an
outage at one of your company's mail servers.
> I am trying to write more efficient code for my database program but am
> having trouble. Here is the code I am trying to use. Working code is
> located at the bottom of this page.
>
> Code Rewrite
> ------------------------------------------------
> my $sth = $dbh->prepare('SELECT COUNT(*) FROM [MSG] WHERE [For] LIKE ?
> AND [save] = ?') || die "Prep Fail: " . $dbh->errstr;
>
> foreach my $names (@names) {
> $sth->execute($names,0) || die "Exec Fail " . $sth->errstr;
> $row[$count]=$sth->fetchrow_array();
>
> $sth->execute($names,1) || die "Exec Fail " . $sth->errstr;
> $save[$count]=$sth->fetchrow_array();
> $count++;
> }
I suggest something like the following. I don't know why you are
getting the invalid cursor state, but this should allow ODBC to finish
each set by itself. It also uses only one execute() per name instead
of two.
# Untested
# Assumes you only care about [save] of 0 or 1
my ( $save, $count, $i, @row, @save );
my $sth = $dbh -> prepare( <<SQL ) or die "Prep Fail: $DBI::errstr";
SELECT [save], COUNT(*)
FROM [MSG]
WHERE [For] LIKE ? AND [save] IN ( 0, 1 )
GROUP BY [save]
SQL
$sth -> bind_columns( \( $save, $count ) );
$i = 0; # $i matches element number in @names
foreach $name ( @names ) {
$sth -> execute( $name ) or die "Exec Fail: $DBI::errstr";
$row[$i] = $save[$i] = 0;
# The loop always uses all rows from the SELECT
while ( $sth -> fetch ) {
if ( $save == 0 ) { $row[$i] = $count; }
else { $save[$i] = $count; }
}
++$i;
}
>
> This is the error message I get when I try to run the code
> Error Message
> ------------------------------------------------
> [Microsoft][ODBC Driver Manager] Invalid cursor state (SQL-24000)(DBD:
> st_execute/SQLExecute err=-1)
--
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.
