Win a copy of Terraform in Action this week in the Cloud forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Set Chained OFF error while executing from my Bean

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

I am calling a Stroed procedure form my ejb bean class and when iam trying to call the procedure i am facing a problem like Stored procedure 'my_Procedure' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode
Generaly my Stored procedure is calling another stored procedure inside it... and i cannot change the Procedure to any mode as its the restriction from DBA, i tried handing the error by setting con.setAutoCommit(false); after tha connection, but still i face error like "SET CHAINED command not allowed within multi-statement transaction.
can anyone please help me in resolving this issue as iam in critical phase of my project..
my code:
public ContactResponse ContactInputRIM(ContactInput inp){
ContactResponse set = new ContactResponse();
MwDbConnection mdc = new MwDbConnection();
Connection conn = null;
try {
conn = mdc.getDbConnection(Constant.MyConnection);
conn.setAutoCommit(false);
CallableStatement cs = conn.prepareCall("{call MyProcedure(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
cs.setInt(1, a);
cs.setString(2, b);
cs.setString(3, c);
cs.setString(4, d);
cs.setString(5, e);
cs.setString(6, f);
cs.setString(7, g);
cs.setString(8, h);
cs.setString(9, i);
cs.setString(10, j);
cs.setString(11, k);
cs.setString(12, l);
cs.setString(13, m);
int count = cs.executeUpdate();
if(count>0) {
throw new Exception("Success");
} else {
throw new Exception("Failure");
}
cs.close();
conn.close();
} catch (Exception e) {
throw new Exception("cannot update");
}
}
return ret;
}
}

===========
my Stored procedure:

IF OBJECT_ID('MyProcedure') IS NOT NULL
BEGIN
DROP PROCEDURE MyProcedure
IF OBJECT_ID('MyProcedure') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE MyProcedure >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE MyProcedure >>>'
END
go
create proc MyProcedure
a int,
b varchar(40),
c varchar(40),
d varchar(40),
e varchar(40),
f varchar(40),
g varchar(4),
h varchar(40),
i varchar(40),
j varchar(40),
k varchar(40),
l varchar(40),
m varchar(40)

As
declare
@param1 varchar(200),
@param2 varchar(200),
@DateParam DATETIME



select @param1='CUS "U" , NULL , NULL , NULL , NULL , NULL ,'+convert(varchar(10),a) +', NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL ,'

select @param2='ADD 1,1,NULL,NULL,NULL,"'+c+'","'+d+'","'+e+'","'+f+'",NULL,"'+g+'","'+b+'","'+h+'",NULL,"'+i+'",NULL,"'+j+'",NULL,NULL,NULL,NULL,1,NULL,NULL,"'+k+'",NULL,"'+l+'","'+m+'",NULL,'

---------------------------------------------------------------------------------
BEGIN
exec 2ndProcedure '"D" , "DB1" , "DB2", ''2008-02-01'', "NULL" , "NULL" , "NULL" , "NULL"',
'1 , 1 , 77 , NULL',
@param1,

' NULL , NULL , NULL , NULL ,',

'NULL , NULL , "Y" , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL ,',

'NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL ,',

'NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL ,',

'NULL, NULL , NULL , NULL , NULL, NULL, NULL ,NULL ',

@param2,'NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL'

END


go
EXEC sp_procxmode 'MyProcedure','anymode'
go
IF OBJECT_ID('MyProcedure') IS NOT NULL
PRINT '<<< CREATED PROCEDURE MyProcedure >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE MyProcedure >>>'
go

please advice guys!!!
reply
    Bookmark Topic Watch Topic
  • New Topic