sybperl-l Archive

From: "Mark Sutfin" <MSutfin at affinitygroup dot com>
Subject: Retrieving current statement with $dbh->errstr
Date: Mar 12 2004 3:44PM

I'm migrating all of our ksh scripts to perl. I'm new to DBI. I'm testing it to see if it'll work for all of the existing db access currently found in our ksh scripts.

If there's a better place to post this question, thanks in advance for the direction.

I'd like the current sql statement to display in my logfile. So for each statement, I'd like to assign this to a variable before/after execute. Is there a way to use this driver-specific database handle attribute to return the sql statement regardless of error?

TIA,
Mark

here's my code.. all comments accepted regardless of topic.... 

use strict;
use DBI;
use DBD::Sybase;

my $job_no = 88;
my @parms;

# Retrieve Operations Job parms
db_get_ops_parms();

sub db_get_ops_parms {
    
    my ($job_no) = shift;
    my ($server, $database, $hostname, $scriptname, $login, $passwd);
    $server     = 'callisto';
    $database   = 'agipps01';
    $hostname   = 'mdsperl';
    $scriptname = 'ops_job.pl';
    $login      = 'x';
    $passwd     = 'x';

	# Connect to the server     
	my $dbh = DBI->connect(	"DBI:Sybase:server=$server;
				database=$database;
				hostname=$hostname;
                                scriptname=$scriptname",
				"$login",
				"$passwd",
				{PrintError		=> 1,
				 RaiseError		=> 0,
				 syb_do_proc_status	=> 0,
				 syb_show_sql		=> 1,
				 syb_show_eed		=> 1,
				 syb_rowcount		=> 0,
				 syb_err_handler	=> \&print_handler}) # Calls print handler when print statement
		or die "Couldn't connect to database: " . DBI->errstr;
        
        # Report the Client Library version
        print "$dbh->{syb_oc_version}\n";
        
	# Prepare the SQL
	my $sth = $dbh->prepare ("select * from test_table") 
		or die "Couldn't prepare statement: " . $dbh->errstr;
 
	# Execute the query
	$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;		

	# CS_ROW_RESULT     = 4040
        # CS_CURSOR_RESULT  = 4041
        # CS_PARAM_RESULT   = 4042
        # CS_STATUS_RESULT  = 4043
        # CS_MSG_RESULT     = 4044
        # CS_COMPUTE_RESULT = 4045
 	do {
	    while (my @data = $sth->fetchrow_array()) {
	        #while
		my $result_type = $sth->{syb_result_type};
		print "-----------------------------------------------------Begin $result_type\n";
		push @parms, @data;
		foreach my $data(@data) {
		    print "$data\n";
		}
		#printf "%-22s%-15s%-10s\n", @data;
		$result_type = $sth->{syb_result_type};
		print "-----------------------------------------------------END   $result_type\n";
    	}
	} while ($sth->{syb_more_results});

	# Clean up connection and close output file handle
	$sth->finish;
	$dbh->disconnect;
}

sub print_handler	{
    
    my($err, $sev, $state, $line, $server, $proc, $msg) = @_;

    if(!$err) #if it's not an error
    {
	# This comes from a PRINT statement in the SQL
	print "$msg \n"; 
	return 0; # tell DBD::Sybase to ignore this
    }
    return 1;
}