Problems Setting up Oracle DataSource

[Former Member]

Hello,

I am trying to set up an Oracle DataSource for a Web Dynpro App using a Stateless Session Bean for database access. I want to use Connection Pooling i.e. I don't want to use straight JDBC. Following the instructions in the Admin. Manual, I setup a new Driver and DataSource for Oracle 8i (8.1.7)

Driver: 
    name = 'ORACLE_DRIVER'
    jar = C:OracleOra81jdbclibclasses12.zip

DataSource:
    application = 'sap.com/EtiRegisterOracle'
    name = 'ORAJ2EDB'
    aliases = none
    jdbc version = 1.x
    driver = oracle.jdbc.driver.OracleDriver
    URL = jdbc:oracle:thin:@localhost:1521:orcl817
    SQL Engine = Vendor_SQL

I do not want to use OpenSQL which as far as I can tell is for SAPDB only(?)

-

---

Using this code (in a try/catch block)

    // Get a connection using a DataSource
    InitialContext ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup("jdbc/ORAJ2EDB");
    conn = ds.getConnection();
    conn.setAutoCommit(false);

I get the following exception @ ds.getConnection()

com.sap.engine.services.dbpool.exceptions.BaseSQLException:
ResourceException in method ConnectionFactoryImpl.getConnection():
    com.sap.engine.services.dbpool.exceptions.BaseResourceException:
SQLException thrown by the physical connection:
com.sap.sql.log.OpenSQLException: JDBC driver not supported for ORACLE database.

which I don't understand at all. I can't find any mention of "JDBC driver not supported" anywhere. And why is it an OpenSQLException?? Remember, I'm trying to use Vendor_SQL.

There seems to be very little little information in these forums of how to use any database other than SAPDB (MaxDB).

Help, please.

[guru_subramanianb · Accepted solution]

Hi Terry,

Jus a check point, did you load the oracle driver file in your "lib" folder of your webdynpro project.

Also here is a list of exception being explained.Have a look.

http://help.sap.com/saphelp_nw04/helpdata/en/24/d4ff0eccf33a49a5c26a280a119b14/frameset.htm

Hope it hepls.

Regards,

Guru

[Former Member · Accepted solution]

Guru -

<b>>Just a check point, did you load the oracle driver file

>in your "lib" folder of your webdynpro project.

</b>

I can't see why this would be necessary -- as the Oracle Driver file (classes12.zip) is specified in ORACLE_DRIVER? The problem does not appear to be that the jdbc driver cannot be found.

But, I will certainly try it.

********************************************************

• Do you know if this even works with Oracle 8i or do I

• need Oracle 9i at minimum?

********************************************************

<b>>Also here is a list of exception being explained. Have

>a look.

>http://help.sap.com/saphelp_nw04/helpdata/en/24/d4ff0eccf33a49a5c26a280a119b14/frameset.htm

</b>

I haven't used this SQL Trace. What will it tell me? Does it give me more info about the OpenSQLException?

********************************************************

• My main question "why am I getting OpenSQLException when

• I'm trying to not use OpenSQL" ?? I setup the DataSource

• to use Vendor_SQL.

********************************************************

Thank you, any help is appreciated.

[Former Member]

I solved my problem. Again, I am very surprised at the lack of information in this forum regarding how to setup a DataSource for other than SAPDB. I found other postings for the same problems with Oracle, <b>but no detailed solution</b>.

I will remedy this deficiency by providing detailed instructions for how to set up an Oracle DataSource and Connection Pooling for Oracle 8i using Visual Administrator. Yes, it does work with 8i. But you must use JDBC 2.0, not JDBC 1.x.

Of course, modify the settings to fit you environment.

Step 1. <b>Create a new Driver</b>

Name : ORACLE_DRIVER

Lib(s) : C:\Oracle\Ora81\jdbc\lib\classes12.zip

[Properties]

SQLEngine = Vendor_SQL

Step 2. <b>Create a new DataSource</b>

NOTE: the property names are case-sensitive and you must use {url, user, password} and not {URL, User, Password} as in other postings.

Application Name : EtiRegisterOracle

DataSource Name : ORAJ2EDB

Aliases : none

JDBC Version : 2.0 (with XA Support)

Object Factory : oracle.jdbc.pool.OracleDataSourceFactory

DataSource Type : ConnectionPoolDataSource

CPDS Classname : oracle.jdbc.pool.OracleConnectionPoolDataSource

[Additional] Properties:

url = jdbc:oracle:thin:@localhost:1521:orcl817

user = scott

password = tiger

[Additional] SQL Engine

[x] Vendor_SQL

Step 3. <b>Stop/start the J2EE Engine</b>

Step 4. <b>Browse the JNDI Repository</b>; you should see

  - jdbc
    - ORAJ2EDB
    - SAPJ2EDB

Step 5. <b>Do the lookup and get a connection</b> using the new DataSource (in a try/catch block of course)

// Get a connection using a DataSource
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/ORAJ2EDB");
conn = ds.getConnection();
conn.setAutoCommit(false);

[Former Member]

THanks for documenting this. It was very helpful

[guru_subramanianb]

Hi,

This is wht SQL trace does for you.

SQL Trace is a convenient tool for detecting problems when working with the persistent layer. It is an application that provides traces for JDBC methods that access the database or are otherwise important for the database connectivity. You can choose to activate or deactivate it.

I has says about the thread, application, method, and session in which the statement was executed, the execution duration, and so on.

This will give you a fair idea abt ur exception.

Regards,

Guru