How to connect webDynpro With oracle database?

[Former Member]

Hi

Experts

I need to connect webDynpro with Oracle 10g database.

I know basic webDynpro Only.

I have Knowledge of JDBC (But Not With WebDynpro).

I am building Guest House Mgt System for a company.

I read from somewhere that there are many ways to connect to external database

like

1). Directly through JDBC

2). Through Visual Admin.

3). Through EJB.

So where to use what ?

and Please provide step by step answer for connectivity procedure.

Regards

Sunny.

[Former Member · Accepted solution]

Hi Sunny

<a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/wiki?path=/pages/viewpage.action&pageid=47055">see this Wiki Document</a>

Regards

Chaitanya.A

[Former Member]

Hi Sunny.

Hey my name is Chaitanya not Chanakya..anyways no problem..all the best...

Regards

Chaitanya.A

[Former Member]

Oh Sorry Chaitanya. You have written nice code I am Just testing it.

[Former Member]

oh..hope that will solve your problem..if u have any new ideas just post it.so what we can learn new things..

Regards

Chaitanya.A

[Former Member]

Hey Sunny,

you can write the simple JDBC code to connect to oracle dataBase inside your webDynpro application as well.

Basically there are two ways to connect to oracle from your webDynpro application, there are other ways also like JCO and JRA.

1.by writting simple JDBC connection - good for standalone appl.

2.by creating DataSource :-- Good for enterprise Application

Example of 1:---

try {

// Load the JDBC driver

String driverName = "oracle.jdbc.driver.OracleDriver";

Class.forName(driverName);

connection = DriverManager.getConnection(url, userName, password);

Statement stmt =connection.createStatement();

ResultSet resultSet = stmt.executeQuery("select * fromemployee");

if(resultSet.next()) {

msgMgr.reportWarning(resultSet.getString(1));

msgMgr.reportWarning(resultSet.getString(2));

}

} catch(Exception exc) {

exc.printStackTrace();

}

you know the Second One.

regards,

Mithileshwar

[Former Member]

Hi

Sahu

Can you Please Explorer More on that Data Source Way.

Because i am making project for enrterprize only.

Please Explain This Process Step By Step Sir (If Possible).

[Former Member]

Chk thes links also..

Which contains infm abt how to connect oracle thru EJB..

Regards,

GS

Message was edited by:

Sathishkumar GS

[Former Member]

Hi

Satiskumar

I dont know EJB at present.Is this really needed?

Please Explain Me sir.

Cant i get directly get connected to oracle?

Thanking You

Sunny Shah.

[Former Member]

HI Shah..

You can also connect oracle database thru EJB. If u don have an idea abt EJB, then u don need to worry abt it. You can do it by directly connecting thru JDBC.

[Former Member]

Hi Sir

Thanks for your all above information.

Sir can you provide me a simple step by step procedure that how to write code to connect webDynpro through oracle in SAP Netweaver.

[Former Member]

Hi try this in doinit()

Connection con = null;

Statement stmt = null;

DataSource ds=null;

ResultSet rs=null;

try

{

InitialContext x = new InitialContext();

ds = (DataSource)x.lookup("jdbc/datadb");

con = ds.getConnection();

stmt = con.createStatement();

rs = stmt.executeQuery("as per your requirement");

}

catch(Exception e)

{

wdComponentAPI.getMessageManager().reportException...

}

[Former Member]

Hi Sir.

For this i need visula admin 's data source.

Is it necessary.

Cant I use JDBC connection in component 's wdDoInit?

What is diff between this two?

[Former Member]

Hi Shah..

For this u don need Visual adm..

You can use JDBC conn in component's doinit() also..

Only diff is ..

The doInit() in Component Controller will be called whenever a component comes into use.

The doInit() in a View Controller will be called whenever a view comes into use.

regards,

Sathish.

[Former Member]

Hi

Sathishkumar

Thank you very much for your help.

InitialContext x = new InitialContext();
ds = (DataSource)x.lookup("jdbc/datadb");

Can you please explain me this two statements and what it will do.

And Mr.Chanakya thank you for your help. I am just reading your WIKI.

Thanking you

Sunny.

[Former Member]

Sunny. You've gotten a lot of responses here so I don't want to clutter things up even more. However, we HAVE been connecting to Oracle from Web Dynpro without using EJB or weblogic servers or anything complicated like that.

It's pretty simple and some of the people have given you some ideas already. If you have more questions please post them and I'll be glad to help.

As for your last question about the two lines that Sathishkumar posted. Those two lines basically will get you a handle on a datasource that was defined in Visual Administrator.

Let me know if you still need some help.

David.

[Former Member]

Thanks Sir

For your help I am Trying it.

If it will not respond then I will Let you know.

You know on SDN i started beliving in Quote "When You want to achive something Whole Universe conspires you to achive it"

From Alchemist by Paulo Coleho.

Thanks For it Sir.

[Former Member]

Hey Sunny,

please don't call sir to any body.

[Former Member]

Ok

[Former Member]

Sunny,

Award the point to sutable answer and close the thread,if you are done with this thread.

regards,

Mithileshwar

[Former Member]

Hi Sahu

I am right now just trying this after some time i will close this question.If I found Any problem I will post that problem.

Regrads

Sunny.

[Former Member]

hi Sahu

when i run your code then i get exceptions

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

at java.net.URLClassLoader$1.run(URLClassLoader.java:200)

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

at java.net.URLClassLoader.findClass(URLClassLoader.java:188)

at java.lang.ClassLoader.loadClass(ClassLoader.java:306)

at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)

at java.lang.ClassLoader.loadClass(ClassLoader.java:251)

at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:164)

at test1.main(test1.java:24)

[Former Member]

chk it

[Former Member]

Hey Sunny,

you need to put classes12.jar file in your class Path.

regards,

Mithileshwar

[Former Member]

Hi

Experts

I Just write below code and i also add classes12.jar file into properties of project by right clicking on project>properties>Java Build Path--> External Jar File

I add classes12.zip

and then

i make two views in first i add below code and bind table with context 's value node nodeEmp.

code is

Connection conn=null;
	try
	{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
	conn =DriverManager.getConnection ("jdbc:oracle:thin:@Server 's IP:1521:adaniinframs","scott", "tiger");
	Statement stmt=conn.createStatement(); 
	ResultSet rs=stmt.executeQuery("select ename,job from emp"); // select statment

	while(rs.next())
	{
	IPrivateDatabaseConnectView.INodeEmpElement a = wdContext.createNodeEmpElement();
	a.setEmpName(rs.getString(1));
	a.setEmpJob(rs.getString(2));
	wdContext.nodeNodeEmp().addElement(a);
	}
	}
	catch(Exception ex)
	{ 
	System.out.println("Error");
	}

	finally
	{
	 try
	{
	conn.close(); // closing the connection
	}
	catch(Exception e)
	{  
	System.out.println("Error"); 
	}
	}   

Above code is not giving any output and also not giving error.

Please Provide me Solution.

Regards

Sunny.

[Former Member]

Hi! Sunny,

try to debug your application by printing some hardcoded line in your application,it's a kind of System.out.println().But to do this firstly create an object of Message Manager like:--

MessageManager msgMgr = (MessageManager)wdThis.wdGetAPI().getComponent().getMessageManager();

and then :--

msgMgr.reportWarning("After line 1");

and try to find are you getting any exception..

Thanks,

Mithileshwar

[Former Member]

Ok Just Checking.

[Former Member]

Hi Sahu.

Error Is getting because of below line.

<b>Class.forName("oracle.jdbc.driver.OracleDriver");</b>

Regards.

Sunny.

[Former Member]

Sunny,

this means your application is not able to register the Driver with driverManager, i think the problem is with classpath.Run this application in simple Java application and try to find out the problem.

one more thing have you included class12.jar in your deployable .ear file?

you need to include class12.jar file at two places in your appl.

regards,

Mithileshwar

[Former Member]

Hi Sahu.

I had already included it into build path of project and i had already add classes12.jar file there by clicking on external jar file button.

Is this ok?

and where else i need to add and how?

Will you please provide step by step solution.

Thanking You

Sunny.

[Former Member]

Hi! Sunny,

you need to include classes12.jar in you application lib folder as well.

1.go to Navigator View

2. expand your application and import the jar file in lib folder.

regards,

Mithileshwar

[Former Member]

Hi Sahu.

First Thank you very much for your support.

and

Now it is going till second line.

Giving error in this line.

<b>conn =DriverManager.getConnection ("jdbc:oracle:thin:@Server IP:1521:adaniinframs","scott", "tiger"); </b>

Regards

Sunny.

[Former Member]

Hi! Sunny,

try to print the error, it's very difficult to say what is the error,just by looking the

line.Catch the exception and print the stack trace.

regards,

Mithilehwar

[Former Member]

Sunny. I don't want to further confuse things but I do want to mention that what you're trying here may not be the way to go if you're using DCs in the NWDI.

Are you using DCs or is this just a local project in Studio?

David.

[Former Member]

Hi.

Sahu and David.

David I am not working with DC I am developing WebDynpro Project.

Sahu Will you please tell me that how to display error Now my code is

Connection conn=null;
		try
		{
		wdComponentAPI.getMessageManager().reportException("after 0",false);
		Class.forName("oracle.jdbc.driver.OracleDriver");
		wdComponentAPI.getMessageManager().reportException("after 1st",false);
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		wdComponentAPI.getMessageManager().reportException("after 2nd",false);
		conn =DriverManager.getConnection ("jdbc:oracle:thin:@132.132.48.4:1521:adaniinframs","scott", "tiger");
		wdComponentAPI.getMessageManager().reportException("after 3",false);
		Statement stmt=conn.createStatement(); 
		wdComponentAPI.getMessageManager().reportException("after 4",false);
		ResultSet rs=stmt.executeQuery("select ename from emp where empno=7369"); // select statment
		wdComponentAPI.getMessageManager().reportException("after 5",false);
		while(rs.next())
		{
		wdComponentAPI.getMessageManager().reportException("after 6",false);
		IPrivateDatabaseConnectView.INodeEmpElement a = wdContext.createNodeEmpElement();
		a.setEmpName(rs.getString(1));
		a.setEmpJob(rs.getString(2));
		wdContext.nodeNodeEmp().addElement(a);
		}
		}
		catch(Exception ex)
		{ 
                ex.printStackTrace();
		System.out.println("Error");
		}
 
		finally
		{
		 try
		{
		conn.close(); // closing the connection
		}
		catch(Exception e)
		{  
		System.out.println("Error"); 
		}
		}   

Error Is at

<b>DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());</b>

line.

[Former Member]

Hi.

Experts

Problem get solved by code

                Class.forName("oracle.jdbc.driver.OracleDriver");
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		conn =DriverManager.getConnection ("jdbc:oracle:thin:@IP:PortNo:SID",UNAME, PASSWD);
		Statement stmt=conn.createStatement(); 

		ResultSet rs=stmt.executeQuery("select ename from emp where job='CLERK'"); // select statment

		while(rs.next())
		{
			wdComponentAPI.getMessageManager().reportException(rs.getString("ename"),false);
		}
		}
		catch(Exception ex)
		{ 
		wdComponentAPI.getMessageManager().reportException("Trapped",false);
		wdComponentAPI.getMessageManager().reportException(ex.toString(),false);
		}

		finally

		{

		 try
		{
		conn.close(); // closing the connection
		}
		catch(Exception e)
		{  
               	wdComponentAPI.getMessageManager().reportException("Error In connection Release",false);
		}
		} 

and any one who get problem can directly communicate with me on sunnyshah.mca at gmail dot com

I get very good help from chitanya 's <a href="https://www.sdn.sap.com/irj/sdn/wiki?path=/pages/viewpage.action&pageid=47055">wiki</a> and all other experts.

Thankx for that.

Regards

Sunny.

[Former Member]

Hi..

Go thru this..

http://e-docs.bea.com/wls/docs81/oracle/API_joci.html

Regards,

GS

[Former Member]

Hello Sir

Thankx for you quick reply but

What is this weblogic server?

Please kindly give me brief about it.

Thinking You

Sunny Shah.

[Former Member]

Hi Shah..

It is a type of JDBC oracle driver. In tht link you can get infm tht how to connect with oracle database. Step by step process is given..

Regards,

GS