File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB and other Java EE Technologies and the fly likes Can I call a Java Stored Procedure from EJB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Can I call a Java Stored Procedure from EJB" Watch "Can I call a Java Stored Procedure from EJB" New topic
Author

Can I call a Java Stored Procedure from EJB

Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi all,

Can I call a Java Stored Procedure from a business method of an EJB?

If yes, is it mandatory to use a Bean Managed Bean for the same so as to use the Callable Statement?

Rgds,

Seetesh
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Can I call a Java Stored Procedure from a business method of an EJB?


Yes you can certainly call.

If yes, is it mandatory to use a Bean Managed Bean for the same so as to use the Callable Statement?


You could use CMP as well. I would like to what are you doing in the stored procedure. Are you managing the persistent field retrieval/update etc. In such a case BMP should be used. CMP is meant to free the developer from handling SQL select/update/insert.


Groovy
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradeep,

Thks for ur post.

Actually I am replacing all my code written in Oracle PL/SQL Stored procedure to Java Stored Procedure as per the coding standards laid for this project.

I have a couple of methods defined in my Java Stored Procedure and all these are connecting to the database to fetch a couple of records or single String value.

I have to create a procedures and/or functions based on the return type of these methods created in Java Stored Proc.

Problem 1 : Not able to connect to database to fetch any single string value.

public class SeeteshCode
{
public static String getDetData(String str_AccountCode)
{
String str_schemecode = getSchemeDetails(str_AccountCode);
System.out.println(str_schemecode);
if (str_schemecode.length() > 0)
{
return "true";
}
else
{
return "false";
}
}


public static String getSchemeDetails(String str_AccountCode)
{
String str_schemecode = "";
java.sql.Connection con = null;
java.sql.ResultSet rs = null;
java.sql.Statement stmt = null;
try
{
String str_query = "SELECT scheme_code_txt, inv_sgrp_code_txt, inv_dk_account_code_txt, dkp_transfer_allowed_flag FROM M_GROUPTYPE WHERE UPPER(inv_grp_code_txt) = '"+str_AccountCode+"'";
System.out.println(str_query);
String str_url = "jdbc racle:thin:@bomw001:1521 atadb";
System.out.println("1");
java.lang.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("2");
con = java.sql.DriverManager.getConnection("jdbc dbc racleodbc","seetesh","seetesh");
System.out.println("3");
stmt = con.createStatement();
System.out.println("4");
rs = stmt.executeQuery(str_query);
System.out.println("5");
while (rs.next())
{
str_schemecode = rs.getString("scheme_code_txt");
System.out.println(str_schemecode);
}
}
catch(java.sql.SQLException se){}
catch(java.lang.Exception ee){}
finally
{
try
{
if(con != null)
{
con.close();
}
}
catch(java.lang.Exception e12) {}
}
System.out.println(str_schemecode);
return str_schemecode;
}
}

No errors displayed while executing the code but displays just "1".

Rgds,

Seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi,

To add to my problems, I am now getting the following error.

ERROR! Adding Employee: the Permission (java.net.SocketPermission bom019
resolve) has not been granted to SEETESH. The PL/SQL to grant this is
dbms_java.grant_permission( 'SEETESH', 'SYS:java.net.SocketPermission',
'bom019', 'resolve' )

where bom019 is my database server name

Rgds,

Seetesh
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

I think you need to post in Oracle forum. Looks like some permission was not granted. Why don't you check out the Oracle java stored procedure doc for more details.
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradeep,

I have give all possible grants to this user including DBA.

Problem is socketexception now.

Rgds,

Seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradedp,

Coming back to my main query.

If I am using CMP, how do I call the Java Stored Proc?

If I am using CallableStatement, I have to use only BMP to get the connection object.

Correct me if i am wrong.

Rgds,

Seetesh
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

If I am using CallableStatement, I have to use only BMP to get the connection object.

You could do a JNDI lookup for DataSource and get the connection.
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradeep,

yes. And write the same code declaring the bean as Bean managed.

Rgds,

Seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradeep,

Cld u tell me the procedure how u have written a java stored procedure containing the database access logic?

Did u create the create or replace ...... by logging on to the oracle database server or the same was tried on your machine which had a oracle server installed?

Rgds,

Seetesh
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradeep,

I have the code with me. All I want to know is how did u compiled, run the loadjava and executed the Java Stored Proc.

Did u try to connect to Oracle database server (placed remotely) with oracle client installed on ur comp or did u create and run the loadjava command along with other required commands directly on you comp having the Oracle Database server being installed.

Rgds,

Seetesh
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

My database server is located on a remote machine.

Here are steps
http://www.oracle.com/technology/sample_code/tech/java/jsp/samples/jspfromanonblock/readme.html#prepare
[ October 12, 2004: Message edited by: Pradeep Bhat ]
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradeep,

guess u've used JDeveloper to deploy ur Java Stored Procedure.

Did u try to deploy the same using the other conventional way as we dont have JDeveloper installed.

Rgds,

Seetesh
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

I did not deploy using JDeveloper. Did you search for the error in the OTN forum?
Rohit Ahuja
Ranch Hand

Joined: Oct 04, 2001
Posts: 121
Hi mates,
Why are u making things difficult for yourself ? Why dont u just use a simple callable statement and call an oracle procedure and then try to call that java procedure from the oracle procedure. It is hardly an overhead. What do u think Pradeep and Seetesh ? I would prefer u do all these things in a stateless session bean which wud be called by code in ur jsp.
[ November 19, 2004: Message edited by: Deepak Acharya ]

Face Off.
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Deepak,

Any sample code to test ur logic.

seetesh
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hi Pradeep,

The error boils down to the Connection object creation logic.

How do I get an Oracle connection as per the code given in one of the links u have mentioned above ie
connection = new OracleDriver().defaultConnection();

Rgds,

Seetesh
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Originally posted by Seetesh Hindlekar:
Hi Pradeep,

The error boils down to the Connection object creation logic.

How do I get an Oracle connection as per the code given in one of the links u have mentioned above ie
connection = new OracleDriver().defaultConnection();

Rgds,

Seetesh


I haven't understood the problem. Are you not able to compile the file?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can I call a Java Stored Procedure from EJB