Problems Accessing a DataSource from EJB

[Former Member]

I'm having problems getting a DataSource to work from a Session Bean. Even after I followed the instructions at this link:

http://help.sap.com/saphelp_nw04/helpdata/en/7d/26e96f1d754408bfd658b6614cb1b6/content.htm

and setup a "Resource Reference" in ejb-jar.xml

<res-ref-name>jdbc/SAPJ2EDB</res-ref-name>

where SAPJ2EDB is of course the 'default' DataSource shown by Visual Administrator.

Here's the code snippet

    Connection conn = null;

    try {
        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SAPJ2EDB");
        conn = ds.getConnection();
        if (conn == null) {
            throw new SQLException("Connection Failed");
        }
        conn.setAutoCommit(false);
    } catch (SQLException sex) {
        sex.printStackTrace();
        return null;
    } catch (NamingException ex) {
        ex.printStackTrace();
        return null;
    }

What happens is this: no Exception is thrown, but no data is returned by the Bean's business method. In other words, no data displays in my Web Dynpro View. The problem is not my EJB method or my WD View because

[1] When I return a dummy String w/o hitting the database everything displays correctly.

[2] I have pulled the code into a console app and replaced the DataSource with a std JDBC connection; this works as expected

I don't understand because the results are the same no matter what I put into the lookup statement:

ctx.lookup("java:comp/env/jdbc/SAPJ2EDB")

ctx.lookup("jdbc/SAPJ2EDB")

If the lookup is failing, why don't I get an error??

[Former Member]

Bhavik -

Yesterday I did try an Alias. From the instructions in "Deploying a DataSource Alias with an Application", I created an alias in my Enterprise Application (MyProjectEar). Which created <b>data-source-aliases.xml</b>

<alias>ETIJ2EDB</alias>

I already had a Resource Reference (MyProjectEjb) in <b>ejb-jar.xml</b>

<res-ref-name>ETIJ2EDB</res-ref-name>

When I tested it, the NamingException was gone. Unfortunately, still no data is displayed in my Web Dynpro App.

**There seems to be a lot of confusion re: lookup

**Only the following does not throw a NamingException

ctx.lookup("java:comp/env/ETIJ2EDB")

I'm running out of options:

Do I have to create Yet Another Project - a Dictionary Project? Some other postings in this forum lead me to believe 'yes'. All of the Tutorials I have seen from SAP use a Dictionary Project? Too many projects, I would say. We certainly don't want to have to redefine 1200 database tables here.

Thanks again for your help.

[Former Member]

Hi Terry,

You are not getting any deta now when you call your EJB.

Means there would be some exception occured in your EJB application.So, do one thing write following code in your catch block to get error stack.

try{

File file = new File("c:
error.log");

FileWriter fr = new FileWriter(file);

e.printStackTrace(new PrintWriter(fr));

fr.flush();

fr.close();

}

catch(Exception ex)

{

}

It will create one file on server's c drive.You can check then what was the error?

Regards,

Bhavik

[Former Member]

Bhavik -

You are right again. I had several OpenSQLException's that I couldn't see.

I am new to OpenSQL, can't say I like it. One of my problems was using ResultSet.getString() to fetch all data types. This works in 'plain' JDBC I use it all the time. My two test tables had DB types of DATE and INTERGER. Once I fixed this, I got some data.

FINALLY it's working. Thanks to everyone for their help.

[Former Member]

Hi Terry,

Nice to hear that you got the solution.

Please now close this thread.

Don't forget to award points if its helpfull.

Regards,

Bhavik

[Former Member]

Hi Terry,

Change the line DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SAPJ2EDB"); to the following:

DataSource ds = (DataSource)ctx.lookup("jdbc/SAPJ2EDB");

This would work.

Cheers,

Rahul

[Former Member]

Rahul -

If you look at my original posting, I tried this also. But, I tried it again with the same results - no data is returned.

Thank you for the suggestion.

[Former Member]

Hi Terry,

Are you catching your SQLException properly?

And where you are catching this Exception?

Do one thing.

Try with the following code.

try {

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SAPJ2EDB");

conn = ds.getConnection();

conn.setAutoCommit(false);

} catch (SQLException sex) {

File file = new File("c:
error.log");

FileWriter fs = new FileWriter(file);

sex.printStackTrace(new PrintWriter(fs));

fs.flush();

fs.close();

return null;

} catch (NamingException ex) {

File file = new File("c:
error_name.log");

FileWriter fs = new FileWriter(file);

ex.printStackTrace(new PrintWriter(fs));

fs.flush();

fs.close();

return null;

}

Here, When you are catching your exception write the errorstack into file.

And then check whether this file is created at that location on server or not. If it is created then it will contain whole error stack.

regards,

Bhavik

[Former Member]

Bhavik -

Good call. I did what you suggested and a NamingException is thrown. The server must not be trapping the stack trace.

Here is the pertinent text from the exception with lines wrapped so you can see:

com.sap.engine.services.jndi.persistent.exceptions.NamingException:
    Exception during lookup operation of object with name
    ejbContexts/sap.com/EtiDBTestEar/DBSessionBean/java:comp/env/jdbc/SAPJ2EDB,
        cannot resolve object reference.
    [Root exception is com.sap.engine.services.connector.exceptions.
        BaseResourceException: ConnectionFactory "jdbc/SAPJ2EDB" does not exist.
            Possible reasons: the connector in which ConnectionFactory
            "jdbc/SAPJ2EDB" is defined is not deployed or not started.]

Do you know what to do to get the Connector started? the DataSource SAPJ2EDB is there and as far as I can tell active?

Thanks for your help.

[Former Member]

Hi Terry,

You have specified your Datasource name in Ejb-jar.xml file, but have you specified this datasource in data-source-aliases.xml file?

You have to specify this in Enterprise application project also.

Try to give some alias name like TEST_DB in both xml files and then in code for lookup specify following string:

"java:comp/env/TEST_DB" it will work.

Regards,

Bhavik