is it possible to call an oracle stored procedure in ejb?
2 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   ritesh_menon
Posted On:   Thursday, October 4, 2001 02:38 AM

cud i have some test code regarding the
same....

Re: is it possible to call an oracle stored procedure in ejb?

Posted By:   Senthil_Kumaran  
Posted On:   Wednesday, October 10, 2001 12:44 PM

You can write a Session bean to call stored procedures.


Le us say you have a store proc by name
tran_stat_sp which takes 3 input params and does insertion.
Let us say first 2 params are String and the 3rd one is integer.


Then the code looks like this


try{
String mySql = "{call tran_stat_sp(?,?,?)}";
Context ctx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("DATASOURCENAME");
Connection conn = ds.getConnection();
CallableStatement myst = conn.prepareCall(mySql);
myst.setString(1, "value1");
myst.setString(2, "value2");
myst.setInt(3, 21);
myst.executeUpdate();
myst.close();
conn.close();
}



If a Store Proc takes some input and reads several tables and inserts records in to a table AA,then after calling executeupdate method in Session bean, call the entity bean AABean (which maps to the result table AA) to read inserted values




Read Sun's EJB book by Vlada Matena talks about why Session beans instead of BMP Entiy beans.I think writing this code in Session beans gives good performance.




Hope this helps

Re: is it possible to call an oracle stored procedure in ejb?

Posted By:   Raymond_Blum  
Posted On:   Thursday, October 4, 2001 07:28 PM

Sure, if your EJB is using Bean Managed Persistence it is fairly straighforward but I would caution against this on principles of design.

If your stored procedure does an update/delete/insert it can be called from
executeUpdate() like any other SQL statement.

If you want to call a stored procedure to run a query, you need to do some work in the EJB, you would code the stored procedure to insert rows into a transient table and then execute a query against that table.

About | Sitemap | Contact