sybperl-l Archive

From: Torsten Bauer <perl at torsten-bauer dot de>
Subject: Re: stored procedure and return values
Date: Oct 12 2005 2:59PM

hi michael,

sorry your answer isn't true as far as i can see... i have fully 
qualified the proc name and the proc gets executed. this proc was only a 
test proc to genereate an error... i create a temp table, then the proc 
which uses this temp table and then i drop the temp table and execute 
the proc... the error says that it can't find table #test... my 
understanding of CS_STATUS_RESULT was that it contains the return status 
of a procedure - but it seems not...

thanks
torsten


michael.peppler@bnpparibas.com schrieb:

> The problem is that the SQL request is not finding your stored proc. Most
> likely because the stored proc is one database, and the default database
> for the login you use is another database (master?).
>
> You can either qualify the stored proc with the database name (exec
> dbname..procname) or add an explicit $dbh->do("use databasename"); to
> change the database context.
>
> Michael
>
>
>
>
> Internet
> perl@torsten-bauer.de@peppler.org - 12/10/2005 15:09
>
>
> Sent by:    owner-sybperl-l@peppler.org
>
> To:    sybperl-l
>
> cc:
>
>
> Subject:    stored procedure and return values
>
>
> hi all
>
> i have some trouble to get the return value from a stored procedure..
> hopefully someone of you is able to help me. the procedure i used for
> testing:
>
>
> create table #test
> (
> test int
> )
> go
> create proc proctest
> as
> begin
>  select * from #test
> end
> go
> drop table #test
> go
> exec proctest
>
>
> when i run the procedure proctest with isql the return status is -6.
>
> i try to catch this in perl but i can't get it.. i used different ways
> and this is the latest one - based on the documentation:
>
> #!/usr/bin/perl
>
> $ENV{SYBASE}="/apps/sybase/current";
> $ENV{SYBASE_SYSAM}="SYSAM-1_0";
> $ENV{SYBASE_FTS}="EFTS-12_5";
> $ENV{SYBASE_JRE}="/apps/sybase/current/shared-1_0/jre1.2.2";
> $ENV{SYBASE_ASE}="ASE-12_5";
> $ENV{SYBASE_OCS}="OCS-12_5";
>
> use DBI;
> use DBD::Sybase;
>
> my $dbuser = "user";
> my $dbpass = "password";
>
> $dbh_dst = DBI->connect("dbi:Sybase:server=SERVER",
>                          $dbuser, $dbpass, {
>                                   RaiseError => 0,
>                                   PrintError => 1,
>                                   AutoCommit => 1
>                                 })        or die "Can't connect to
> database: ", $DBI::errstr, "\n";
>
>
> $sth_dst = $dbh_dst->prepare("exec dwh_trash..proctest") or die "ERR";
>
> $sth_dst->execute();# or die "ERR";
>
> do
> {
>  while($data = $sth_dst->fetch)
>  {
>    if($sth_dst->{syb_result_type} == CS_STATUS_RESULT)
>    {
>      $status = $data->[0];
>      print "if @$data\n";
>    }
>    else
>    {
>      print "else @$data\n";
>    }
>  }
> }
> while($sth_dst->{syb_more_results});
>
>
> print "end\n";
>
>
> it seems that i don't get any data for the result type CS_STATUS_RESULT
> as the output is only:
>
> ../test.pl
> DBD::Sybase::st execute failed: Server message number=208 severity=16
> state=1 line=4 server=SERVER procedure=proctest text=#test not found.
> Specify owner.objectname or use sp_help to check whether the object
> exists (sp_help may produce lots of output).
> end of proc
>
>
> has someone an idea what i do wrong? it seems i'm going mad..
>
>
> thanks a lot in advance
>
> torsten
>
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential. If you receive 
> this message in error, please delete it and immediately notify the 
> sender. Any use not in accord with its purpose, any dissemination or 
> disclosure, either whole or partial, is prohibited except formal 
> approval. The internet
> can not guarantee the integrity of this message. BNP PARIBAS (and its 
> subsidiaries) shall (will) not therefore be liable for the message if 
> modified.
>                ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le "message") sont 
> etablis a l'intention exclusive de ses destinataires et sont 
> confidentiels. Si vous recevez ce message par erreur, merci de le 
> detruire et d'en avertir immediatement l'expediteur. Toute utilisation 
> de ce message non conforme a sa destination, toute diffusion ou toute 
> publication, totale ou partielle, est interdite, sauf autorisation 
> expresse. L'internet ne permettant pas d'assurer l'integrite de ce 
> message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce message, 
> dans l'hypothese ou il aurait ete modifie.
>  
>