PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Rob Biedenharn <Rob at AgileConsultingLLC dot com>
Subject: Turning a Sybase PRINT statement into a "result"
Date: Mar 23 2006 7:56PM

Well, this might not work for everyone, but I thought I'd get  
comments anyway.  I'm working with a client that is moving many ksh  
script that use isql to perl scripts that indirectly use  
DBD::Sybase.  I say "indirectly" because the database is futher  
wrapped in an object which handles the connection and some helpful  
things like fetching all the results into an array (where each  
element is the result of a call to fetchrow_arrayref).

Several stored procedures that mixed PRINT and SELECT to produce  
output via isql now need to be changed to work the same way under  
DBD::Sybase (and DBI of course).

I could find many references to using the syb_err_handler to  
intercept the PRINT, handle it, and prevent higher levels from seeing  
the "error".  However, this would cause the output immediately, but  
the rest of the results were being neatly collected into an array.

Here's the interesting bits of my solution:

Setup:

      1	    $self->{print_buffer} = ();
      2	    my $err_handler;
      3	    {
      4	        my $print_buffer = \@{$self->{print_buffer}};
      5	        $err_handler = sub {
      6	            my($err, $sev, $state, $line, $server, $proc,  
$msg, $sql, $err_type) = @_;
      7	
      8	            if (defined($err) && $err == 0
      9	                && ($sev == 10 || $sev == 0)
     10	                && $err_type eq 'server') {  # a PRINT  
statement?
     11	                push @{$print_buffer}, $msg; # save it for later
     12	                return 0;                    # it's been  
handled!
     13	            }
     14	            return 1;
     15	        };
     16	    }
     17	    $self->{dbh} = DBI->connect($self->{datasource},
     18	                                $self->{username},
     19	                                $self->{password},
     20	                                { syb_cancel_request_on_error  
=> 0,
     21	                                  syb_err_handler =>  
$err_handler }
     22	                            );

Line 4 establishes a 'my' variable which can be used in the closure  
of the err_handler.
The test in lines 8..10 may be overly cautious, but it seems to work.
Line 11 just tucks the $msg away for later.

Retrieval:  Remember, the object that's set-up presents a convenience  
method to get all the results into a single array (it can also take a  
CODE reference to call for each "row", but that doesn't matter here).

      1	sub fetch_results {
      2	    my ($self, $results, $status, $output_param, $nocount) = @_;
      3	    my $sth = $self->{sth};
      4	    my $rowref;
      5	
      6	    do {
      7	        while ($rowref = $self->_fetchrow($nocount)) {
      8	            my $type = $result_types{$sth->{syb_result_type}};
      9	            if ($type eq 'CS_ROW_RESULT') {

In line 7, note the call to the method _fetchrow() (that's shown next)
and in line 8, the use of $sth->{syb_result_type} to decide what  
should be done with this result (ROW and COMPUTE go into $results,  
STATUS goes into $status, PARAM goes into $output_param).

      1	sub _fetchrow {
      2	    my $self = shift or die;
      3	    my $nocount = shift;
      4	    my $sth = $self->{sth};
      :	
      :
     15	    my $row;
     16	    {                         # originally, this block  
defined a 'local' scope
     17	        if (@{$self->{print_buffer}}) {
     18	            $row = [ shift @{$self->{print_buffer}} ];
     19	            $sth->{syb_result_type} = CS_MSG_RESULT();
     20	        }
     21	        else {
     22	            eval {
     23	                $row = $sth->fetchrow_arrayref();
     24	            };
     25	            if ($@) {
     26	                $self->{logger}->print('problem in the eval: ');
     27	                $self->{logger}->print($@);
     28	            }
     29	        }

Lines 5..14 did some error checking that's not relevant here.
Line 17 checks to see if something has been captured in the  
print_buffer.
Line 18 makes the (first) saved message into an arrayref and makes  
the syb_result_type a CS_MSG_RESULT.  (Well, tries to... the type  
seems to be CS_ROW_RESULT when checked by fetch_results line 9.)
Line 23 gets the next result from the statement handle if the  
print_buffer was empty.

So I tried it with this stored procedure:

      1	Create Proc dbo.sample_p
      2	As
      3	Begin
      4	  DECLARE @today smalldatetime
      5	
      6	  SELECT  @today = GETDATE()
      7	  print  "1 this was printed first"
      8	  select "2 today is ", @today
      9	  print  "3 this in the middle"
     10	  select "4 tomorrow will be ", datepart(DDD, dateadd(dd, 
1,@today))
     11	  print  "5 first of two..."
     12	  print  "6 ...second of two (in a row)"
     13	  select "7 did you get that?"
     14	
     15	  RETURN 0
     16	END

And it worked:

         Fetched         8 results        (7 rows, 1 status)

         1 this was printed first
         2 today is Mar 23 2006 12:50PM
         3 this in the middle
         4 tomorrow will be Friday
         5 first of two...
         6 ...second of two (in a row)
         7 did you get that?
         Stored procedure return status: 0

What makes me curious is that all 7 were seen as CS_ROW_RESULT rather  
than three of them being CS_ROW_RESULT and four being CS_MSG_RESULT.   
That is, I'd have expected "(3 rows, 4 messages, 1 status)".

If anyone's still with me, I have two questions:

1. Am I breaking anything with this?  In particular, I was surprised  
that "$sth->{syb_result_type} = CS_MSG_RESULT();" seemed to have no  
effect.

2. Is there a better way to accomplish this?  The existence of the  
CS_MSG_RESULT() makes me think there's something else...

Thanks for you time,
-Rob

Rob Biedenharn		http://agileconsultingllc.com
Rob@AgileConsultingLLC.com
+1 513-295-4739