Project and Portfolio Management Practitioners Forum
cancel

Standard tags for PPM-JDBC connection

SOLVED
Go to solution
Highlighted
Shravan Kathuri
Honored Contributor.

Standard tags for PPM-JDBC connection

Hi,
I have custom JSP page which will connect to database using JDBC and get the data.
Is there any standard PPM tags available for
getting Database connection instead of giving jdbc_url and other required information?

Thanks
Shravan kumar.K
10 REPLIES
Sascha Mohr
Acclaimed Contributor.

Re: Standard tags for PPM-JDBC connection

Hello Shravan,
we use custom JSPs for reports. I'm not a Java programmer, but they include these lines:
<%@ page import="com.kintana.core.logging.*" %>
<%@ page import="com.kintana.core.db.DBSession" %>

and later:



and this:
// get DB connection from pageContext (set by rpt:getJDBCConnection)
DBSession dbsession = (DBSession) pageContext.getAttribute("RPT_DATA_SRC" , 2);
Connection conn = dbsession.getConnection();

and finally:
conn.close();

Maybe this will help you.
Regards
Sascha
Shravan Kathuri
Honored Contributor.

Re: Standard tags for PPM-JDBC connection

Hi Sascha,

Yes , you are right. I have also tried to use from reports JSP's. But it is asking about REPORT_ID. I need to see again by modifying code to check whether it is working or not. Meanwhile, is there nay other alternative for getting JDBC connections through tags?

Thanks
Shravan kumar.K
Sascha Mohr
Acclaimed Contributor.

Re: Standard tags for PPM-JDBC connection

Sorry, I don't know another way. Maybe there is some docu on developing jsr168 compliant portlets for PPM that would explain on how to use PPM's database connections.
Cenk Oguz
Respected Contributor.
Solution

Re: Standard tags for PPM-JDBC connection

Hi,

In java you usually use DataSource objects to get hold of a database connection. The datasource is already configured on Jboss, and in PPM8 at least is called "java:/ItgDS". I have a snippet bellow which worked out in a portlet, but should work in a JSP scriptlet as well.


/*String dbResult = null;
final String JNDI_NAME = "java:/ItgDS";

Connection result = null;
try {
Context initialContext = new InitialContext();
if ( initialContext == null){
throw new NamingException("JNDI problem. Cannot get InitialContext.");
}
DataSource ItgDataSource = (DataSource)initialContext.lookup(JNDI_NAME);
if (ItgDataSource != null) {
result = ItgDataSource.getConnection();
}
else {
throw new SQLException("Failed to lookup datasource.");
}

Connection con = ItgDataSource.getConnection();
PreparedStatement prepStatement = con.prepareStatement("SELECT last_name FROM KNTA_USERS WHERE user_id = 32728");

ResultSet rSet = prepStatement.executeQuery();

rSet.next();
dbResult = rSet.getString(1);

rSet.close();
prepStatement.close();
con.close();

} catch (Exception e) {
throw new IOException(e.getMessage());
}
Shravan Kathuri
Honored Contributor.

Re: Standard tags for PPM-JDBC connection

Hi Cenk Oguz,

I have included the code in my java file and when i ran this file it is giving error

"Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial".

We are using PPM 7.1 SP5.

I don't know where we can specify this parameter or class?

Can you please help me If you have any information on this?

Thanks
Shravan kumar.K

Cenk Oguz
Respected Contributor.

Re: Standard tags for PPM-JDBC connection

How are you executing this file? In a jsp in ppm? Or as a regular java application? Looks like the datasource is not found, implying you are not executing ppm application server.
Shravan Kathuri
Honored Contributor.

Re: Standard tags for PPM-JDBC connection

Hi Cenk Oguz,

I was just put this code in java file and placed this java file in the crt folder and tried to execute and got this error.

If we can put this same code in JSP, will it work?

And i have seen my datasource name in "itg-ds.xml". the datasource name is "ItgDS".
Am i referring the correct file? If so how can i give the JNDI_NAME?

Please help me..

Thanks
Shravan kumar.K
Cenk Oguz
Respected Contributor.

Re: Standard tags for PPM-JDBC connection

The JNDI name will get resolved once you run in the context of the PPM application server, yes ItgDS is right.

Just executing the java code file as a regular application will not do it. Needs to execute in the application server context, ie through the web interface in a JSP file. Besides from the code snippet you also need to import the necessary classes through jsp directive, like:
<%@ page import='java.util.*' %>
<%@ page import='java.io.*' %>

However, as you are working with jsp:s why not just use standard JSTL sql taglibrary, that way you only work with tags and no java coding. Think there are plenty of examples of how to use those. Check http://java.sun.com/products/jsp/jstl/1.1/docs/tlddocs/index.html

But if you are only working with report jsps, you can just use the ppm report taglibrary, like as sasha suggested.
Shravan Kathuri
Honored Contributor.

Re: Standard tags for PPM-JDBC connection

Hi Hi Cenk Oguz,

I have included the code in JSP file and tried to open the JSP page,but it has given the below error message

Wrong credentials passed to getConnection!; - nested throwable: (org.jboss.resource.JBossResourceException: Wrong credentials passed to getConnection!).

Is there anything like we should pass user credentials in our Custom JSP?

Is there any possibility to specify without the credentials explicitly.?

Thanks
Shravan Kumar.K
Cenk Oguz
Respected Contributor.

Re: Standard tags for PPM-JDBC connection

Can't really say. You shouldn't need to state credentials when using a Datasource. I don't when using PPM8.

Although I remember seeing this briefly in 7.1 before moving no to 8.

BR,
Cenk