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: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: stored procedure tutorial
Date: Sep 28 1999 5:26PM

Cull, Tim  BGI SF writes:
 > Can anyone point me to a really comprehensive tutorial about how to call a
 > stored procedure in sybperl?  Especially from the point of view of someone
 > who has never had occasion to use ct_lib in C and has no idea how it works?
 > I'd especially appreciate a start-to-finish example of calling a stored
 > procedure with more than one input parameter and at least one output
 > parameter and an explanation/listing of the meanings of the various constant
 > values in the calls to ct_param and ct_command.

You have two options.

You can use the language commands, and do it like this:

$dbh->ct_execute("declare \@out_param int
exec the_proc \@param1 = 1234 \@param2 = 'this is a string', \@param3
= \@out_param");
while($dbh->ct_results($restype) == CS_SUCCEED) {
    next unless $dbh->ct_fetchable($restype);
    while(@d = $dbh->ct_fetch) {
	if($restype == CS_PARAM_RESULT) {
	    $the_output_param = $d[0];
	} else {
           ... maybe do something with any other return type here,
           in particular the CS_STATUS_RESULT which is the proc
	   status result.
        }
    }
}

Now, if you want to use the RPC mechanism you should check this sample 
script that is part of the sybperl distribution, in the eg/ directory:

#!/usr/local/bin/perl
#
# 	@(#)ct_param.pl	1.4	10/5/95
#
# Example of ct_param() usage.
# The RPC we want to run is in the proc.isql file in this directory.

use Sybase::CTlib;

$d = new Sybase::CTlib mpeppler;

$d->ct_command(CS_RPC_CMD, "t_proc", CS_NULLTERM, CS_NO_RECOMPILE);

%param = (name => '@acc',
	  datatype => CS_CHAR_TYPE,
	  status => CS_INPUTVALUE,
	  value => 'CIS 98941' ,
	  indicator => CS_UNUSED);

$d->ct_param(\%param) == CS_SUCCEED || die;

# Alternate technique: pass an anonymous hash...
$d->ct_param({name => '@date',
	      datatype => CS_DATETIME_TYPE,
	      status => CS_RETURN,
	      value => '950529' ,
	     indicator => CS_UNUSED});
$d->ct_param({name => '@open_val',
	     datatype => CS_FLOAT_TYPE,
	     status => CS_RETURN,
	     indicator => -1});
$d->ct_param({name => '@open_val_t',
	      datatype => CS_FLOAT_TYPE,
	      status => CS_RETURN,
	     indicator => -1});

$d->ct_send();
while($d->ct_results($restype) == CS_SUCCEED)
{
    print "$restype\n";
    next if($restype != CS_ROW_RESULT &&
	    $restype != CS_PARAM_RESULT &&
	    $restype != CS_STATUS_RESULT);

    while(%dat = $d->ct_fetch(1))
    {
	foreach (keys(%dat)) {
	    print "$_: $dat{$_}\n";

	    # Check to see if the date parameter that we've retrieved is really
	    # a Sybase::CTlib::DateTime reference.
	    if($_ =~ /\@date/)
	    {
		print ref($dat{$_}), "\n";
		# Should print 'Sybase::CTlib::DateTime'.
	    }
	}
    }
}

YOu should also check out the Sybase OpenClient manuals on ct_param()
and ct_command() for information on the symbolic values being
referenced, and possibly also read the articles I wrote for the ISUG
Technical Journal (on my web page).

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@peppler.org    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com