SQL Engine: Native vs Vendor

[Former Member]

Hello Everyone,

I have an external oracle datasource created in Visual Administrator with Native SQL radio button selected. I get an exception when I try to do the following.

InitialContext ctxt = new InitialContext();
DataSource ds = (DataSource) ctxt.lookup("jdbc/ORAWEBDB2");
Connection con = ds.getConnection();

The exception occurs at ds.getConnection(). However, when I change the username/password to the admin's username/password, everything works fine.

Also, when I change the SQL Engine to "Vendor SQL", both the user ids work fine in getting a connection.

There has been discussion about such a problem before too at

Any pointers appreciated.

Thanks,

Kiran

[former_member184385]

Hi Kiran,

another couple of suggestions to try.

1. Use a variation of your "select 1 from dual" program, executed as a standalone java program, to find out, if other developers, beside the lucky one, can access oracle at all. If 1 succeeds, fine, else look, why it doesn't, checking: jdk version and the oracle driver jar.

2. Use another driver lib to find out, if the problem is driver related. Here it goes how:

a) make com.sap.datadirect a driver lib

b) this driver's url and class are

class: com.sap.portals.jdbc.oracle.OracleDriver

dburl: jdbc:sap:oracle://${oracle.host};PortNumber=1521;SID=${oracle.sid};MaxPooledStatements=150

You would ask, why use this driver: Well, this merant driver is as good, if not of higher quality then the original oracle driver, no kidding!

3. Reconsider using native versus vendor sql. Afaik the difference between these two are some goodies like prepared statement caching etc. But, what SAP is doing on the client, Oracle is also doing on the server, so, why bother?

4. A little bit far reaching: Consider using VmWare for your development team. Create one functioning environment and, by using the cloning function, distribute it to all your developers. Also, be able to reset your env. to a functioning snapshot, when things go wild.

Regards

Gregor

[Benny]

Especially as in the stacktrace you can find the message: <i>JDBC driver not supported for ORACLE database.</i>

There clearly must be a difference in the used JDBC drivers. Please remember thatin native mode OpenSQL is used, which needs special drivers, while Vernod mode does not and lets you use any driver you want.

Question is: where did you get the driver for this server?

Regards,

Benny

[Former Member]

Thanks for the reply.

That was our first thought. So, I copied the driver from my co-developer (on whose machine the data source works!) and created a new driver and a data source. This didn't work either!

Spent a lot of time on this already, couldn't figure out the reasons.

FYI:The driver was created from classes12.zip under C:\oracle\ora817\jdbc\lib.

Thanks,

Kiran

[former_member184385]

Kiran,

if you colleague succeeded with her DS setup, why not give her setup on your box a try?

Gregor

[Former Member]

Gregor,

Thanks for the continues support. I did what all I could. I even tried exporting her datasource xml to mine and create a new one, which didn't work. I also tried creating the driver with the classes12.zip that she had (thinking its my classes12 which's wrong), but that too didn't work. The fact is, every developer here has this problem except one.

Any more suggestions?

Thanks,

Kiran

[Former Member]

Hello,

I work with Kiran and may be able to give you some more details about what's happening.

The situation is this:

Two coworkers (call them A & B) are trying to access data on an Oracle database using their local J2EE engine. Both A & B define a Datasource using Visual Administrator. The datasource uses a thin driver and has the "Native SQL" option selected.

A's datasource works fine. B's does not. To eliminate the possibility of B having defined the datasource incorrectly, A exports her datasource. B then deletes his datasource and imports the xml from A's datasource.

The error still happens.

Next we tried to eliminate the code as being the problem. We had both A&B run this snippet. It works for A but not for B.

There HAS to be something different but we have no clue what else to check.

try {
  InitialContext context = new InitialContext();
  Object obj = context.lookup("jdbc/testDS");
  DataSource ds = (DataSource) obj;
  Connection conn = ds.getConnection();
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery("select sysdate from dual");
  while (rs.next()) {
    System.out.println(rs.getDate(1));
  }
} catch (Exception e) {
  e.printStackTrace();
}

[former_member184385]

Kiran,

I assume, you already evaluated the vendor versus native Sql access options and are opting for native, don't you?

If so, please find out, what the sap db pool is doing, which requires dba privileges, using Sql trace and the help of your dba buddy.

You will succeed, I'm sure, and will tell the rest of us, what it was.

Regards

Gregor

[Former Member]

Gregor,

I have a DataSource created with "Vendor SQL" option, in Visual Administrator. I also set the initial connections to 1. My application using this database runs fine. Also, I see that there is one free (green) connection in the Monitoring tab.

I now go to visual administrator and change the SQL Engine option to "Native SQL" and hit the save button. Right after I hit save, I have a exception pop-up window saying, "Error occured .." and the details are as under.

java.rmi.RemoteException: Error occurred while making runtime changes with application sap.com/Oracle Connection; nested exception is: 
	com.sap.engine.services.deploy.exceptions.ServerDeploymentException: Clusterwide exception: server ID 8470450:com.sap.engine.services.dbpool.exceptions.BaseDeploymentException: Cannot start DataSource "ORAWEBDB".

	at com.sap.engine.services.dbpool.deploy.ContainerImpl.startDataSource(ContainerImpl.java:1549)

	at com.sap.engine.services.dbpool.deploy.ContainerImpl.startDataSources(ContainerImpl.java:1825)

	at com.sap.engine.services.dbpool.deploy.ContainerImpl.prepareStart(ContainerImpl.java:489)

	at com.sap.engine.services.deploy.server.application.StartTransaction.prepareCommon(StartTransaction.java:370)

	at com.sap.engine.services.deploy.server.application.StartTransaction.prepare(StartTransaction.java:318)

	at com.sap.engine.services.deploy.server.application.ApplicationTransaction.makeAllPhasesOnOneServer(ApplicationTransaction.java:299)

	at com.sap.engine.services.deploy.server.application.ParallelAdapter.makeAllPhasesImpl(ParallelAdapter.java:301)

	at com.sap.engine.services.deploy.server.application.ParallelAdapter.runInTheSameThread(ParallelAdapter.java:110)

	at com.sap.engine.services.deploy.server.application.ParallelAdapter.makeAllPhasesAndWait(ParallelAdapter.java:212)

	at com.sap.engine.services.deploy.server.application.RuntimeTransaction.finalActions(RuntimeTransaction.java:286)

	at com.sap.engine.services.deploy.server.application.ApplicationTransaction.makeAllPhases(ApplicationTransaction.java:332)

	at com.sap.engine.services.deploy.server.DeployCommunicatorImpl.makeRuntimeChanges(DeployCommunicatorImpl.java:337)

	at com.sap.engine.services.dbpool.deploy.DataSourceManagerImpl.updateDataSourceApplication(DataSourceManagerImpl.java:630)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

	at java.lang.reflect.Method.invoke(Method.java:324)

	at com.sap.pj.jmx.introspect.DefaultMBeanInvoker.invoke(DefaultMBeanInvoker.java:58)

	at com.sap.pj.jmx.mbeaninfo.AdditionalInfoProviderMBean.invoke(AdditionalInfoProviderMBean.java:289)

	at com.sap.pj.jmx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:944)

	at com.sap.pj.jmx.server.interceptor.MBeanServerWrapperInterceptor.invoke(MBeanServerWrapperInterceptor.java:288)

	at com.sap.engine.services.jmx.CompletionInterceptor.invoke(CompletionInterceptor.java:400)

	at com.sap.pj.jmx.server.interceptor.BasicMBeanServerInterceptor.invoke(BasicMBeanServerInterceptor.java:277)

	at com.sap.jmx.provider.ProviderInterceptor.invoke(ProviderInterceptor.java:255)

	at com.sap.engine.services.jmx.RedirectInterceptor.invoke(RedirectInterceptor.java:340)

	at com.sap.pj.jmx.server.interceptor.MBeanServerInterceptorChain.invoke(MBeanServerInterceptorChain.java:330)

	at com.sap.engine.services.jmx.MBeanServerSecurityWrapper.invoke(MBeanServerSecurityWrapper.java:287)

	at com.sap.engine.services.jmx.MBeanServerInvoker.invokeMbs(MBeanServerInvoker.java:157)

	at com.sap.engine.services.jmx.ClusterInterceptor.invokeMbs(ClusterInterceptor.java:220)

	at com.sap.engine.services.jmx.ClusterInterceptor.invoke(ClusterInterceptor.java:803)

	at com.sap.engine.services.jmx.MBeanServerInterceptorInvoker.invokeMbs(MBeanServerInterceptorInvoker.java:102)

	at com.sap.engine.services.jmx.connector.p4.P4ConnectorServerImpl.invokeMbs(P4ConnectorServerImpl.java:61)

	at com.sap.engine.services.jmx.connector.p4.P4ConnectorServerImplp4_Skel.dispatch(P4ConnectorServerImplp4_Skel.java:64)

	at com.sap.engine.services.rmi_p4.DispatchImpl._runInternal(DispatchImpl.java:294)

	at com.sap.engine.services.rmi_p4.DispatchImpl._run(DispatchImpl.java:183)

	at com.sap.engine.services.rmi_p4.server.P4SessionProcessor.request(P4SessionProcessor.java:119)

	at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)

	at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)

	at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

	at java.security.AccessController.doPrivileged(Native Method)

	at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:95)

	at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:159)

Caused by: 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.

	at com.sap.engine.services.dbpool.spi.CPManagedConnectionFactoryImpl.createManagedConnection(CPManagedConnectionFactoryImpl.java:188)

	at com.sap.engine.services.connector.jca.ConnectionHashSet.init(ConnectionHashSet.java:158)

	at com.sap.engine.services.connector.jca.ConnectionHashSet.<init>(ConnectionHashSet.java:127)

	at com.sap.engine.services.connector.jca.AdapterCache.open(AdapterCache.java:207)

	at com.sap.engine.services.connector.ResourceObjectFactory.startConnectionFactory(ResourceObjectFactory.java:290)

	at com.sap.engine.services.dbpool.deploy.ContainerImpl.startDataSource(ContainerImpl.java:1540)

	... 41 more

Caused by: com.sap.sql.log.OpenSQLException: JDBC driver not supported for ORACLE database.

	at com.sap.sql.log.Syslog.createAndLogOpenSQLException(Syslog.java:106)

	at com.sap.sql.jdbc.direct.DirectConnectionFactory.createContextFactory(DirectConnectionFactory.java:485)

	at com.sap.sql.jdbc.direct.DirectConnectionFactory.createDirectPooledConnection(DirectConnectionFactory.java:238)

	at com.sap.sql.connect.OpenSQLConnectionFactory.createDirectPooledConnection(OpenSQLConnectionFactory.java:229)

	at com.sap.engine.services.dbpool.spi.CPManagedConnectionFactoryImpl.createManagedConnection(CPManagedConnectionFactoryImpl.java:177)

	... 46 more

Caused by: com.sap.sql.log.OpenSQLException: JDBC driver not supported for ORACLE database.

	at com.sap.sql.log.Syslog.createAndLogOpenSQLException(Syslog.java:85)

	at com.sap.sql.jdbc.direct.DirectConnectionFactory.createContextFactory(DirectConnectionFactory.java:477)

	... 49 more

There is no java here. Apparently, just changing the option to Native SQL caused an exception as it tried to create an initial connection to it.

I am very surprised as I remember creating datasources with Native SQL option before and they worked fine. This is not just heppening to me but with my collegues too. Also, I have one of them have Native SQL option selected and her app is running fine.

Any clues?

Thanks,

Kiran

[Benny]

Hi Kiran,

the "vendor" option means that you completely go around OpenSQL - conclusion: your problem has to do with it.

As SAP strictly complies to the J2EE rules I guess it will work if you change your code to:

DataSource ds = (DataSource) ctxt.lookup("java:comp/env/jdbc/ORAWEBDB2");

as can be seen from the manuals...

Regards,

Benny

[Former Member]

Benny,

I tried what you said, but now I get an exception at

 ctxt.lookup("java:comp/env/jdbc/ORAWEBDB2");
com.sap.engine.services.jndi.persistent.exceptions.NameNotFoundException: Path to object does not exist at java:comp, the whole lookup name is java:comp/env/jdbc/ORAWEBDB2

As I said in my previous post, I get an exception at ds.getConnection(). I don't have any other option but to use Vendor SQL.

Any help appreciated.

Thanks,

Kiran