Solved

reading return codes from stored procedure execution

Posted on 2003-10-31
11
4,581 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
eclipse error 7 86
error when doin maven update 2 89
HTML/Webpage Editor for Windows 10 20 126
CKEditor - will not function 7 35
Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
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…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now