Monday, June 13
Connecting SAP EP to a SQL database
This post will explain the procedure of connecting SAP EP to a SQL database.
Create a portal project. Create a portal component of type abstractportalComponent in the project. Add few external Jar files in the portal project. These external jar files will be needed while writing some code for the connection. The Jar Files to be added in the project build path are :
j2eeclient/activation.jar
j2eeclient/connector.jar
portalapps/com.sap.portal.ivs.connectorserviceapi.jar
other/genericConnector.jar
j2eeclient/jta.jar
The Code to open a connection is written below
public class JDBC extends AbstractPortalComponent {
public void doContent(
IPortalComponentRequest request,
IPortalComponentResponse response) {
// Open a connection
IConnectorGatewayService cgService =
(IConnectorGatewayService) PortalRuntime
.getRuntimeResources()
.getService(
IConnectorService.KEY);
ConnectionProperties prop =
new ConnectionProperties(request.getLocale(), request.getUser());
IConnection client = null;
try {
client = cgService.getConnection("myDB", prop);
} catch (Exception e) {
response.write(e.toString());
return;
}
try {
// Issue SQL Query statement
INativeQuery query = client.newNativeQuery();
String queryStr =
"SELECT name, address, zip FROM hotel.hotel";
Object result = query.execute(queryStr);
// Iterate returned result
ResultSetMetaData recordMetaData =
((ResultSet) result).getMetaData();
int colNum = recordMetaData.getColumnCount();
//result.beforeFirst();
response.write("<table border=1>");
while (((ResultSet) result).next()) {
response.write("<tr>");
for (int i = 1; i <= colNum; i++) {
response.write("<td>" + ((ResultSet) result).getString(i)+ "</td
}
}
// Close the connection
client.close();
} catch (QueryExecutionException e) {
response.write(e.toString());
} catch (CapabilityNotSupportedException e) {
response.write(e.toString());
} catch (ConnectorException e) {
response.write(e.toString());
} catch (InvalidQueryStringException e) {
response.write(e.toString());
} catch (ResourceException e) {
response.write(e.toString());
} catch (SQLException e) {
response.write(e.toString());
} catch (Exception e) {
response.write(e.toString());
}
}
}
After writing this code, if error occur, right click in the editor and say organize imports. Make sure that you have service reference in the <application-config> tag.
<application-config> <property name="ServicesReference" value="com.sap.portal.ivs.connectorservice"/> </application-config>
Then upload the PAR file.
Thats it. The portal application you created above is ready to be tested. Now by merely changing the query you wrote i nthe above code to fetch data from the hotel table can be changed to fetch data from any other database table as well.
Create a portal project. Create a portal component of type abstractportalComponent in the project. Add few external Jar files in the portal project. These external jar files will be needed while writing some code for the connection. The Jar Files to be added in the project build path are :
j2eeclient/activation.jar
j2eeclient/connector.jar
portalapps/com.sap.portal.ivs.connectorserviceapi.jar
other/genericConnector.jar
j2eeclient/jta.jar
The Code to open a connection is written below
public class JDBC extends AbstractPortalComponent {
public void doContent(
IPortalComponentRequest request,
IPortalComponentResponse response) {
// Open a connection
IConnectorGatewayService cgService =
(IConnectorGatewayService) PortalRuntime
.getRuntimeResources()
.getService(
IConnectorService.KEY);
ConnectionProperties prop =
new ConnectionProperties(request.getLocale(), request.getUser());
IConnection client = null;
try {
client = cgService.getConnection("myDB", prop);
} catch (Exception e) {
response.write(e.toString());
return;
}
try {
// Issue SQL Query statement
INativeQuery query = client.newNativeQuery();
String queryStr =
"SELECT name, address, zip FROM hotel.hotel";
Object result = query.execute(queryStr);
// Iterate returned result
ResultSetMetaData recordMetaData =
((ResultSet) result).getMetaData();
int colNum = recordMetaData.getColumnCount();
//result.beforeFirst();
response.write("<table border=1>");
while (((ResultSet) result).next()) {
response.write("<tr>");
for (int i = 1; i <= colNum; i++) {
response.write("<td>" + ((ResultSet) result).getString(i)+ "</td
}
}
// Close the connection
client.close();
} catch (QueryExecutionException e) {
response.write(e.toString());
} catch (CapabilityNotSupportedException e) {
response.write(e.toString());
} catch (ConnectorException e) {
response.write(e.toString());
} catch (InvalidQueryStringException e) {
response.write(e.toString());
} catch (ResourceException e) {
response.write(e.toString());
} catch (SQLException e) {
response.write(e.toString());
} catch (Exception e) {
response.write(e.toString());
}
}
}
After writing this code, if error occur, right click in the editor and say organize imports. Make sure that you have service reference in the <application-config> tag.
<application-config> <property name="ServicesReference" value="com.sap.portal.ivs.connectorservice"/> </application-config>
Then upload the PAR file.
Thats it. The portal application you created above is ready to be tested. Now by merely changing the query you wrote i nthe above code to fetch data from the hotel table can be changed to fetch data from any other database table as well.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment
You are welcome to express your views here...