• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Can I call a Java Stored Procedure from EJB

 
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Pradeep,

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

Problem is socketexception now.

Rgds,

Seetesh
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Pradeep,

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

Rgds,

Seetesh
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I did not deploy using JDeveloper. Did you search for the error in the OTN forum?
 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Deepak,

Any sample code to test ur logic.

seetesh
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic