powerbuilder , stored procedure, output parameters

Posted on 2009-04-21
Medium Priority
Last Modified: 2013-12-26
i have created a stored procedure in sql server 2000 as follows  (only the parameters are included)

CREATE procedure sp_update_logist_01
 @scr_code_ph            nvarchar(08)
,@scr_date                                 datetime
,@scr_xwros            nvarchar(2)
,@scr_n_seira            nvarchar(2)
,@scr_parast            nvarchar(2)
,@scr_parast_no            decimal
,@scr_status            int  output

how can i execute this stored procedure from powerbuilder ? How can i receive the value of @scr_status which is an output parameter and store its value to a local int variable?
Question by:basilhs_s
LVL 14

Expert Comment

ID: 24195349
There are several ways...

If stored procedure is supposed to return multiple records (again... records not the columns)
for given input then i would recommend to create datawindow using that stored procedure and retrieve it.

Otherwise, create stored function instead of stored procedure.

Both stored procedure and stored functions can be also called as RPC.
1. create non-visual object of type Transaction
2. declare stored procedure or function as external function to it.
subroutine sp_update_logist_01(string var1,datetime var2, string var3,string var4,string var4, dec var5, ref integer var6) RPCFUNC ALIAS FOR "sp_update_logist_01"
(see pb help - 'Declaring DBMS stored procedures as remote procedure calls' for more detail)
3. change sqlca property to above transaction object in your application object.
Open applicaiton object > properties > general tab.. click additional properties > variable types tab... change sqlca value.
4. now you can call stored procedure from anywhere by
sqlca.sp_update_logist_01(arg1, arg2,....,arg6)
where arg6 is the result returned from procedure.

LVL 18

Accepted Solution

diasroshan earned 2000 total points
ID: 24195507

ur script wud be like this,
DECLARE sp_update_logist_01_proc PROCEDURE FOR sp_update_logist_01
                        @scr_code_ph = :ls_scr_code_ph ,
                        @scr_date    = :ldt_scr_date,
                        @scr_xwros   = :ls_scr_xwros,
                        @scr_n_seira = :ls_scr_n_seira,
                        @scr_parast  = :ls_scr_parast,
                        @scr_parast_no = :ls_scr_parast_no,
                        @scr_status    = :li_scr_status output

                  USING SQLCA ;
            EXECUTE sp_update_logist_01_proc;
            Choose case Sqlca.sqlcode
                  Case 0
                        FETCH sp_update_logist_01_proc INTO :li_scr_status ;
                        CLOSE sp_update_logist_01_proc;
                  Case 100
                        CLOSE sp_update_logist_01_proc;
                        Return -1
                  Case else
                        CLOSE sp_update_logist_01;
                        Return -1
            End Choose


Author Closing Comment

ID: 31572696
this is exactly what i want

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question