Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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:

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)

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};
			  $eventDB{USER}, $eventDB{PASSWORD},
			  {PrintError => 1, RaiseError => 1 });

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

  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