Problem with Returning a Value from a Stored Procedure to Powerbuilder

I am using Powerbuilder 6.5.1 and Sql Anywhere 6.0.3

I trying to call a Strored Procedure from within Powerbuilder.This
procedure should return me a value to tell me if everything went ok
in this procedure.The procedure runs but it does not return me a value
back to powerbuiler.

-----------------------------------------------
Here's a shorten ver of the Stored Procedure.
-----------------------------------------------

Alter procedure
DBA.Unbill_wkst(in d_invoice_id decimal(10),out s_ret_code char(3))
 begin atomic
   Declare no_error exception for sqlstate value '00000';

UPDATE  Work_tech
    set invoice_id = null,
    regular_rate = 0
    where    work_tech.invoice_id = d_invoice_id;

IF sqlstate <> no_error then
    set s_ret_code='bad'
ELSE
     set s_ret_code='OK'
END IF

END

-----------------------------------------------
Here how i call it from Powerbuilder.
-----------------------------------------------


DECIMAL d_inv_id;
STRING  s_ret_code;
INTEGER i_nstatusNB

DECLARE un_bill PROCEDURE FOR unbill_wkst
         d_invoice_id = :d_inv_id,
         s_ret_code = :s_ret_code;

 d_inv_id = dw_tab.object.invoice_id[dw_tab.Getrow()]
 EXECUTE un_bill;

 i_nstatusNB = SQLCA.sqlcode;
 MessageBox('Unbilling Status  ',s_ret_code)

 CLOSE UN_BILL;

 END IF




What i am doing wrong


Thanks

Daniel Richard
Reg Clinical Eng Dept.







0
Daniel
4/6/2000 11:51:02 AM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

3 Replies
589 Views

Similar Articles

[PageSpeed] 25

I generally don't bother with OUTPUT parms. Probably because I haven't taken
the time to learn how to use them properly...  ;-)
When I need to return info from a proc, I code it to return a result set,
and then paint a datawindow object to invoke it.  Then I can get my OUT data
with simple GetItemXXX calls.

If you want to try this, here's how:
 Alter procedure
 DBA.Unbill_wkst(in d_invoice_id decimal(10) )
  result ("s_ret_code" char(3) )
  begin atomic
    Declare no_error exception for sqlstate value '00000';

 UPDATE  Work_tech
     set invoice_id = null,
     regular_rate = 0
     where    work_tech.invoice_id = d_invoice_id;

 IF sqlstate <> no_error then
     Select 'BAD'
 ELSE
      Select 'OK'
 END IF

END

Then paint a datawindow object 'd_myproc' that calls this procedure.
Your PowerScript becomes:
int   ls_RV
string  ls_RC
datastore ds_Proc

ds_Proc   = Create datastore
ds_Proc.dataobject = 'd_myproc'
ds_Proc.SetTransObject( SQLCA )

RV = ds_Proc.Retrieve( dw_tab.object.invoice_id[dw_tab.Getrow()] )

If RV > 0   then
   ls_RC = ds_Proc.GetItemString( 1, "s_ret_code" )
End if

Destroy ds_Proc

Paul Horan
VCI   www.twoplus.com
Springfield, MA

"Daniel Richard" <danielr@health.nb.ca> wrote in message
news:1JXSo57n$GA.316@forums.sybase.com...
> I am using Powerbuilder 6.5.1 and Sql Anywhere 6.0.3
>
> I trying to call a Strored Procedure from within Powerbuilder.This
> procedure should return me a value to tell me if everything went ok
> in this procedure.The procedure runs but it does not return me a value
> back to powerbuiler.
>
> -----------------------------------------------
> Here's a shorten ver of the Stored Procedure.
> -----------------------------------------------
>
> Alter procedure
> DBA.Unbill_wkst(in d_invoice_id decimal(10),out s_ret_code char(3))
>  begin atomic
>    Declare no_error exception for sqlstate value '00000';
>
> UPDATE  Work_tech
>     set invoice_id = null,
>     regular_rate = 0
>     where    work_tech.invoice_id = d_invoice_id;
>
> IF sqlstate <> no_error then
>     set s_ret_code='bad'
> ELSE
>      set s_ret_code='OK'
> END IF
>
> END
>
> -----------------------------------------------
> Here how i call it from Powerbuilder.
> -----------------------------------------------
>
>
> DECIMAL d_inv_id;
> STRING  s_ret_code;
> INTEGER i_nstatusNB
>
> DECLARE un_bill PROCEDURE FOR unbill_wkst
>          d_invoice_id = :d_inv_id,
>          s_ret_code = :s_ret_code;
>
>  d_inv_id = dw_tab.object.invoice_id[dw_tab.Getrow()]
>  EXECUTE un_bill;
>
>  i_nstatusNB = SQLCA.sqlcode;
>  MessageBox('Unbilling Status  ',s_ret_code)
>
>  CLOSE UN_BILL;
>
>  END IF
>
>
>
>
> What i am doing wrong
>
>
> Thanks
>
> Daniel Richard
> Reg Clinical Eng Dept.
>
>
>
>
>
>
>


0
Paul
4/6/2000 2:07:06 PM
While there is probably a solution somewhere in your syntax, I can tell you that
there is a much easier way to run stored procedures within PB.  If your
application
is not already using an inherited transaction object (such as n_tr in a PFC app)
do so.  Then, within the transacation object declare a local external function.
PB's painter will provide you with a <Procedure> button that will display a list
of the available SP's and will create the proper syntax for you.  

Your syntax in Pb will then simply be:

  sqlca.un_bill(d_inv_id, s_ret_code)

and life will be much simpler.

-David Gerdner

On Thu, 6 Apr 2000 08:51:02 -0300,
 in sybase.public.sqlanywhere.general
Daniel Richard <danielr@health.nb.ca> wrote: 
>I am using Powerbuilder 6.5.1 and Sql Anywhere 6.0.3
>
>I trying to call a Strored Procedure from within Powerbuilder.This
>procedure should return me a value to tell me if everything went ok
>in this procedure.The procedure runs but it does not return me a value
>back to powerbuiler.
>
>-----------------------------------------------
>Here's a shorten ver of the Stored Procedure.
>-----------------------------------------------
>
>Alter procedure
>DBA.Unbill_wkst(in d_invoice_id decimal(10),out s_ret_code char(3))
> begin atomic
>   Declare no_error exception for sqlstate value '00000';
>
>UPDATE  Work_tech
>    set invoice_id = null,
>    regular_rate = 0
>    where    work_tech.invoice_id = d_invoice_id;
>
>IF sqlstate <> no_error then
>    set s_ret_code='bad'
>ELSE
>     set s_ret_code='OK'
>END IF
>
>END
>
>-----------------------------------------------
>Here how i call it from Powerbuilder.
>-----------------------------------------------
>
>
>DECIMAL d_inv_id;
>STRING  s_ret_code;
>INTEGER i_nstatusNB
>
>DECLARE un_bill PROCEDURE FOR unbill_wkst
>         d_invoice_id = :d_inv_id,
>         s_ret_code = :s_ret_code;
>
> d_inv_id = dw_tab.object.invoice_id[dw_tab.Getrow()]
> EXECUTE un_bill;
>
> i_nstatusNB = SQLCA.sqlcode;
> MessageBox('Unbilling Status  ',s_ret_code)
>
> CLOSE UN_BILL;
>
> END IF
>
>
>
>
>What i am doing wrong
>
>
>Thanks
>
>Daniel Richard
>Reg Clinical Eng Dept.
>
>
>
>
>
>
>

---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp
0
David
4/6/2000 7:20:04 PM
David's solution should do the trick.  There have been issues with SQL 
Anywhere output parameters and PB and the only solution I am aware of was 
to do it as a local external function (RPC).
-- 
Jim Egan [TeamSybase]
Houston, TX

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm
0
Jim
4/7/2000 2:31:09 AM
Reply:

Similar Artilces:

Problems with Return Value with Stored Procedure
Hi, I have facing this problem, and i don't know what is going wrong. The SP when tested in the Query Analyser works fine. the resultant is either a 0 or PID where 0 is returned when the insert doesn't happen I might have some extra declaration, which might be causing this error , or not defining the right thing. Any suggestion is welcomed and greatly appreciated. Thanks Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click Dim sSQLConnection As SqlConnection = New SqlConnection(Configuration...

Problem Stored Procedure with Return Value
Hi, I have a simple procedure: create procedure [dbo].[sp_seg_ins_usuario_u] ( @x_login varchar(30), @x_nome varchar(20), @x_sobrenome varchar(20), @x_grupo varchar(30), @x_entidade varchar(05), @x_matricula varchar(07), @x_serverid varchar(40) ) as begin -- set nocount on added to prevent extra result sets from -- interfering with select statements. set nocount on; insert into tb_usuario ( login, nome, sobrenome, grupo, entidade, matricula, serverid ) values ( @x_login, @x_nome, @x_sobrenome, @x_grupo, @x_entidade, @x_matricula, @x_serverid ); se...

Problem with stored procedure that returns a value
Dear All, I have a stored procedure that accepts some parameters and return an integer value. CREATE PROCEDURE [dbo].[Add_Alerts] @adminUserId varchar(12),@DocNo varchar(15),@Moduleno varchar(5),@Modulename varchar(200),@Reqfromid varchar(12),@ReqfmName varchar(200),@Alert_spresult int OUTPUT AS if (@adminUserId = '') OR (@DocNo = '') OR (@Moduleno = '') OR (@Modulename = '') OR (@Reqfromid = '') OR (@ReqfmName = '') Begin   set @Alert_spresult = 0 Endelse Begin  DECLARE @sql  varchar(4000)  DECLARE @nextSrno varchar(10)  DECLAR...

Problem returning two values from stored procedures
Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window. My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:\blah' Please could anyone shed ligth on this problem? here the code for the stored procedure: CREATE PROCEDURE dbo.GetTillInfo ( @TillIdR varchar(50), @Password varchar(50) ) AS declare @TillId int d...

Stored procedure to return string value as output problem.
I am using a MS SQL server stored procedure that returns 3 output results of datatype char(5), Int, Int respectively. I am using PB RPC to execute the stored procedure. But, the string output I get is the first charcter of the stored procedure output. The stored procedure when executed from SQL Server returns the string value properly as 'SE1'. But in Powerbuilder the output is just 'S'. Is this a bug? If yes, how do I get around this problem? Pls. help. Not sure about SQLServer. Make sure you properly pad the string reference variable w/ the max number of characte...

Problem with return value of stored procedure when using tableadapters
hello Could you please help me with this problem? I have a stored procedure like this: ALTER PROCEDURE dbo.UniqueChannelName ( @UserName nvarchar(50), @ChannelName nvarchar(50) ) AS return 5;   Then inside of my dataset, I added a new query(dataset1.QueriesTableAdapter) to handle above mentioned stored procedure. Properties window is showing that return type of this adapter is of type int32 as we expected to be. now I want to use it inside of my code: DataSet1TableAdapters.QueriesTableAdapter b = new DataSet1TableAdapters.QueriesTableAdapter(); int i; i=Convert.To...

Problems with utilizing return value from stored procedure in a webform
Hi, I have a webform where I am trying to first display the return value from stored procedure in a textbox. Once I achieve that I will utilize this value in another part of the application. However, it seems I cannot display this return value in the textbox. I am not sure where I am going wrong. I have created a prototype in Northwind. Any help is appreciated. Thanks  CODE: Imports System.Data.SqlClientPartial Class NorthwindEx    Inherits System.Web.UI.Page    Private Sub Button1_Click(ByVal sender As System.Object, _    ByVal ...

MS ADO / ASA 7 Stored Procedure Return Value Problem
I've got an application that we've upgraded from MS DAO & ASA 5 to MS ADO & ASA 7. All this has worked quite smoothly (with some help from this newsgroup!!), but I've now discovered that our stored procedures are not returning the correct return values. This was not previously a problem. We are using ADO 2.5 SP1 and the latest ASA EBF 1133. The specific problem seems to be that stored procedures that do not specify a return value, return garbage. I've checked the stored procedures using ISQL and code along the lines of: CREATE VARIABLE @SPRet tinyint @SPR...

Store Procedure never returns expected return value
Hi, I am having a SP as following: .... @ai_return int output as .... select @ai_return = 0 update AAA set A = "AAA" if @@error <> 0 begin select @ai_return = -1 return end .... But even the UPDATE statement fails, I can't get the -1. This might depend upon which DBMS you are using and how you are invoking the sp and how you are trying to get the output parm. Can you post some info related to the above? steve [TeamPS] Sherwin wrote: > > Hi, > > I am having a SP as following: > ... > @ai_return ...

Stored Procedure only returns 1 value when it should return 7
Hello. I have a web page where i would like to fill a detailsview with some statistics from my database. I tried creating a stored procedure in the sql management studio and when i ran the query it seemed to return 7 values. However when i hook up my SP to a SqlDataSource and bind it to a gridview the datasource is only populated with the first value "ANTAL". What i would like to get is 7 values to bind in my detailsview. Bellow is my SP: CREATE PROCEDURE GetStats AS BEGIN SELECT COUNT(COUNTER) AS ANTAL FROM ARTIKEL SELECT COUNT(DISTINCT PLATS) AS ETABLERINGAR FROM ART...

How to get return value or output value from stored procedure with sqldatasource control?
I created a user registration web page with sqldatasource, some textbox controls or submit button web control, I applied below stored procedure in insert command of sqldatasource control, but how can I get return value from stored procedure after clicking submit button, I want to judge whether data has been saved into database by return value, then give user a message. Who can tell me? thanks much. ALTER PROCEDURE UserInsert ( @Email nvarchar(50), @Password nvarchar(50), @FullName nvarchar(50), @Phone nvarchar(50), @Mobile nvarchar(50), @Address nvarchar(100), ...

return values with stored procedure
Hello Group I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page. Thank you Michael CREATE PROCEDURE stpMyAuthentication ( @fldUsername varchar( 50 ), @fldPassword Char( 25 ), @fldFullName varc...

Returning values from a stored procedure
PB8.0.4 build 10923 MS SQL Server 2000 I have a stored procedure declared as: CREATE PROCEDURE pCreateOrder @ItemID Int, @Quantity Int, @Location Int = NULL AS DECLARE @OrderID Int .... paraphrased INSERT INTO DebugLog (Message) SELECT 'Value is: ' + Cast (@OrderID AS VarChar(50)) RETURN @OrderID In PowerBuilder, I have this declared as an RPC Func as follows: FUNCTION Int pCreateOrder (long al_ItemID, long al_Quantity) RPCFUNC ALIAS FOR 'dbo.pCreateOrder' In my script, I call the RPC Func as follows: ll_OrderID = SQLCA.pCreateOrder...

stored procedure not returning value
Hi GuysI'm having a problem whereby a stored procedure that I have created and am calling via a class is not returning a value:The following stored procedure is added to a tableadapter:  1 ALTER PROCEDURE dbo.sp_InsertClub2 3 (4 @title nvarchar(100),5 @author nvarchar(100),6 @body ntext,7 @date datetime,8 @categoryID int,9 @sticky bit,10 @page int11 )12 AS13 begin14 15 SET NOCOUNT ON16 insert into blogEntries (title, postedBy, body, datePosted, categoryID, sticky,page)17 ...

Web resources about - Problem with Returning a Value from a Stored Procedure to Powerbuilder - sybase.sqlanywhere.general

PowerBuilder - Wikipedia, the free encyclopedia
PowerBuilder is an integrated development environment owned by Sybase , a division of SAP . It has been in use since 1991, peaking around 1998 ...

Sybase PowerBuilder tool readied for Microsoft's .Net
... Win32 or newer style .Net development After several years of work, Sybase is ready to deliver on the final step in its plan to move PowerBuilder ...

Jim O'Neil (@jimoneil) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Open Directory - Computers: Programming: Languages
about dmoz - dmoz blog - report abuse/spam - help the entire directory only in Programming/Languages Description Top : Computers : Programming ...

The HP Booth Staff (Goons) Owe Me an Apology
... when you might need to use it. For instance, when I was in the WinRunner world there were plugins for driving terminal emulators and powerbuilder ...

Contact Us - Sybase Inc
Thanks for visiting the 主页 section of Sybase.com. Here you will find information about Contact Us. For more information about Business Intelligence, ...

热门搜索 - 我的异常网
... 我的异常网 » 热门搜索 sdpnet2 移动【A111】其它用户原因鉴权失败,是什么意思啊 http:// 192.168.1.102:22578 本网站服务器位于美国,受美国法律保护 MiniUI js 破解 http://192.168.1.103:8080/ powerbuilder ...

Mobile app security: Always keep the back door locked
The best way to keep mobile apps safe is to secure the services they connect to.

10 Tech Skills That Will Instantly Net You A $100,000+ Salary
... that let's developers run Java applications. Java is highly popular language for writing web apps and custom enterprise apps. No. 8: PowerBuilder ...

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Resources last updated: 12/28/2015 7:38:21 PM