Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Java-Sybase query

Posted on 2006-04-25
15
Medium Priority
?
966 Views
Last Modified: 2008-01-09
Hi all,

Not sure if i should be putting this in sybase area or java area..
but I am putting in java area because I am gettign an exception in java jsp page when trying to execute this stored procedure while just in sybase it is running fine..

exception i get is

"set chained command not found for multi statement transaction"

any ideas?

anything you see in stored procedure which should be built differently..

basically, last statement "select" is suppose to return the charge_id based on type of operation "insert", "update" or "delete"...

looks like in certain scenario this last select is not being executed properly..

please help

IF OBJECT_ID('dbo.chargeMaintenance') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.chargeMaintenance
    IF OBJECT_ID('dbo.chargeMaintenance') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.chargeMaintenance >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.chargeMaintenance >>>'
END
go
CREATE PROCEDURE dbo.chargeMaintenance @operation int, @contextId int, @subcontextId int,
                           @description varchar(50) = null, @chargeId int = null,
                           @max_charge varchar(50) = null, @min_charge varchar(50) =null,
                           @is_recalc_at_alloc bit = null, @is_calc_at_block bit = null,
                           @is_sliding_scale bit = null, @basedOn int=null,
                           @last_user char(7) = null, @effective_date datetime = null,
                           @is_for_buy bit = null, @is_for_sell bit=null,
                           @currency char(5) = null,@is_zero_for_MM bit = null
                           
AS
   /*
    *  if @operation is an update or a delete, then @chargeId should be passed.
    */
   DECLARE @refChargeId int
   DECLARE @next_id int
   DECLARE @version int
   DECLARE @newChargeId int
BEGIN
   
    IF (@operation = 0) -- IS an insert?
    BEGIN
      exec insertCharge @contextId, @subcontextId, @description,  
           @max_charge, @min_charge, @is_recalc_at_alloc, @is_calc_at_block,
           @is_sliding_scale, @basedOn, @last_user, @effective_date, @is_for_buy,
           @is_for_sell, @currency, @is_zero_for_MM, @newChargeId output
    END --IF (@operation = 0)
    ELSE IF (@operation = 1 and @chargeId is not null)-- is an update?
    BEGIN
      exec updateCharge @contextId, @subcontextId, @description, @chargeId,
           @max_charge, @min_charge, @is_recalc_at_alloc, @is_calc_at_block,
           @is_sliding_scale, @basedOn, @last_user, @effective_date, @is_for_buy,
           @is_for_sell, @currency, @is_zero_for_MM, @newChargeId output
    END -- IF (@operation = 1)
    ELSE IF (@operation = 2) -- is a delete?
    BEGIN
      UPDATE Charge
         SET valid = 0,
             last_user = @last_user,
             last_updated = getdate()
       WHERE id = @chargeId
        SELECT @newChargeId = @chargeId
    END -- IF (@operation = 2)

    SELECT c.id as chargeId,
          context_id,subcontext_id, ref_charge_id,max_charge,    
 min_charge,is_recalc_at_alloc,is_calc_at_block,is_sliding_scale,
          basedOn,last_user,effective_date,    
          version,is_for_buy,is_for_sell,currency,is_zero_for_MM,
          rc.id, rc.description, rc.is_discretionary, rc.charge_classname, rc.calc_classname, rc.is_currency_specific, rc.execountry, rc.short_description, rc.charge_precision, rc.charge_precision_type                      
    FROM Charge c INNER JOIN RefCharge rc
      ON c.ref_charge_id = rc.id
     where c.id = @newChargeId
END
go
IF OBJECT_ID('dbo.chargeMaintenance') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.chargeMaintenance >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.chargeMaintenance >>>'
go
EXEC sp_procxmode 'dbo.chargeMaintenance','unchained'
go
0
Comment
Question by:hunky_sh
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 16535529
How are you calling it in your Java code?
0
 

Author Comment

by:hunky_sh
ID: 16536653
as a callable statement...

but in certain instances -- we are passing the callable stmt to a method which basically accetps PreparedStmt..

but it should not matter right ??

callableStmt is a subclass of preparedStmt..  so it should not have an issue..

this problem is coming up for some reason while we request from JSP  which calls java methods (using struts) and inside that in one of the call we use callableStmt and then pass it to a method which really accepts preparedStmt...


0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16536879
Post a link to this in the Sybase area as well.
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!

 

Author Comment

by:hunky_sh
ID: 16537076
i did now
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16537123
>> inside that in one of the call we use callableStmt and then pass it to a method which really accepts preparedStmt

What does the code of that method look like?
0
 

Author Comment

by:hunky_sh
ID: 16537337
you mean one which accepts the preparedStmt or one which has a callableable stmt?
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16538271
preparedStmt
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 16540340
I've given you a detailed answer in the Sybase area, but I briefly - I suspect this is a "procedure transaction mode" issue. The answer in the entry in the Sybase area goes into detail.
0
 

Author Comment

by:hunky_sh
ID: 16544069
All,

I have answered in Sybase area with some questions..
i guess i know the issue -- issue is transactions..

i am just not able to figure out

1). how to manage transactions in Jdbc?

2). Where should the transaction be managed? Sybase? JDBC? or both?

Sybase transactions -- Joe has given me nice description -- i have some additional questions which Joe or anybody can answer in Sybase area..

But about JDBC transactions -- why to manage transactions in JDBC if it can be managed in Sybase..

I guess I am unable to figure out how to design it..

thanks
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 600 total points
ID: 16548539
1) Just issue BEGIN/COMMIT/ROLLBACK statements as you would any other SQL.

2) Doesn't really matter where you do it, so long as you do it consistently. Mistakes happen when you combine transaction management both in application code (JDBC) and the database (stored procedures). Neither is superior. Pick one.
0
 

Author Comment

by:hunky_sh
ID: 16631561
But are you saying, transactions should be only at one place? either in JDBC? or Sybase? not at both places correct?
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 16635945
Well, do they *have* to be only at one place? No.

But it is *much* easier to code if all transaction management is done at the same place.

If you don't, you need complex code at every nesting level in both places. It's a lot of work.
0

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

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month15 days, 19 hours left to enroll

580 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