Java iView, Connecting to a database

[Former Member]

Created jdbc datasource called "SAPOracleDS" which has alias called "SAPOracleDSAlias1" to talk to orcle db using visual administrator. Datasource test works fine. After that I Created java iview with following code:

IConnectorService connectorService;

IConnectorGatewayService connectorGatewayService;

IConnection connection;

IBIConnection ibiconn ;

response.write("<h3><font face ='arial' color='green'>");

response.write("<p>This is Application for Oracle DataBase which extracts the table contents and Displays in portal</p><br>");

response.write("</h3></font><br>");

response.write("Testing For Connection<br>");

String SystemAlias = "SAPOracleDSAlias1";

try {

connectorService = (IConnectorService) PortalRuntime.getRuntimeResources().getService(IConnectorService.KEY);

response.write("After Connector Service<br>");

connectorGatewayService = connectorService.getIConnectorGatewayService();

response.write("After connectorGatewayService <br>");

ConnectionProperties connectionProperties = new ConnectionProperties(request.getLocale(),request.getUser());

response.write("After connectorGatewayService <br>");

connection = connectorGatewayService.getConnection(SystemAlias, connectionProperties);

ibiconn = (IBIConnection)connection ;

if (ibiconn == null) {

response.write("Boom - No connection");

} else{

response.write("You got connection Buddy <BR>");

}

IBIRelational rel = ibiconn.getRelational();

String sqlStatement ="SELECT * FROM CUSTOMER" ;

ResultSet res = rel.getResultSet(sqlStatement);

response.write("<table width=100% border = 1> ");

while (res.next()) {

response.write("<tr>");

response.write("<td>"+res.getString("CUSTID") + " </td>");

response.write("<td>"+res.getString("NAME") + " </td>");

response.write("<td>"+res.getString("ADDRESS") + " </td>");

response.write("<td>"+res.getString("CITY") + " </td>");

response.write("<td>"+res.getString("PHONE") + " </td>");

response.write("<td>"+res.getString("CREDITLIMIT") + " </td>");

response.write("</tr>");

}

response.write("</table> ");

ibiconn.close();

} catch (Exception e) {

response.write("CAUGHT ERROR <BR>");

e.printStackTrace();

}

}

it does not work. Does anyone how to use that datasource inside java iview??? Is there anything I need to do for this to work???

[Former Member · Accepted solution]

You need to correct the following lines.

IConnection connection = getJDBCConnection(systemAlias, request);

String sqlStatement ="SELECT * FROM CUSTOMER"

INativeQuery query = connection.newNativeQuery();

Object obj_resul = query.execute(sqlStatement);

java.sql.ResultSet res = (java.sql.ResultSet) obj_resul;

PS: Please reward points if this is helpful. Thank you.

[Former Member · Accepted solution]

Where is getJDBCConnection method defined??

[Former Member · Accepted solution]

I found the getJDBCConnection method from previous posts. Now where should I define the SystemAlias??

[Former Member · Accepted solution]

System Aliases are defined for each system that you create in the System configuration.

[Former Member · Accepted solution]

Go to portal and click on following links.

System Administration->System Configuration->System

Define your JDBC system there and assign the alias.

This is the alias that you will use in the code.

Alias is just a string.

PS: Please reward the points , if this helps. thank you

Message was edited by: Prakash Singh