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: c22309 at j1xsfs90 dot is dot nwa dot com (Mark Conty)
Subject: RE: Executing stored procedures with parameters
Date: Mar 20 1996 3:20PM

Tom Wilson writes:
> The other way is to execute the stored procedure as a normal SQL command:
> Note that here you have to declare a variable (@junk) for the exec even though
> it does nothing.
> $dbh->dbcmd("declare \@junk int\n");
> $dbh->dbcmd("exec test_proc \@in='some stuff',\@out=\@junk output");
> $dbh->dbsqlexec;
> @dat=$dbh->dbretdata;
> print ("parms= ",@dat,"\n");

First off, I agree with Tom's statement that it's better to use the dbrpc*() 
routines.  It'll take you a bit more time up-front for coding and testing, but 
there's be no server time spent parsing/verifying the incoming SQL stream (such 
as the "declare" and "exec" statements you started with).  In addition, invoking 
your stored procedure via RPC cuts down on your network traffic to (and from?) 
the Sybase server.  For specific examples, check your Sybase manual; there 
should be some sample code on the dbrpc*() pages.  Also, Michael P. was kind 
enough to include some sample RPC code in the /eg/ directory, file 
"".  (Thanks, Michael!)

But if you're going to go instead with generating your own SQL statements and 
sending them via dbsqlexec(), you do not necessarily have to declare and use 
"@junk".  It all depends on what kinds of parameters are expected by the stored 
procedure -- it might also be an option to specify the parameters positionally.

Let's say you wanted to run "sp_depends" on a table, assuming you had already 
done the dblogin(), dbuse(), and so forth:

> $dbh->dbcmd("exec sp_depends sysobjects");

... and rather than just picking up the returned data, you should also check the 
results and for returned output.  I use a general-purpose routine for that:

> sub
> getDBresults
> {
> 	my($dbh) = @_;
> 	my(@dat,@results);
> 	while ( $dbh->dbresults() != $NO_MORE_RESULTS ) {
> 		while ( (@dat = $dbh->dbnextrow()) ) {
> 			push(@results,@dat);
> 		}
> 		while ( (@dat = $dbh->dbretdata()) ) {
> 			# Check if Sybperl work-around for no returned data.
> 			if ( grep (/__ALL NULL__/,@dat) ) {
> 				push(@results,grep(!/__ALL NULL__/,@dat));
> 				last;
> 			}
> 			else {
> 				push(@results,@dat);
> 			}
> 		}
> 	}
> 	return(@results);
> }

... but first, I check for a return status.  Then I call the routine to retrieve 
any the returned output/data:

> if ( $dbh->dbhasretstat() ) {
>         printf STDOUT ("Stored procedure '%s' returned: %d\n",
>                 $proc_name, $dbh->dbretstatus());
> }
> else {
>         printf STDOUT ("Stored procedure returned no status.\n");
> }
> @Results = getDBresults($dbh);
> print STDOUT @results, "\n";

(I guess I could have changed getDBresults() to accept and return pointers to 
separate output and data arrays, but I haven't had to do much with returned data 

As with most any software question (especially when Perl is involved! :-), there 
is usually going to be more than one way to do something, so now you have at 
least two ways of doing that task.

Hope this helps...
Mark D. Conty                      
Northwest Airlines                        
IS/Applications                   <><