sybperl-l Archive

From: Knut Behrends <knb at gfz-potsdam dot de>
Subject: Re: Column headings question
Date: Oct 8 2009 8:50AM

Louis Proyect schrieb:
> Is there an easier way to access column headings than this:
> 
> @res=$dbh->ct_sql("exec stored_procedure");
> 
> @res2=$dbh->ct_sql("sp_help table_name"); ---> this is the table that
> maps to the stored procedure results
> 
> $cntr = 0;
> 
> foreach $row (@res2)   # Loop through each row
> {
>   print "@$row[0]: ";
>   print "$res[0][$cntr]\n";
>   $cntr++;
> }
> 
> Most importantly, this only works if the stored procedure is returning
> results that map to a single table. If the results are from joined
> tables, you are out of luck.
> 
> 


Do you want to do something like this code fragment does?
(I've thrown this together from one of our perl modules)

my $sql = "exec some_proc args" ;

	$sth = $dbh->prepare($sql);
	$sth->execute;

	# find column names and types (as numeric codes)
	$column = $sth->{NAME};
	$type   = $sth->{TYPE};

# fetch data
	$data = $sth->fetchall_arrayref( {} );
	

# and later
for ( my $r = 0 ; $r < @$data ; $r++ ) {
	my @line = ();
	for ( my $i = 0 ; $i < @$column ; $i++ ) {
			# do something with the data
                        #my $val = $data->[$r]{ $column->[$i] };
			# e.g. push @line, $val

	}
}
-- 
____________________________________________________________________________

Knut Behrends                         Phone: +49 (0) 331 288 1688
Potsdam 14473                         KeyID: 0xF22CACEF (PGP Public Key)
____________________________________________________________________________