Just did some hunting, found this: http://www.mail-archive.com/[EMAIL PROTECTED]/msg13240.htm l
Seems some DBDs can only have 1 statement handle per connection :( The link discusses some work arounds. J-T MacNeil Design Team Member, Connect 24 Division Digital Security Controls Phone: (905) 760-3000 x7318 e-Mail: [EMAIL PROTECTED] > -----Original Message----- > From: Adam Peterson [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 30, 2003 3:18 PM > To: dbi users > Subject: Re: Help With inner queries - JT is right, but i'm still not > out of the woods > Importance: Low > > > > > > thanks for all the help. i've changed various things and although my > > program is working slightly better there are still issues. > > > > i renamed the $sth to prevent from going out of scope. i've > also tried > > using placeholders with no success. here's the latest code > and errors. > > > > still stumped.. > > -adam > > > > errors: > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server > > Driver]Connection is b > > usy with results for another hstmt (SQL-HY000)(DBD: > > st_execute/SQLExecute > > err=-1 > > ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 987. > > DBD::ODBC::st fetchrow_array failed: (DBD: no select > statement currently > > executi > > ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 989. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server > > Driver]Connection is b > > usy with results for another hstmt (SQL-HY000)(DBD: > > st_execute/SQLExecute > > err=-1 > > ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1001. > > DBD::ODBC::st fetchrow_array failed: (DBD: no select > statement currently > > executi > > ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1005. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server > > Driver]Connection is b > > usy with results for another hstmt (SQL-HY000)(DBD: > > st_execute/SQLExecute > > err=-1 > > ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 987. > > DBD::ODBC::st fetchrow_array failed: (DBD: no select > statement currently > > executi > > ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 989. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server > > Driver]Connection is b > > usy with results for another hstmt (SQL-HY000)(DBD: > > st_execute/SQLExecute > > err=-1 > > ) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1001. > > DBD::ODBC::st fetchrow_array failed: (DBD: no select > statement currently > > executi > > ng err=-1) at C:\Inetpub\wwwroot\cgi-bin\payorlist.cgi line 1005. > > > > > > > > code: > > > > sub sql_custtracker > > { > > > > my @list = (); > > my $color = ""; > > $cnt = 1; > > my $where = ""; > > $where = $_[0]; > > my $sql = ""; > > my $sql2 = ""; > > my $sql3 = ""; > > my $group = ""; > > my $payorid = ""; > > my $multistateid = ""; > > my $customername = ""; > > > > #if ($where =~ /\w+/) > > if ($where) > > { > > $sql = " > > > > select txnid, > > datestarted, > > ISNULL(projectlead, ' ') as projectlead, > > ISNULL(status, ' ') as status, > > ISNULL(biller, ' ') as biller, > > ISNULL(formatstatus, ' ') as formatstatus, > > ISNULL(unionid, '0') as unionid, > > ISNULL(customercase, ' ') as customercase, > > ISNULL(enrollmentstatus, ' ') as > enrollmentstatus, > > ISNULL(routestatus, ' ') as routestatus, > > ISNULL(complianceerrors, ' ') as > complianceerrors, > > mandateid, > > custid, > > ISNULL(formatname, ' ') as formatname, > > expectedstartdate, > > expectedcompdate, > > ISNULL(testfilename, ' ') as testfilename, > > testfiledate, > > ISNULL(trainingstatus, ' ') as > trainingstatus, > > ISNULL(kickoffstatus, ' ') as > kickoffstatus, > > ISNULL(websitestatus, ' ') as > websitestatus, > > ISNULL(paymentverification, ' ') > as paymentverification, > > ISNULL(productioncontrol, ' ') as > productioncontrol, > > ISNULL(fileacknowledged, ' ') as > fileacknowledged, > > ISNULL(customercontact, ' ') as > customercontact > > from customertracker > > $where > > "; > > } > > else > > { > > $sql = " > > > > select txnid, > > datestarted, > > ISNULL(projectlead, ' ') as projectlead, > > ISNULL(status, ' ') as status, > > ISNULL(biller, ' ') as biller, > > ISNULL(formatstatus, ' ') as formatstatus, > > ISNULL(unionid, '0') as unionid, > > ISNULL(customercase, ' ') as customercase, > > ISNULL(enrollmentstatus, ' ') as > enrollmentstatus, > > ISNULL(routestatus, ' ') as routestatus, > > ISNULL(complianceerrors, ' ') as > complianceerrors, > > mandateid, > > custid, > > ISNULL(formatname, ' ') as formatname, > > expectedstartdate, > > expectedcompdate, > > ISNULL(testfilename, ' ') as testfilename, > > testfiledate, > > ISNULL(trainingstatus, ' ') as > trainingstatus, > > ISNULL(kickoffstatus, ' ') as > kickoffstatus, > > ISNULL(websitestatus, ' ') as > websitestatus, > > ISNULL(paymentverification, ' ') > as paymentverification, > > ISNULL(productioncontrol, ' ') as > productioncontrol, > > ISNULL(fileacknowledged, ' ') as > fileacknowledged, > > ISNULL(customercontact, ' ') as > customercontact > > from customertracker > > "; > > } > > > > print FILE "$sql\n"; > > > > $sth = $dbh->prepare($sql); > > $sth->execute(); > > > > while( my @data = $sth->fetchrow_array) > > { > > my $txnid = $data[0]; > > my $datestarted = $data[1]; > > my $projectlead = $data[2]; > > #print "$projectlead\n"; > > my $status = $data[3]; > > my $biller = $data[4]; > > my $formatstatus = $data[5]; > > my $unionid = $data[6]; > > my $customercase = $data[7]; > > my $enrollmentstatus = $data[8]; > > my $routestatus = $data[9]; > > my $complianceerrors = $data[10]; > > my $mandateid = $data[11]; > > my $custid = $data[12]; > > my $formatname = $data[13]; > > my $expectedstartdate = $data[14]; > > my $expectedcompdate = $data[15]; > > my $testfilename = $data[16]; > > my $testfiledate = $data[17]; > > my $trainingstatus = $data[18]; > > my $kickoffstatus = $data[19]; > > my $websitestatus = $data[20]; > > my $paymentverification = $data[21]; > > my $productioncontrol = $data[22]; > > my $fileacknowledged = $data[23]; > > my $customercontact = $data[24]; > > > > ###inner queries for custid, payorid, and multistate > > > > $sql2 = "select [group], customer from customer > where custid = > > '$custid'"; > > #$sql2 = "select [group], customer from > customer where custid = ?"; > > > > my $sth2 = $dbh->prepare($sql2); > > #$sth2->execute($custid); > > $sth2->execute(); > > > > while( my @row = $sth2->fetchrow_array) > > { > > $group = $row[0]; > > #print "$group\n"; > > $customername = $row[1]; > > #print "$customername\n"; > > } > > > > $sql3 = "select payorid, multistateid from > payor where payid = (select > > payid from payortxn where mandateid = '$mandateid')"; > > #$sql3 = "select payorid, multistateid from > payor where payid = > > (select > > payid from payortxn where mandateid = ?)"; > > > > my $sth3 = $dbh->prepare($sql3); > > $sth3->execute(); > > #$sth->execute($mandateid); > > > > > > while( my @row = $sth3->fetchrow_array) > > { > > $payorid = $row[0]; > > #print "here"; > > print "$payorid\n"; > > > > $multistateid = $row[1]; > > print "$multistateid\n"; > > } > > > > $multistateid = "<a > > href=http://eggpayorteam/cgi-bin/payorlist.cgi?msid="; . > $multistateid . > > "&stat=payor>" . $multistateid . "</a>"; > > > > if ($cnt%2 == 0) > > { > > $color = "bgcolor=#eeeecc"; > > } > > else > > { > > $color = "bgcolor=#ffffff"; > > } > > > > > > > > my %rows = ( num=>$cnt, > > color=>$color, > > datestarted=>$datestarted, > > projectlead=>$projectlead, > > status=>$status, > > biller=>$biller, > > formatstatus=>$formatstatus, > > unionid=>$unionid, > > customercase=>$customercase, > > enrollmentstatus=>$enrollmentstatus, > > routestatus=>$routestatus, > > complianceerrors=>$complianceerrors, > > formatname=>$formatname, > > expectedstartdate=>$expectedstartdate, > > expectedcompdate=>$expectedcompdate, > > custid=>$customername, > > group=>$group, > > payorid=>$payorid, > > multistate=>$multistateid, > > testfilename=>$testfilename, > > testfiledate=>$testfiledate, > > trainingstatus=>$trainingstatus, > > kickoff=>$kickoffstatus, > > websitestatus=>$websitestatus, > > customercontact=>$customercontact, > > ); > > > > > > > > push (@list, \%rows); > > > > > > > > > $cnt++; > > > > } > > > > return @list; > > > > } > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com >
