Solved

reading return codes from stored procedure execution

Posted on 2003-10-31
11
4,630 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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. …
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.
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…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

832 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