Problems with Oracle JDBC

[Former Member]

Hi we are trying to connect to a Oracle 8.1.7 Database using JDBC via XMII 12.04

We have deployed Oracle JDBC ojdbc14.jar

I have set up this connection

Connector IDBC

ConnectorID

ConnectorType SQL

DatePrefix TO_DATE('

DateSuffix ','MM/DD/YYYY HH24:MI:SS')"

DaysRetention 7

Description BIT_ORA

Enabled T

InitCommand

InternalDateFormat MM/dd/yyyy HH:mm:ss

JDBCDriver oracle.jdbc.driver.OracleDriver

MaxRetryCount 5

Name ConectorBIT

PoolMax 100

PoolSize 1

RetryInterval 60000

ServerPackage com.lighthammer.Illuminator.connectors.IDBC

ServerURL jdbc:oracle:thin:@host.domain.com:1521:DBSID

Timeout 15

UseCount 256

UserName pruebas

ValidationQuery SELECT SYSDATE FROM DUAL

WaitTime 30

When I try to see the status I get this

Server Name ConectorBIT

Connector Type SQL

Status *Running*

No. of Used Connections

No. of Available Connections

Max. No. of Used Connections

Max. Wait Time

As you can see it says running but no connections are available.

When I investigate the xapps-xmii-ear apps logs I get the following warning and infos

info 2008-08-12 16:56:08:590 Created Connection pool [

warning 2008-08-12 16:56:08:590 Unable to fill connection pool for /Applications/XMII

warning 2008-08-12 16:56:07:621 Unable to detemermine maximum number of connections from JDBC driver

So somethings wrong. We cannot make a select to the oracle database.

Please advise.

[Former Member · Accepted solution]

Daniel,

did you realy have the ServerPackage com.lighthammer.Illuminator.connectors.IDBC ?

In MII 12.0.4 the ServerPackage is com.sap.xmii.Illuminator.connectors.IDBC.

Please create a new Data Server and select IDBC. Do not modify the ServerPackage.

Configure the Data Server as you know and test it.

Let me know.

Pedro

[Former Member · Accepted solution]

Hi Pedro,

we are facing a similar problem when trying to connect to a Oracle 8.0.6.2 DB, getting the error "cannot connect to DB".

As a ServerPackage it shows com.sap.xmii.Illuminator.connectors.IDBC. We are using MII 12.0.2 with ojdbc14.jar.

Regards

Michael

[Former Member · Accepted solution]

Hi Michael,

can you provide the error entry from the logs when you activate the DB connection? I have some questions:

+ Is the DB on a different maschine?

+ Exist a Firewall between MII and DB?

+ Are the Ports in the Firewall open?

Regards

Pedro

[Former Member · Accepted solution]

Hi Pedro,

here are the messages:

- Unable to create connection to database for <data server>

- Server IDBC does not exist

- Unable to run the query

- Invalid server IDBC was used

- Unable to get a connection from the pool

- Cannot create a connection to the database

I have installed the data server with oracle.jdbc.driver.OracleDriver and com.sap.xmii.Illuminator.connectors.IDBC.

We also have downloaded the classes12.jar as mentioned above. How can I use it in the data server settings?

Michael

[Former Member · Accepted solution]

I had to delete the ojdbc14.jar and deploy the classes12.jar, have you done this too?

Regards.

[Former Member · Accepted solution]

Michael,

I never had problems with the ojdbc14.jar (DB Version >= 9.x). Problem was always related with:

+ Wrong Connection information

+ Firewall blocked the Port

+ UID / PW information

Try one time more with the driver above. Can you also answer my question from the post before?

Regards

Pedro

[agentry_src · Accepted solution]

Pedro,

I think they are both working with Oracle 8.

[Former Member · Accepted solution]

Hi Michael,

I have google and found that this driver will be work for 8, 9 and 10. The information was not on the oracle homepage.

I will look later also into the oracle homepage.

Cheers

Pedro

[Former Member · Accepted solution]

Hi Pedro,

here are some more information from this afternoon.

+ Is the DB on a different maschine?

Yes, it is situated on a different server.

+ Exist a Firewall between MII and DB?

No, it is in the same network. I was able to connect it via ODBC from my laptop (Linked tables in MS Access), but I still failed in SQL Developer (version 1.2.0) and MII to connect it.

+ Are the Ports in the Firewall open?

Yes.

My colleague connected using PLSQLDeveloper. We compared the connection string, but I stilled failed. Maybe I find some new ideas, changing the connection string or something similar.

Tricky thing...

Michael

[Former Member · Accepted solution]

Hi Michael,

yes it is a tricky thing. Do you have a Oracle Version 9 instance? I think the best is to look if you can

connect with the ojdbc14.jar driver to the Oracle Version 9 DB. If it is working without error, then try

again to your DB 8 instance. When is failing, please provide the full error message that you will see in the

netweaver logs.

On the Oracle site is only the information that ojdbc.jar is compatible with >= 8.1.x. So maybe with

your 8.0.x you will have no success.

I would also suggest you to create a own Message, so other people could help you on this issue.

Regards

Pedro

[Former Member · Accepted solution]

Hi Pedro,

thanks for your comments!

We have only been working with Oracle 10g DBs so far, so I had no chance to connect to an Oracle 9 DB.

I also think it is a good idea to open a message. I will respond after I get any news.

Michael

[Former Member · Accepted solution]

Michael,

try to connect from your MII Server with ojdbc14.jar to any Oracle (Version 9 or 10). Only

to see if its working or not.

Good Luck.

Pedro

[Former Member]

My case was solved with a conbination of things

First the JDBC driver was wrong

Download classes12.jar from this link version 10.2.0.4

[http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html]

Then be carefull when you set up the string because some oracle databases have servicenames instead of SID, and the url was different if you use them instead of the SID

I found out which was our SID and it worked for us.

For services names the url changes to jdbc:oracle:thin@//host:port/servicename

But I coudnt get it working so we finally used the sid.

To know which oracle jdbc you need , depends which JDK your oracle engines uses, mine was using old JDK 1.1 , so I used classes12.jar.

IF you have JDK 1.4 and above continue using your oracle14 driver.

Hope it helps.

Regards