Java iView, Connecting to a database.

[daniel_davinci]

Hi All,

I am working on developing some java to to retrive data from an MSSQL database. I am having a little difficulty and would appreaciate <b><u>any</u></b> assistance. I imported the additional lib's and corrected my appinfo.xml. I am getting the error: “java.lang.UnsupportedOperationException: IQuery is not supported”

I am only getting this error at runtime…

My code reads as follows:

import com.sapportals.portal.prt.component.AbstractPortalComponent;

import com.sapportals.portal.prt.component.IPortalComponentRequest;

import com.sapportals.portal.prt.component.IPortalComponentResponse;

import com.sap.security.api.IPrincipal;

import java.util.LinkedList;

import java.util.List;

import java.util.Set;

import com.sapportals.connector.execution.structures.IRecordMetaData;

import com.sapportals.portal.ivs.cg.IConnectorService;

import com.sapportals.portal.ivs.cg.IConnectorGatewayService;

import com.sapportals.portal.ivs.cg.ConnectionProperties;

import com.sapportals.connector.connection.IConnection;

import com.sapportals.connector.execution.structures.IRecordSet;

import com.sapportals.portal.prt.component.IPortalComponentInitContext;

import com.sapportals.portal.prt.runtime.PortalRuntime;

import com.sapportals.connector.metadata.objects.IObjectsMetaData;

import com.sapportals.connector.metadata.objects.IBusinessObjectGroup;

public class ConnectionTest extends AbstractPortalComponent

{

protected void doContent(IPortalComponentRequest request, IPortalComponentResponse response){

try{

response.write("test <br>");

IConnection connection = getConnectorServiceConnection(request);

response.write(connection.toString());

// Get the Business objects meta data

IObjectsMetaData objectsMetaData = connection.getObjectsMetaData();

response.write("Have objectMetaData " + objectsMetaData.toString() + "<br>");

// Get the root object

IBusinessObjectGroup issues = objectsMetaData.retrieveRootBusinessObjectGroup();

response.write("Have issues " + issues.toString() + "<br>");

// Iterate level elements if wanted

Set schemas = issues.retrieveNextLevelElements();

response.write("Have schemas " + schemas.toString() + "<br>");

// prepare a query on the connection

com.sapportals.connector.execution.objects.IQuery query = connection.newQuery();

List categoryAttributes = new LinkedList();

response.write("Have categoryAttributes " + categoryAttributes.toString() + "<br>");

// set query conditions assignto

categoryAttributes.add("dbo.IssuesTab.assignto");

query.setSelectedAttributes("dbo.IssuesTab", categoryAttributes);

response.write("Have categoryAttributes " + categoryAttributes.toString() + "<br>");

boolean success = query.execute();

response.write("Have query " + query.toString() + "<br>");

if (!success) {

connection.close();

}

IRecordSet singleResultSet = query.retrieveRecordSet();

response.write(singleResultSet.toString());

IRecordMetaData resultSetMetaData = singleResultSet.retrieveMetaData();

// write data

int columnCount = 0;

while (singleResultSet.next())

{

response.write("test");

for (int index=1;index<=columnCount;index++)

{

response.write("" + singleResultSet.getString(index) + "");

}

response.write("");

}

connection.close();

} catch (Exception e) {

UnsupportedOperationException uoe = (UnsupportedOperationException)e;

response.write("Exception:" + uoe.getLocalizedMessage());

}

}

// get connection method

private IConnection getConnectorServiceConnection(IPortalComponentRequest request)

throws Exception{

String SystemAlias = "JDBC_System";

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

IConnectorGatewayService gateway = (IConnectorGatewayService)connectorService;

ConnectionProperties connProps = new ConnectionProperties();

connProps.setLocale(request.getLocale());

IPrincipal principal = (IPrincipal)request.getUser();

connProps.setUser(principal);

IConnection connection = gateway.getConnection(SystemAlias, connProps);

return connection;

}

public void init(IPortalComponentInitContext arg0) {

}

}

This results in:

test

com.sapportals.jdbcconnector.connection.CCIConnection@16218f3Have objectMetaData com.sapportals.jdbcconnector.metadata.objects.ObjectsMetaData@142edd6

Have issues com.sapportals.jdbcconnector.metadata.objects.CatalogObject@10c8ca2

Have schemas [com.sapportals.jdbcconnector.metadata.objects.SchemaObject@e5127b]

Exception:IQuery is not supported

I am sure that the connector is sucessful due to the results that I get from my debug statements.

Anyone had this problem before or see a problem with the code?

All suggestions welcome.

Regards

Daniel

[Former Member]

Hi Daniel,

I am having the same problem like you have "IQuery is not supported", how did you resolve the problem, please explain.

Many Thanks

Vijay

[Former Member]

i am unable to locate the jar containing IConnection class.

can you let me know the same.

[Former Member]

Hi divakar,

The jar file for IConnection class is GenericConnector.jar

It can be found in <Drive>:\Program Files\SAP\JDT\eclipse\plugins\com.sap.tc.ap\comp\SAP_JTECHS\DCs\sap.com\tc\conn\connectorframework\_comp\gen\default\public\default\lib\java folder.

I advice you to have jar/Class locator. That will make your life easier.

Regards,

Sai Krishna.

[Former Member]

<?xml version="1.0" encoding="utf-8"?>

<application>

<application-config>

<property name="ServicesReference" value="com.sap.portal.htmlb, com.sap.portal.ivs.iviewservice, com.sap.portal.ivs.connectorservice, com.sap.portal.runtime.system.inqmy, com.sap.portal.pcd.glservice, com.sap.portal.ivs.systemlandscapeservice,com.sap.portal.pcmbuilderservice, usermanagement, com.sap.portal.ivs.logger, com.sap.portal.usermapping , landscape, jcoclient"/>

<property name="releasable" value="true"/>

</application-config>

<components>

<component name="empSearch">

<component-config>

<property name="ClassName" value="com.saped.prototype.search.empSearch"/>

<property name="SecurityZone" value="com.saped.prototype.search/high_safety"/>

</component-config>

<component-profile>

<property name="System" value="SAP_ED"/>

<property name="tagLib" value="/SERVICE/htmlb/taglib/htmlb.tld"/>

</component-profile>

</component>

</components>

<services/>

</application>

Above view the <application-config> tags where we have defined the services. Please note the example I will be showing uses the Database with an Alias, just ask ur system admin to mount the database for u as using an Alias is mich more simpler.

private IConnection getJDBCConnection(

String systemAlias,

IPortalComponentRequest request)

throws ConnectorException, Exception {

try {

IConnectorGatewayService cgService =

(IConnectorGatewayService) PortalRuntime

.getRuntimeResources()

.getService(

IConnectorService.KEY);

if (cgService == null) {

throw new ConnectorException("Error in get Connector Gateway Service");

}

// Use the Connector Gateway to get connection to JDBC DB

ConnectionProperties cp =

new ConnectionProperties(

request.getLocale(),

request.getUser());

return cgService.getConnection(systemAlias, cp);

} catch (NoLogonDataAvailableException e) {

throw new ConnectorException(

"*** No Logon Data supplied: "

+ e.getNestedException().getLocalizedMessage());

} catch (ConnectorException e) {

throw new ConnectorException(

"*** Connector Exception: "

+ e.getLinkedException().getLocalizedMessage());

}

}

This will establish the connection, and this code works very well, so try it out and if u have any difficulties let me know.

private TableViewModel getQueryResults(String systemAlias)

throws ConnectorException, Exception {

//TODO Enter name of Stored procedure

//String PROCNAME = "CustOrdersDetail";

IPortalComponentRequest request =

(IPortalComponentRequest) this.getRequest();

IPortalComponentResponse response =

(IPortalComponentResponse) this.getResponse();

IPortalComponentProfile prof =

request.getComponentContext().getProfile();

TableViewModel tvm = new DefaultTableViewModel();

// initialize the physical connection

IConnection con = getJDBCConnection(systemAlias, request);

IRecordSet rs = null;

try {

INativeQuery query = con.newNativeQuery();

//rs= st.executeQuery(" SELECT EmployeeMast.ino, EmployeeMast.fname, EmployeeMast.lname, EmpTechQualMast.cid,

EmpTechQualMast.certified, EmpTechQualMast.experiencce, ComponentMast.cmajordesc, ComponentMast.cminordesc FROM ComponentMast INNER JOIN (

EmployeeMast INNER JOIN EmpTechQualMast ON EmployeeMast.ino = EmpTechQualMast.ino) ON ComponentMast.cid = EmpTechQualMast.cid WHERE (((

EmpTechQualMast.cid)="coid") AND ((EmpTechQualMast.certified)='"x.getCert()"') AND ((EmpTechQualMast.experiencce)>="qexperience"))");

String MySqlString = null;

//if (SEARCH_VAL==1)

//{

MySqlString =

" SELECT EmployeeMast.ino, EmployeeMast.fname, EmployeeMast.lname, EmpTechQualMast.cid,

EmpTechQualMast.certified, EmpTechQualMast.experiencce, ComponentMast.cmajordesc, ComponentMast.cminordesc FROM ComponentMast INNER JOIN (

EmployeeMast INNER JOIN EmpTechQualMast ON EmployeeMast.ino = EmpTechQualMast.ino) ON ComponentMast.cid = EmpTechQualMast.cid WHERE (((

EmpTechQualMast.cid)="

+ queryResults.getSkillset()

+ ") AND ((EmpTechQualMast.certified)='"

+ queryResults.getCertified()

+ "') AND ((EmpTechQualMast.experiencce)>="

+ queryResults.getExperience()

+ "))";

//selse{

//String MySqlString = " SELECT * form EmployeeMast";

//}

String MySqlComponents = "select * from ComponentMast";

//String MySqlString = "SELECT * FROM EmployeeMast";

Object obj = query.execute(MySqlString);

Object obj2 = query.execute(MySqlComponents);

java.sql.ResultSet resultset = (java.sql.ResultSet) obj;

java.sql.ResultSet resultset2 = (java.sql.ResultSet) obj2;

//TODO retrieve the output recordset

//IRecordSet result = (IRecordSet) output.get(PROCNAME+".@RESULT");

tvm = RecordSetToTVM(resultset);

compid = new DefaultListModel();

compid.setSingleSelection(true);

while (resultset2.next()) {

//compids.add(resultset2.getString(1));

String x = resultset2.getString(1);

String y = resultset2.getString(2);

compid.addItem(x, y);

//compvalues.add(resultset2.getString(2));

compid.setSelection(x);

}

} catch (ResourceException e) {

printException(e);

} catch (Exception e) {

printException(e);

} finally {

try {

con.close();

} catch (ResourceException e1) {

printException(e1);

}

}

return tvm;

}

Here u go Daniel, hope it solves ur problem, I had the same problem as ur and it took me some time to figure this out. Hope it helps, so rate my answers thanks.

[daniel_davinci]

Thanks very much for that Emmanuel.

Got it working wonderfully now.

Cheers

Daniel