Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Set Chained OFF error while executing from my Bean

 
y pasha
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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!!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic