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!!!