Web dynpro and database

[Former Member]

I need to get to the sap db from my web dynpro, and I need to be able to save/retrieve/modify data.

Any idea how to approach it??

I've looked into creating ejbs but have been unsuccessful at finding an ejb document on how to create them and then use them in web dynpro.

If not, what other ways are to go about saving/retrieving data??

Thanks

[Former Member]

Thanks for the quick reply, but I've already looked at plenty of threads on this forum before posting this.

Does anyone have an example of a very simple web dynpro project that is getting its data from an ejb?? I have already check out the car rental tutorial and that gets too confusing because it has too many fiends.

Is there an example where its gettng one field from the database? that would be easier to understand.

[Former Member]

You can use simple JDBC connection (without EJB).

All the connection are maintained in the VA on the server.

[Former Member]

Could you please elaborate on how to go about doing so???

Thanks

[Former Member]

You use simple SQL objects [http://java.sun.com/j2se/1.4.2/docs/api/javax/sql/package-summary.html]

The definitation of the connection itself is done in the Visual Administrator and in the code you connect to the defined connection by his name.

[Former Member]

Hey!

Try this out, it should work.. I would sugest that you use this code snipet on DAO's and put them in a java library project, and make reference to it from the web dynpro project properties so you don't have business logic on your WD Application, although it will also work in the WebDynpro if you copy/paste and customize this code

//
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;


	Connection conn = null;
	try {
		InitialContext dbInitContext = new InitialContext();
		Properties sysProperties = System.getProperties();
		String sysname = sysProperties.getProperty("SAPSYSTEMNAME");
		String dbName = "jdbc/" + "SAP" + sysname + "DB";
		DataSource dataSource = (DataSource) dbInitContext.lookup(dbName);
		conn = dataSource.getConnection();

		Statement stmtQ = conn.createStatement();

		ResultSet rs = stmtQ.executeQuery( "SELECT... ");

		while (rs.next()) {
			//iterate query
		}
	} catch (NamingException e) {
		//handle exception
	} finally {
		try {
			conn.close();
		} catch (SQLException e) {
//handle exception
		}
	}

Cheers,

JV

Edited by: Jesus Valencia on Aug 6, 2009 11:00 AM

[Former Member]

thank you for the code, I got this far before as well. I just stuck afterwards.

Could you point me in the right direction for how to create the DAO? and Java Libraries?

Can't I execute this from the component controller of my WD?

Thanks!!

[Former Member]

Sure you can, you can call this from your component controller, a custom controller, or even a view controller... but neither of this options shuld be considered a "Best Practice" because your business logic wold be "living" inside your WD Application and in the end WD is supposed to know nothing about the model, that why I recomend that.

So you got stuck afterwards in what? Can you specifically tell me what is the problem you have? if you have already connected to the database and executed a query...

Cheers

[Former Member]

Well I have seen the code you posted above on numerious posts, and I've tried to execute it right from my WD component controller (just to see if I can even get data). The result set fetch size is always zero (Does that mean that the query didn't return anything?) I have executed the same code for my sql statement at the database command line and it works, it returns 5 rows. Its a simple "select * from myschema.mytable";

So I fugured I wasn't doing something right since, or maybe I didn't configure stuff properly. I know that its not best practice to get to the database right from WD controller. Thats why I was trying EJBs, but so far I am unsuccess with tha also.

You mentioned using DAO's and Java Library to access the database. How do I do about that?? That would be ideal for me. I am not very familiar with this, and I am not very familiar with WD or NWDS.

Thank you for all your help!

[Former Member]

Hi Sana,

The best way is to use Webdynpro models. If you are on netweaver 7.1 then you will be able to create a EJB model directly. If you are in 7.0 then it will ge good for you to create a Java Bean model.

Java bean model is just a plain java bean with a default constructor, getter/setter and business methods. You can have the business methods to query the database. You can call the methods like modelclass.modelobject.mybusinessmethod(). You can create a jar file with all the beans and save to your desktop. When you create the java bean model you can refer to this jar file. The attributes in the class will be your model nodes and attributes. While deploying pl remember to deploy the jar file as well.

Before all this you need to create/bind JNDI to your database using visual administrator.

Regards

Srini

[Former Member]

I am using 7.0 and I have crated a smiple model (ie: just a few fields with getters/setters and a business method that houses the code above to get data from the database), but when I import this into my WD, the WD doesn't import in the business method. ??

[Former Member]

If you are trying to execute this query *"select * from myschema.mytable"* you should remove the schema, that will work.

like this: "select * from mytable" .

If you like to learn about DAOs here is a link... http://javaboutique.internet.com/tutorials/dao/

A library project allows you to put java classes or jars available on the application server. You can rigth click on the name of you web dynpro project in the webdynpro explorer and in the Web Dynpro Reference there is a tab for Library projects, you write the name of your library project and you can instance the classes in the library.

Hope this helps.

JV

[Former Member]

How do I get the data out of my result set??

I see that it bought back something, but whenever I try this:

rs.getObject(i);

I get a null pointer error.

I also went into the visual administrator to make sure the JNDI was set up , and I do see it there, but its red. Is that alright?

Edited by: Sana Beg on Aug 6, 2009 11:09 PM

[Former Member]

Hey,

Try this:

		while (rs.next()) {

			String x = rs.getString("YOUR_DB_STRING_FIELD");
			long y = rs.getLong("YOUR_DB_LONG_FIELD"));
			boolean z = rs.getBoolean("YOUR_DB_BOOLEAN_FIELD");
			BigDecimal w = rs.getString("YOUR_DB_BIGDECIMAL_FIELD");

		}

Instead of using the name of the field, you could use the index of the field, that should work too.

Cheers

JV

[Former Member]

hi

check this link , connecting to different databases.

Thanks

[Former Member]

Thank you for the quick reply, but I do not need to connect to that database... I needed to connect to the local portal db (max db). Can I connect to that using an RFC??

[Former Member]

hi

you can use adaptibe rfc model to save , retieve data from the SAP DB,

you can integrate the RFC / BAPI and call the RFC/BAPI you can retreieve data from the RFC .

http://help.sap.com/saphelp_nw04/helpdata/en/6a/11f1f29526944e8580c5e59333d96d/frameset.htm

http://wiki.sdn.sap.com/wiki/display/WDJava/FAQ-Models-Adaptive+RFC

check the above links , you even search for adaptive rfc documents in help portal or sdn .

Thanks