wood burning stoves 2.0*
The moose likes EJB and other Java EE Technologies and the fly likes Set Chained OFF error while executing from my Bean Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Set Chained OFF error while executing from my Bean" Watch "Set Chained OFF error while executing from my Bean" New topic
Author

Set Chained OFF error while executing from my Bean

y pasha
Greenhorn

Joined: Jan 23, 2011
Posts: 1
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!!!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Set Chained OFF error while executing from my Bean