Retrieve data from MS SQL Server 2008 database

[Former Member]

I have been developing Webdynpro java applications with ECC system as the backend for couple of years.

Now, I am trying to create a system object (portal system) to connect to an external Microsoft SQL Server (2008) Database.

Portal version is EP 7.0

I am planning on using Webdynpro Java or JSP Dynpage application for the frontend. (Visual Composer is not an option for me)

I have looked around on SDN and I am kind of confused on the right way of doing this.

Some posts talk about simply creating a portal system object using the JDBC template.

Whereas some other posts talk about creating a system from Visual Admin on the server.

There are a lot of posts that talk about Visual Composer or BI based JDBC connection - I am not interested in this because there is no BI system involved in my case.

I have the following step by step document which tells to create a datasource through Visual Admin and also a system alias in the portal :

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/40db1ed9-b7c2-2b10-66b8-84f6954b6...

I am not sure about what is the link between the datasource and the system created on the portal.

What is the best (or) right way to connect to a SQL Server database to retrieve data from there and display it on the portal ?

What configuration is required from the Visual Administrator ? (Drivers, Datasources, etc)

At the end of the day, in the java coding, what is "really" required to access data from the SQL Server Database (system alias or data source) ?

Thank you in advance.

Jaya

[Former Member]

Hi Jaya

The following approach might help you:

1. The document link given by you is the first step to start with. Configure the JDBC connection using Visual Admin and test your configuration in the Visual Admin iteslf or using the URL.

2. Not required to create a system alias in the Portal.

3. However, you can create an alias for the datasource or use an existing an alias in your programing code. (The alias is defined at Services -> JDBC Connector -> DataSources -> <DataSourceName> in the VA.)

4. A sample code to access the data:

try{
         InitialContext initialContext = new InitialContext();
         DataSource dataSource = (DataSource)initialContext.lookup("XYZABC");//XYZABC is the datasource alias name
        con = dataSource.getConnection();
        String selQuery = "SELECT * FROM EMP_TABLE WHERE EMPL_NAME='QWE'";
				
        pstmtSelect = con.prepareStatement(selQuery);
		
        ResultSet rs = pstmtSelect.executeQuery();

         while(rs.next()){

	//Access the data using column names
	if(rs.getString("LAST_NAME") != null && rs.getString("LAST_NAME").equalsIgnoreCase("")==false)
		  //Print or do some action;

     } //End of while

}catch(Exception e){

       e.printStackTrace();

}

The above code can be used in WebDynpro or PAR applications. If it is a simple UI like displaying report, then PAR files are suitable.

Thanks

Deepak