I need a j2ee code for getting data from oracle database

[Former Member]

hi,

I need a j2ee and java code for getting data from oracle database in my SAP Developer Network perspective.Need step by step help.. if u send a example code thats fine.. it will be very useful for me..Expecting the reply soon..

Murugan a

[Former Member · Accepted solution]

hi,

I will give u a suggestion.Go to visual admin and create a DSN with these parameters.

Visual admin -> JDBC Connector - > create a driver -> here u add the classes12.zip and the then go to datasource->specify alias name -> specify the driver naem as the driver u have just created.

Driver class the oracledriver class and the url the ip port with sid.

Give the user name and credentials..

In ur code use the DSN alias name to connect.

InitialContext ctx = new InitialContext();

DataSource ds = (Datasource)ctx.lookup("jdbc/<dsn alias name>);

Regards

Bharathwaj

[guru_subramanianb]

Hi Murugan/Vasu,

I see that form ur reply that ur using Studio for ur J2EE project.Webdynpro are available in the same studio itself.Just goto window and then choose perspective and then select webdynpros perspective.In your Webdynpros project right click and goto properties.Then select Java Build path and then select "Add External Jars" and then add your classes12.jar file to it.This is set your classpath and eventually the classnot exception watever you get now will be solved.

Regards,

Guru

[Former Member]

Hi,

my requirement is trying to connect oracle database and getting values from oracle database and displaying in portal (through EP)

i created driver name called - sapdirect and import the jars like pOracle.jar in the driver and then i'll try to create datasource with the following configurations

Name : sapdsn

Aliases : sapalias

Driver name: selected whatever i created before (ex: sapdirect)

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

url : jdbc:sap:oracle://10.145.4.171:1521;RAMS

user name :scott

password :tiger

But the datasource can't create , the follwing error shown,

Please gimme Solution for this.

Even, all the services running oracle m/c.

im wating for solution,

Thanks in advance

Bye for now

Vasu

Error:

-

---

java.rmi.RemoteException: Error occurred while starting application in whole cluster and wait.; nested exception is:

com.sap.engine.services.deploy.exceptions.ServerDeploymentException: Clusterwide execption: server ID 3739150:Exception while preparing start of application sap.com/JDBCConnector_sapdsn.xml.

at com.sap.engine.services.deploy.server.DeployCommunicatorImpl.startApplicationAndWait(DeployCommunicatorImpl.java:679) at com.sap.engine.services.deploy.server.DeployCommunicatorImpl.startApplicationAndWait(DeployCommunicatorImpl.java:661) at com.sap.engine.services.dbpool.deploy.DataSourceManagerImpl.startApplication(DataSourceManagerImpl.java:562) at com.sap.engine.services.dbpool.deploy.DataSourceManagerImpl.deploy(DataSourceManagerImpl.java:261) at com.sap.engine.services.dbpool.deploy.DataSourceManagerImpl.createDataSource(DataSourceManagerImpl.java:317) 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.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:291) 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:37) at com.sap.engine.core.cluster.impl6.session.UnorderedChannel$MessageRunner.run(UnorderedChannel.java:71) 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:94) at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:162)Caused by: com.sap.engine.services.deploy.exceptions.ServerDeploymentException: Clusterwide execption: server ID 3739150:Exception while preparing start of application sap.com/JDBCConnector_sapdsn.xml.

at com.sap.engine.services.deploy.server.application.ParallelAdapter.sleepClientThread(ParallelAdapter.java:232) at com.sap.engine.services.deploy.server.application.ParallelAdapter.runInTheSameThread(ParallelAdapter.java:113) at com.sap.engine.services.deploy.server.application.ParallelAdapter.makeAllPhasesAndWait(ParallelAdapter.java:214) at com.sap.engine.services.deploy.server.DeployServiceImpl.startApplicationAndWait(DeployServiceImpl.java:4601) at com.sap.engine.services.deploy.server.DeployCommunicatorImpl.startApplicationAndWait(DeployCommunicatorImpl.java:677) ... 31 more

[former_member212767]

Hi,

In Visual Admin check that you have Vendor SQL seleceted in the Additional tab for the driver you've created. You can also try setting initial connection to 1 and go to monitoring to see if the driver is working. you should see a green line moving from right to left if it is working.

Regards,

Jari

Message was edited by: Jari Pakarinen

[Former Member]

hi,

Which tool are u using in NDS.

What kind of project is it?

Regards

Bharathwaj

[Former Member]

Bharat,

In the NDS we are working with Enterprise portal(Abstract Portal Component).We are doing some POC's on getting the data from oracle database through Portal.

Murugan a

[Former Member]

Hi,

I am not sure i get u right.. can u explain me in a more detailed manner.

regards

Bharathwaj

[Former Member]

Bharat,

Am using developer studio for developing the component.

From there i've added the classes12.zip file for getting the data from oracle.. Am getting the same exception mentioned below.. Expecting ur reply..

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver Found in negative cache -

---

Loader Info -

---

ClassLoader name: [com.sapportals.portal.prt.util.ApplicationClassLoader@1e0a7b2] Parent loader name: [com.sapportals.portal.prt.util.ApplicationClassLoader@1af5c7b] No references ! Resources: F:\usr\sap\J2E\JC00\j2ee\cluster\server0\apps\sap.com\irj\servlet_jsp\irj\root\WEB-INF\portal\portalapps\dbdata\private\lib\dbdatacore.jar -

---

[Former Member]

hi,

In the webdypro explorer right click on the project .. go to properties..

In that go to java build path - > select libraries -> add external jars and add ur classes12.zip .

Then in the next order and export ..select the classes12.zip u have added and apply the changes

Regards

Bharathwaj

[Former Member]

Bharat,

i've SAP NetWeaver developer studio only.In the developer studio i added the classes12.zip file in the external JAR's,Even the error show as above.We din't have Webdynpro...Expect the reply..

Thanks and regards,

Murugan a

[guru_subramanianb]

Hi Murugan,

Please find the code snippet here with.

//put this code in a method in ur java file

try

{

// Load the jdbc-odbc driver

Class.forName("oracle.jdbc.driver.OracleDriver");

// Open a connection to the odbc data source entered by the user

Connection con =DriverManager.getConnection("jdbc:oracle:thin:@10.201.103.78:1521:HCM" ,"<userid>","<passwd>");

System.out.println("Connection Established --->"+con);

Statement stmt = con.createStatement();

//sample query.Replace this by ur query

ResultSet rst = stmt.executeQuery("select text from category where category_id='Index100'");

while(rst.next())

{

resultString = rst.getString("text");

}

rst.close();

}

catch(Exception e)

{

e.toString();

System.out.println(e.toString());

}

[Former Member]

hi guru,

Im Vasu, Murugan's Freind.

Thx alot for urs imm. reply.

The code is ok, But i want to know how to add oracle thin

driver(i.e., - classes12.zip).Is any tool available to add the driver, give step by step and also i want to know

whats import statement to add the oracle driver.

While im running the code the following error i caught.

Pls rply me with answers step by step.

Its urgent...

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver Found in negative cache -

---

Loader Info -

---

ClassLoader name: [com.sapportals.portal.prt.util.ApplicationClassLoader@1e0a7b2] Parent loader name: [com.sapportals.portal.prt.util.ApplicationClassLoader@1af5c7b] No references ! Resources: F:\usr\sap\J2E\JC00\j2ee\cluster\server0\apps\sap.com\irj\servlet_jsp\irj\root\WEB-INF\portal\portalapps\dbdata\private\lib\dbdatacore.jar -

---

Thanks in advance

Bye for now

Vasu

[Former Member]

Hi Vasu/Murugan,

Refer the following document which describes "Use SAP J2EE Database Pooling Service"

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/webas/stu/use sap j2ee database pooling service

This will help you in adding a new driver, creating a new datasource and accessing the same.

Regards,

Santhosh.C

[Former Member]

Him

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection(

"jdbc:oracle:thin:@<system ip>:<db port no>:SID,

"user name for the db","password");

Statement stmt = conn.createStatement();

rs = stmt.executeQuery("write ur DB query here..in ur case to get data a select query");

Regards

Bharathwaj

[Former Member]

hello

in addition to this you need to import the package that contain OracleDriver class.

regards,

Piyush.