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 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:

# 	@(#)	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});

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 Peppler         -||-  Data Migrations Inc.    -||-
Int. Sybase User Group  -||-
Sybase on Linux mailing list: