18th_bronzeman has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks.

I am creating some scripts to verify stored procedures. These stored procedures contain print statements meant to organize data for the user.

I have gotten past getting DBD::ODBC to recognize print statements and get them output to the console without throwing an error. Now, I want to now take those SQL PRINT statements and place them in data structures in the same order that they appear on the console. Say to create a file later.

I used this code as my sample to get the statements to the console:

#CREATE THE CUSTOM ERROR HANDLER AND
#DO CODE HERE THAT RETURNS PRINT STATEMENT HEADERS
#MINGLED WITH RETURNS OF ROWS, I.E.:
#Name
#JOHN SMITH
#Title
#MANAGER
#Department
#FOO BAR
#Data from Database
my @sp_return_data;
#Array to be populated like stored procedure output
my @final_output;

do { 
   my $line; 
   while (@sp_return_data = $sp_sth->fetchrow_array()) {
      $line = join (' | ', @sp_return_data) . ".\n";
      push @final_output, $line;
      }
} while ($sp_sth->{odbc_more_results});

[download]
I don't really understand how to grab the PRINT statements being handled by my custom error handler. My question is:

How do you modify that code snippet to put all of the PRINT statements into the array along with the rows (In order of appearance).

Thank you Monks!

Replies are listed 'Best First'.
Re: Saving SQL PRINT statements - MSSQL, SQL Server, DBD::ODBC.
by runrig (Abbot) on Sep 22, 2007 at 02:27 UTC
    I've done this...and the code is somewhere (it'll take a while to dig up)...but basically you have to look into odbc_err_handler in the DBD::ODBC docs, the print statements are caught there as errors.

    And I see that the docs mention some examples under the odbc_err_handler section. So maybe I don't have to dig up that code :-)

[reply]
      Thank you for the response!

      I have actually used the code in the DBD::ODBC docs. That is what you see modified above. I wrapped all of the error trapping code in a subroutine. That was not included. The while on the end handles the PRINT statements if/when the occur.

      I can print to console from within that subroutine. I can make that subroutine return its output as a variable. What I don't understand is how to take that variable and place it into the same data structure as the sql results in the order it returned from the database.

      Thanks again for the response.

[reply]
        sorry, misunderstood the question due to lack of formatting. Wrap your code in <code></code> tags. Your error handler should be in the same scope as your while loop. A rough outline is:
        
{
  my @output;
  local $dbh->{odbc_err_handler} = sub {
    ....
    push @output, $print_statement;
    ...
  };

  while ($sth->{odbc_more_results}) {
    while (my $data = $sth->fetchrow_array()) {
      ...
      push @output, (join("|", @$data) . "\n");
    }
  }
}

        [download]
[reply]
[d/l]
Re: Saving SQL PRINT statements - MSSQL, SQL Server, DBD::ODBC.
by mje (Curate) on Sep 26, 2007 at 07:59 UTC
    See PRINT Statement and Status Messages. Now put the odbc_error_handler in the same scope as the code which fetches your data and change the print statements to push the procedure output into the same array. Something like: 
    my @final_output;
sub err_handler {
   my ($sqlstate, $msg, $nativeerr) = @_;
   # Strip out all of the driver ID stuff
   $msg =~ s/^(\[[\w\s:]*\])+//;
   push @final_output, $msg;
   return 0;
}
$dbh->{odbc_err_handler} = \&err_handler;
my $sp_sth = $dbh->prepare('{call myproc}');
$sp_sth->execute;
do { 
   my $line; 
   while (@sp_return_data = $sp_sth->fetchrow_array()) {
      $line = join (' | ', @sp_return_data) . ".\n";
      push @final_output, $line;
      }
} while ($sp_sth->{odbc_more_results});

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom