Solved

reading return codes from stored procedure execution

Posted on 2003-10-31
11
4,667 Views
Last Modified: 2013-12-26
We currently have a pb application that executes a stored procedure as follows:

The stored procedure is declared in the transaction object as a local external function:
   function long abs_s_admin_recalc(datetime statement_date,string credit_account_nmbr, string retro, string calc_type, decimal data_origination_cd_in, decimal data_origination_cd_out) RPCFUNC ALIAS FOR "dbo.abs_s_admin_recalc"

It is then executed in the app as follows:
SQLCA.AutoCommit = TRUE

ll_rc = SQLCA.abs_s_admin_recalc(DateTime(idt_end_dt),"ALL","N",'both',3,19)

If SQLCA.SQLCode < 0 then
    generate error
    SQLCA.AutoCommit = FALSE
    return -1
end if

SQLCA.AutoCommit = FALSE


I've added return code processing to the stored procedure by putting statements as follows after each sql:

        IF (@@error <> 0)
      RETURN -56

I want to pull this return code when I get back to my app.  I'm not understanding how to obtain the error code.  I don't see it in sqlca and I don't get it in my ll_rc value either on my execute statement.

How do I get the actual value I returned?


BTW, I'm using a sql2000 database.
0
Comment
Question by:babybird
11 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9660634
I am not sure if sqlca.sqldbcode is the one. This is a vendor specific err code. I thought ll_rc would return the value returned using RETURN statement in Stored Proc.
0
 

Author Comment

by:babybird
ID: 9661250
If the proc takes a database error,  I'm able to identify that by checking sqlca.sqlcode and the database error is in sqldbcode.  But I'm not able to obtain the error I specified on the return statement.  This would be more helpful so I know specifically where in the proc it failed.  ll_rc comes back = 0 regardless of whether it errors or not.  

0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9661576
When you run the strored proc from query analyzer to get the right return code?

i.e
declare @rc
exec @rc = sp_yourproc @parm1, @etc
select @rc

Does you stored proc has a single exit point or mutiple exit point? Do you have a return statement at the end of proc?

0
Technology Partners: 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!

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9661594
Sorry. Should be.
@rc  = exec  sp_yourproc @parm1, @etc
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9661596
How is this sp declared in PB. RPCFUNC?
0
 
LVL 8

Assisted Solution

by:Vikas_Dixit
Vikas_Dixit earned 100 total points
ID: 9662211
Hi,
 Can you try getting the return value as an out paramater ??, Or by using the Declare procedue method instead ??

Regards,
Vikas Dixit
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 100 total points
ID: 9662826
I have sp declared as a RPCFUNC on n_tr (PFC App) as a local external function and I get the return code from sp without any problem.

Example:

function long f_myfunc(long my_id,string my_string) RPCFUNC ALIAS FOR "dbo.sp_myproc"

Or as Vikas suggests,

DECLARE sp_duration PROCEDURE FOR pr_date_diff_prd_ken

@var_date_1 = :ad_start,
      @var_date_2 = :ad_end,
      @rtn = :ll_rc OUTPUT;

In stored proc, before exiting you will need to,
select @rtn = -1 // or whatever

HTH

Namasi

0
 
LVL 8

Assisted Solution

by:gajender_99
gajender_99 earned 100 total points
ID: 9669840
hi

when you are declaring your function you have to declare it as a subroutine where i have added the last argument as REF DOUBLE return_value"

"SUBROUTINE ABS_S_ADMIN_RECALC(DATETIME STATEMENT_DATE,STRING CREDIT_ACCOUNT_NMBR, STRING RETRO, STRING CALC_TYPE, DECIMAL DATA_ORIGINATION_CD_IN, DECIMAL DATA_ORIGINATION_CD_OUT, REF DOUBLE RETURN_VALUE ) RPCFUNC ALIAS FOR "DBO.ABS_S_ADMIN_RECALC" "
AS A LOCAL EXTERNAL FUNCTION

double ll_rc
the variable ll_rc should be the last argument int you subroutine which can hold back the return value.

SQLCA.abs_s_admin_recalc(DateTime(idt_end_dt),"ALL","N",'both',3,19,ll_rc)

If SQLCA.SQLCode < 0 then
    generate error
    SQLCA.AutoCommit = FALSE
    return -1
end if

if ll_rc = -56 then
  // your condition
Else
//your condition
End if

one more thing to rembember
the PROCEDURE which you are exceuting should have the last argument defined as
IN OUT INTEGER
//Example
ABS_S_ADMIN_RECALC(a in integer, b in integer, v_status IN OUT INTEGER)

so you can always return a value in V_STATUS  and get that value in ll_RC

I am working on this type of procedures for around three years.
if you have any problem let me know. i can give you a detail example.

thanks
Gajender


0
 
LVL 1

Expert Comment

by:sandhiyaa
ID: 9670258
Hi,
U cannot get the value of the return code that u have used in the procedure.
sinc sqlcode returns u the prefined value ie 1 ,0,100.
i got the same problem.
i solve it ,by using ouptput parameter and according to the parameter if displayed the error message and placed the cursor.

Regards,
sandhiya
0
 
LVL 8

Expert Comment

by:gajender_99
ID: 9670320
Hi Sandhiyaa

the SQLcode we use is to check weather our  procedure worked or not.
I we wnat to return a value from the procedure then we have to use a in and out variable in the calling of the procedure.
this is what i have explained.

thanks Gajender

0
 
LVL 5

Accepted Solution

by:
berXpert earned 200 total points
ID: 9673720
Hi,

if you want your code catch your own error code, try to use "raiseerror" statment in your stored procedure, here's an example:



declare  @errno   int,
         @errmsg  varchar(255)

/* Make a validation and RAISE the error*/
IF  ( 1 < 2 )
begin
        select @errno  = 30056,
        @errmsg = 'Put your message here, it will appear in SQLCA.SQLErrText '
        raiserror @errno @errmsg
        rollback transaction
        return
end



Hope this helps,
berX
0

Featured Post

Independent Software Vendors: 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

Suggested Solutions

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
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 perform CRUD operations on a MySql database.

740 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