Help urgent regarding Session Bean JDBC call

[Harpal Singh]

Dear guys,
I have wasted hell lot of time on this....now I think u guys gotta help me out....HOW DO I MAKE A SIMPLE JDBC CALL TO SQL7 OR CLOUDSCAPE FROM MY SESSION BEAN??IF I USE SQL7 I GET ERROR NO SUITABLE DRIVER FOUND,IF I USE CLOUDSCAPE I GET AN ERROR LIKE NO CLASS DEFINATION FOUND!!!
CANSOME ONE JUST WRITE A SIMPLE CODE WHICH I CAN JUST INTEGRATE IN MY SESSION BEAN CODE.......
THANKS,
HARPAL;(

[Bill Pearce]

Harpal,
Your problem sounds more like a classpath problem to me (based on the error you say you are getting with SQL7), but here is something I have that works for me. First, I do all my SQL interaction in a helper class, not in the bean class itself. The bean can then focus on being a bean, and not worry about all that database stuff. For example, the getCustomerName method of my session bean:

public String getCustomerName(long id) {
  String name = null;

  try {
    name = helper.getCustomerName(id);

  } catch (Exception e) {
    System.out.println("Exception in CustomerEJB.getCustomerName: " + e);
  }
  return name;
}

The "helper" is a private variable of my CustomerEJB that is instantiated in the ejbCreate() method. The helper.getCustomerName(id) method then looks like this:

public String getCustomerName(long id) throws java.sql.SQLException {

  Connection con = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  String name = null;

  try {
    con = getConnection();
    stmt = con.prepareStatement("SELECT name FROM customer WHERE customer_id = ?");

    stmt.setLong(1, id);
    rs = stmt.executeQuery();
    if(rs.next()) {
      name = rs.getString(1);
    } 
    return name;

    } finally {
      closeResources(con, stmt, rs);
    }
}

Finally, my helper.getConnection() method simply returns a JDBC connection:

private Connection getConnection() throws java.sql.SQLException
{
  Connection c = null;

  try {

    c = DriverManager.getConnection("jdbc  :oracle:thin:@192.168.45.1:1500:db_inst", "user", "pass");

  } catch (Exception e) {
    System.out.println("Exception in CustomerHelper.getConnection: " + e);
  }
  return c;
}

Hope this helps a little. But I still think you need to make sure you have the JDBC classes in your CLASSPATH first. Can you get a connection and execute a statement in your java code at all, or just not in your bean?
Good luck,
Bill
[This message has been edited by Bill Pearce (edited February 13, 2001).]
[This message has been edited by Bill Pearce (edited February 13, 2001).]
[This message has been edited by Bill Pearce (edited February 13, 2001).]

[Harpal Singh]

Dear Bill,
Thanks a lot my friend!I think this is a nice way to get the records from database,Yes,I can access data in my database if I access it through my ordinary java class....but why not through my Session Bean,I can also access it through a Servlet...but problem comes when I try it from my Session Bean class..
Your Solution solves my problem for time being but I would really appreciate if you try from your Bean class and let me know How to do it??
Thanks pal,
Harpal

[Harpal Singh]

Dear Bill,
It does not work I am still getting the same errors,I guess it has something to do with the configuration of default.properties file ,it gives me still the same error i.e. No Suitable driver found....
Well,Back to square one.
Harpal

[NageswaraRao Karra]

Hello!
is it not necessary to mention the driver you are using in auth.properties file if it is other than cloudscape database?
correct me if i am wrong

[Harpal Singh]

dear Nag........,
If I use cloudscape also I get Exceptions but different ones like No Class Defination,IllegalAcessException...I also tried pointing my J2EE Classpath to jdbc drivers but that also does not help.............
Harpal ;(

[Frank Lin]

Dear guys, resource management, including DB connections, are implicitly being handled by EJB container which prohibit beans from making direct I/O, threading or DB connections. No wonder you are getting exceptions.

[Harpal Singh]

Dear Frank,

if this is a restriction or say security feature of EJB Container what is the solution to it ,what can be done to fetch records from the database from a bean say session bean..
Harpal

[Frank Lin]

You are supposed to use Entity beans if you follow the J2EE paradigm.