sybperl-l Archive

From: Morgan Curley <mcurley at newscorp dot com>
Subject: Stored Procedures Problem
Date: Jan 25 1999 3:23PM

Michael Peppler wrote:
>>>>> "Morgan" == Morgan Curley  writes:
>Morgan> Hey folks, I am writing a CGI that needs to retrieve lots of
>> records totaled and broken down by date. I am using a stored
>> procedure that selects all the dates then opens a cursor and
>> selects the ind pages by day. Does this sound like something
>> Sybase::DBlib doesn't support. I get no results DBCOUNT=-1 and
>> DBROWS=0. If I run this through isql I get plenty of info
>> returned.

>This should definitely be supported. Could we see the perl code (and
>maybe the stored proc) that you are using?
>
>Michael
>-- 
>Michael Peppler         -||-  Data Migrations Inc.
Okay, here is the subrotuine I call this from($debug logs my SQL statements
and results). The stored procedure is below also.

sub viewsummary {
	my($self)		= shift;
	my($type)		= shift;
	my($format)		= shift;
	my($precision)	= shift || 3;
	my($barlength)	= shift || 205;

	my($dbh)	= $self->{'DBHANDLE'};
	my($debug)	= $self->{'DEBUGHANDLE'};

	my($table, $total,  $trash, @trash, %summary);

	if(lc($type) eq 'ref'){
		$table = 'referrals';
	} else {
		$table = 'requests';
	} 

	$summary{'total'}	= $self->sum_total($table);
	
	my($cmd)	 = "--EXECUTE referrals_by_date--\n";
	$cmd		.= qq/referrals_by_date		/;
	
	$cmd 		= $debug->sql_cmd($cmd);

	$trash 	= $dbh->dbcmd($cmd);
	$trash 	= $dbh->dbsqlexec;
	$trash 	= $dbh->dbresults;

	while(@trash 	= $dbh->dbnextrow()){
		$summary{'type'} 		= $table;
		$summary{'hitcount'}		= $trash[2];
		$summary{'servername'}	= $trash[4];
		$summary{'webpage'}		= $trash[6];
		$summary{'decimal'}		= sprintf("%0.${precision}f",
$summary{'hitcount'}/$summary{'total'});
		if($summary{'decimal'}){
			$summary{'pct_raw'}		= $summary{'decimal'}*100;
			$summary{'percentage'}	= $summary{'pct_raw'}.'%';
		}

		$summary{'barlength'} = $barlength * $summary{'decimal'};

		eval $format;

		if($@){
			$debug->_print("Eval Error: $@\n");
		}
	}
	$debug->reset_buffer($dbh);
	return 1;
}

Stored procedure:
CREATE PROC referrals_by_date  
	@ref_serverid int = 0,
	@ref_pageid int = 0 
AS
DECLARE @month 	int
DECLARE @day 	int
DECLARE @year 	int

DECLARE @month2 varchar(2)
DECLARE @day2 	varchar(2)
DECLARE @year2 	varchar(4)

DECLARE @date varchar(10)
DECLARE @date2 varchar(22)

DECLARE @total 	int
DECLARE referrals_by_date CURSOR FOR 
	SELECT DISTINCT 
		DATEPART(mm, timestamp),
		DATEPART(dd, timestamp),
		DATEPART(yy, timestamp) 
	FROM transactions 
	ORDER BY 
		DATEPART(yy, timestamp), 
		DATEPART(dd, timestamp),
		DATEPART(mm, timestamp)
	DESC

OPEN referrals_by_date 
FETCH NEXT FROM referrals_by_date INTO @month, @day, @year 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	SELECT @month2=CONVERT(varchar(2), @month) 
	SELECT @day2=CONVERT(varchar(2), @day) 
	SELECT @year2=CONVERT(varchar(4), @year) 

	SELECT @date=CONVERT(varchar(10), @month2+'/'+@day2+'/'+@year2) 
	SELECT @date2=CONVERT(varchar(22), @month2+'/'+@day2+'/'+@year2+'
23:59:59:59') 

	IF @ref_pageid = 0
	 BEGIN 
		SELECT @total=count(*) 
		FROM transactions 
		WHERE timestamp BETWEEN @date
			AND @date2
	
		SELECT 'Total'=@total, * 
		FROM transactions 
		WHERE timestamp BETWEEN @date
			AND @date2
	 END 
	ELSE 
	 BEGIN 
		SELECT @total=count(*) 
		FROM transactions 
		WHERE ref_serverid=@ref_serverid 
			AND ref_pageid=@ref_pageid 
			AND timestamp BETWEEN @date
				AND @date2
	
		SELECT 'Total'=@total, * 
		FROM transactions 
		WHERE ref_serverid=@ref_serverid 
			AND ref_pageid=@ref_pageid 
			AND timestamp BETWEEN @date
				AND @date2
	 END

	FETCH NEXT FROM referrals_by_date INTO @month, @day, @year
END
DEALLOCATE referrals_by_date 
GO


Thanks for any help.

Morgan