sybperl-l Archive

From: Michael Burstin <mikeb at concerto dot com>
Subject: processing output from stored procedures using DBD::Sybase
Date: Aug 6 2003 8:49PM

I know I probalby posted somethign similar to this a few years ago,
but can't seem to get into the Sybperl archives to find it.

I am trying to process the results from running sp_helpcache to see
what tables are bound to a named cache, to see whether I need to add a
binding for a table.  The results run from ISQL are as follows:

	    ====================begin====================
1> sp_helpcache event_table_cache
2> go
Cache Name                Config Size     Run Size       Overhead
------------------------ -------------   ----------     ----------   
event_table_cache         100.00 Mb      100.00 Mb        9.99 Mb
 
------------------ Cache Binding Information: ------------------ 
 
Cache Name           Entity Name                Type               Index Name                    Status
----------           -----------                ----               ----------                    ------
event_table_cache    eventdb.dbo.hist_agent_off  index              row_id                          V
event_table_cache    eventdb.dbo.hist_agent_off  table                                              V
(return status = 0)
	    =====================end=====================

I want to be able process the Entity Name and Type columns.  I have
tried unsuccessfully to use both $sth->func('syb_output_params')
and a do { }while $sth->{syb_more_results} and neither appear to
work.  In addition, without me issuing any print statement, I get
everything up to the "Cache Name..." and "----------..." printed to my
screen when I call $sth->execute.  When I do a Data::Dumper of my
results, I get $VAR1 = [ 0 ];

Any help is appreciated.  Below is my code:

  #create dbh
  my $dbh =  DBI->connect("DBI:Sybase:server=$eventDB{SERVER};
                                       database=$eventDB{DATABASE}",
			  $eventDB{USER}, $eventDB{PASSWORD},
			  {PrintError => 1, RaiseError => 1 });

  my $sql = "sp_helpcache $cache";
  my $sqlH = $dbh->prepare($sql);
  $sqlH->execute;

  my @results = $sqlH->func('syb_output_params');
  print "Data\n";
  print Dumper \@results;
  print "\n\n\n";

  #need to handle multiple result sets that the stored proc returns
  do {
    print "More data\n";
    while (my $results = $sqlH->fetch) {
      print "New row\n";
      print Dumper $results;
    }
  } while($sqlH->{syb_more_results});


-- 
Michael Burstin
NPI Engineer
Concerto Software
978-952-0842
mikeb@concerto.com