sybperl-l Archive

From: "Klein, Shoshana R" <Shoshana dot Klein at gs dot com>
Subject: RE: executing a stored procedure with 2 sql statements and returning the final resultset
Date: Oct 25 2000 4:43PM

Below is one sample of code where we're trying to execute 3 sql statements
at once.
we dont get anything back in the result set. If I remove the first 2 sql
statements which we are try ing to run to improve perfomance, then I do get
back the result of my sql query.

These are the commands in the stored proc "get_proc_info" used below
++++set rowcount 200  set forceplan on select
u.guid,u.user_name,u.location,ur.r
ole_id,r.role_name,u.dept,
  u.emp_stat,r.aid, u.kerberos_id,o.object_name, o.object_id,
  a.actn_id,a.actn_nm
  from amp_repos_user u, amp_repos_user_role ur,amp_repos_role r,
  amp_repos_object o, amp_repos_action a, amp_repos_role_obj_act roa  where
u.k
erberos_id!=null
  and u.guid=ur.guid
  and ur.aid=r.aid
  and ur.role_id=r.role_id
  and r.aid*=roa.aid
  and r.role_id*=roa.role_id
  and roa.aid*=o.aid
  and roa.object_id*=o.object_id
  and roa.aid*=a.aid
  and roa.actn_id*=a.actn_id order by
u.location,u.dept,u.kerberos_id,r.aid,role
_id,object_id,actn_id++++ 

#!/opt/bin/perl

use Sybase::DBlib;
require 'getopts.pl';
require 'ctime.pl';


$sapw='hello world!';
print "Enter kerberos id :";
chop($user = <>);

print "Enter guid :";
chop($guid = <>);

print "Enter user name :";
chop($user_name = <>);

print "Enter dept :";
chop($dept_htm = <>);

print "Enter location :";
chop($location = <>);
                                
print "Enter  aid :";
chop($aid = <>);

$dbproc = new Sybase::DBlib("xxxxxs","xxxxxs","IxxxxxV");

$dbproc->dbuse("amp");


$dbproc->dbcmd ( "exec get_proc_info \@user='$user', \@aid='$aid',
\@guid='$guid
',\@user_name='$user_name',\@dept_htm='$dept_htm',\@location='$location'");
$dbproc->dbsqlexec;
$dbproc->dbresults;
while ((@dat = $dbproc->dbnextrow))
{
        print "\n";
        foreach $col (@dat) {
        $sproc=$sproc.$col;
        }
}

print "++++$sproc++++";
print "\n"; 
$dbproc->dbcmd ( $sproc);
$dbproc->dbsqlexec;
$dbproc->dbresults;
while ((@dat = $dbproc->dbnextrow))
{
        print "\n";
        foreach $col (@dat) {
        print "$col ";
        }
}
      


run results:

Enter kerberos id :
Enter guid :
Enter user name :
Enter dept :
Enter location :
Enter  aid :

++++set rowcount 200  set forceplan on select
u.guid,u.user_name,u.location,ur.r
ole_id,r.role_name,u.dept,
  u.emp_stat,r.aid, u.kerberos_id,o.object_name, o.object_id,
  a.actn_id,a.actn_nm
  from amp_repos_user u, amp_repos_user_role ur,amp_repos_role r,
  amp_repos_object o, amp_repos_action a, amp_repos_role_obj_act roa  where
u.k
erberos_id!=null
  and u.guid=ur.guid
  and ur.aid=r.aid
  and ur.role_id=r.role_id
  and r.aid*=roa.aid
  and r.role_id*=roa.role_id
  and roa.aid*=o.aid
  and roa.object_id*=o.object_id
  and roa.aid*=a.aid
  and roa.actn_id*=a.actn_id order by
u.location,u.dept,u.kerberos_id,r.aid,role
_id,object_id,actn_id++++





 -----Original Message-----
From: Michael Peppler [mailto:mpeppler@peppler.org]
Sent: Tuesday, October 24, 2000 8:19 PM
To: SybPerl Discussion List
Subject: Re: executing a stored procedure with 2 sql statements and
returning the final resultset 


Klein, Shoshana R writes:
 > Is there a way of executing a stored procedure with 2 sql statements and
 > only returning the final resultset to my perl script.
 > For instance, if I want to select * into temp-tbl from sysusers where
name
 > like xyz and then
 >                                     select  * from temp-tbl where
x=myprof
 > So far I've only been successful with 1 sql statement returning 1
resultset.
 > 

Hmmm.... normally if you execute the two statements above you should
only get the results from the second statement (the first one doesn't
produce any rows)

Do you have an actual example that you could post?

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