Re: It's a bad day here...

On 29/03/12 22:45, Bruce Johnson wrote:
> another odd error:
>
> [for Statement "select distinct a.cn, a.email from admins a, resourceadminaffil r, resources r2, adminaffil a2, affils a3 where a.pid = r.pid and r.resource_id = r2.resource_id and a.pid = a2.pid and a2.affil_id = a3.affil_id and a3.affil_code in ('1901','PHRM') and r2.resource_id =?" with ParamValues: :p1='149'] at /home/allwebfiles/perl/reserve2.pl line 124., referer: https://resource-scheduler.pharmacy.arizona.edu/calendar/reserve.pl
>
> Execute is being called, so what else could cause this error?
>
> Happens at:
>
> my $sq_prefapp ="select distinct a.cn, a.email from admins a, resourceadminaffil r, resources r2, adminaffil a2, affils a3 where a.pid = r.pid and r.resource_id = r2.resource_id and a.pid = a2.pid and a2.affil_id = a3.affil_id and a3.affil_code in ('$dept','$coll') and r2.resource_id =?";
>
> my $csr_prefapp= $lda->prepare($sq_prefapp) or die $DBI::errstr;
>
> my $sq_allapp ="select distinct a.cn, a.email from admins a, resourceadminaffil r, resources r2 where a.pid = r.pid and r.resource_id = r2.resource_id and r2.resource_id =?";
>
> my $csr_allapp =$lda->prepare($sq_allapp) or die $DBI::errstr;
> my ($have_pref, %approvers);
>
> foreach $i (@resources){
> 	$have_pref=0;	
> 	$csr_prefapp->execute($i) or die $DBI::errstr;<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<  I know this is being called, because the right value is in the ParamValues part of the error message

I'm not sure what you say here is correct. execute is a DBI method which will map to various other DBI methods (like bind_param) and then to various OCI calls (including describing the result). It is possible bind_param is called for you by DBI's execute but then the remainder of the execute does not complete or perhaps does not look like a select statement in the driver.

> 	while (($k, $j) = $csr_prefapp->fetchrow()){$approvers{$k}=$j;$have_pref=1;}
> 	if (!$have_pref){
> 		$csr_allapp->execute($i) or die $DBI::errstr;
> 		while (($k, $j) = $csr_prefapp->fetchrow()){$approvers{$k}=$j;}
> 	}
> }
>
>

At the end of the day the code in DBD::Oracle which issues this error is:

AV *
dbd_st_fetch(SV *sth, imp_sth_t *imp_sth){
	dTHX;
     D_imp_xxh(sth);
	sword status;
	D_imp_dbh_from_sth;
	int num_fields = DBIc_NUM_FIELDS(imp_sth);
	int ChopBlanks;
	int err;
	int i;
	AV *av;


	/* Check that execute() was executed sucessfully. This also implies	*/
	/* that dbd_describe() executed sucessfuly so the memory buffers	*/
	/* are allocated and bound.						*/
	if ( !DBIc_ACTIVE(imp_sth) ) {
		oci_error(sth, NULL, OCI_ERROR, imp_sth->nested_cursor ?
		"nested cursor is defunct (parent row is no longer current)" :
		"no statement executing (perhaps you need to call execute first)");
		return Nullav;
	}

So, as far as DBD::Oracle is concerned the statement handle is not active.

Perhaps setting ora_verbose before your code above and turning it off afterwards would help us see what is really happening.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com