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++;
}
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)
I have tried using $sth->finish; at the end of each fetchrow_array but
the second execute statement returns a zero throughout the whole loop
proccess. Data is not corrupt. Working code at the bottom of this page
ruturns correct results. Trace log of problem below.
I have also tried $sth->bind_param() function and two prepare statements
ex. $sth2 but continue getting the same error message.
Trace log
------------------------------------------------
DBI::db=HASH(0x1a447e8) trace level set to 4 in DBI 1.28-nothread
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x1a42aec)~0x1a447e8
'SELECT COUNT(*) FROM [MSG] WHERE [For] LIKE ? AND [save] = ?')
dbih_setup_handle(DBI::st=HASH(0x1a42b1c)=>DBI::st=HASH(0x1a134c4),
DBD::ODBC::st, 1a42b58, Null!)
dbih_make_com(DBI::db=HASH(0x1a447e8), DBD::ODBC::st, 164)
dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql f29038680
SELECT COUNT(*) FROM [MSG] WHERE [For] LIKE ? AND [save] = ?
<- prepare= DBI::st=HASH(0x1a42b1c) at mainmsg.pl line 26 via
e:\apache\cgi-bin\mainmsg.pl line 233
-> execute for DBD::ODBC::st (DBI::st=HASH(0x1a42b1c)~0x1a134c4
'Tim W.' 0)
bind 1 <== 'Tim W.' (attribs: )
bind 1 <== 'Tim W.' (size 6/7/0, ptype 4, otype 1)
bind 1 <== 'Tim W.' (len 6/6, null 0)
bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=6.
bind 2 <== '0' (attribs: )
bind 2 <== 0 (size 1/2/0, ptype 5, otype 1)
bind 2 <== '0' (len 1/1, null 0)
bind 2: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=1.
dbd_st_execute (for sql f29038680 after)...
dbd_describe sql 29038680: num_fields=1
col 1: INTEGER len= 4 disp= 12, prec= 10 scale=0
col 1: 'Expr1000' sqltype=INTEGER, ctype=SQL_C_CHAR, maxlen=12
<- execute= -1 at mainmsg.pl line 29 via
e:\apache\cgi-bin\mainmsg.pl line 233
-> fetchrow_array for DBD::ODBC::st
(DBI::st=HASH(0x1a42b1c)~0x1a134c4)
SQLFetch rc 0
dbih_setup_fbav for 1 fields => 0x1a19d18
fetch num_fields=1
fetch col#0 Expr1000 datalen=1 displ=12
<- fetchrow_array= '7' row1 at mainmsg.pl line 30 via
e:\apache\cgi-bin\mainmsg.pl line 233
-> execute for DBD::ODBC::st (DBI::st=HASH(0x1a42b1c)~0x1a134c4
'Tim W.' 1)
bind 1 <== 'Tim W.' (attribs: )
bind 1 <== 'Tim W.' (size 6/7/6, ptype 4, otype 1)
bind 1 <== 'Tim W.' (len 6/6, null 0)
bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=6.
bind 2 <== '1' (attribs: )
bind 2 <== 1 (size 1/2/1, ptype 5, otype 1)
bind 2 <== '1' (len 1/1, null 0)
bind 2: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=1.
dbd_st_execute (for sql f29038680 after)...
dbd_error: err_rc=-1 rc=0 s/d/e: 29038680/29037304/29037136
dbd_error: SQL-24000 (native 0): [Microsoft][ODBC Driver Manager]
Invalid cursor state (SQL-24000)
dbd_error: err_rc=-1 rc=0 s/d/e: 0/29037304/29037136
dbd_error: err_rc=-1 rc=0 s/d/e: 0/0/29037136
st_execute/SQLExecute error -1 recorded: [Microsoft][ODBC Driver
Manager] Invalid cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1)
!! ERROR: -1 '[Microsoft][ODBC Driver Manager] Invalid cursor state
(SQL-24000)(DBD: st_execute/SQLExecute err=-1)'
<- execute= undef at mainmsg.pl line 32 via
e:\apache\cgi-bin\mainmsg.pl line 233
-> errstr in DBD::_::common for DBD::ODBC::st
(DBI::st=HASH(0x1a42b1c)~0x1a134c4)
<- errstr= '[Microsoft][ODBC Driver Manager] Invalid cursor state
(SQL-24000)(DBD: st_execute/SQLExecute err=-1)' at mainmsg.pl line 32
via e:\apache\cgi-bin\mainmsg.pl line 233
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1a134c4)~INNER)
<- DESTROY= undef at mainmsg.pl line 32
-> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x1a447e8)~INNER)
<- DESTROY= undef during global destruction
Working Code
------------------------------------------------
foreach my $names (@names) {
my $sqlstatement="SELECT COUNT(*) FROM [MSG] WHERE [For] LIKE '$names'
AND [save] = 0";
my $sth = $dbh->prepare($sqlstatement);
$sth->execute || die "Exec Fail " . $sth->errstr;
$row[$count]=$sth->fetchrow_array();
my $sqlstatement="SELECT COUNT(*) FROM [MSG] WHERE [For] LIKE '$names'
AND [save] = 1";
my $sth = $dbh->prepare($sqlstatement);
$sth->execute || die "Exec Fail " . $sth->errstr;
$save[$count]=$sth->fetchrow_array();
$count++;
}
