The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Database connection information for Custom JSP page.

SOLVED
Go to solution
Highlighted
Shravan Kathuri
Senior Member

Database connection information for Custom JSP page.

Hi,

I have written more than 20 Custom JSP pages in PPM.But When i used to connect to database through JDBC, i used to hardcode the database connection credentials in every JSP page. Can anyone tell me how to get the datasource information related to ITG database and how to use it every custom JSP page to avoid hardcoding database credentials in every JSP page?

Thanks
Shravan Kumar.K
10 REPLIES
Jason Nichols K
Honored Contributor
Solution

Re: Database connection information for Custom JSP page.

Shravan,

You can now use the tag libraries that come with PPM (I think they started with 7.x). To inlcude the proper tag library, put this line with any other <%@ page import... lines:

<%@ taglib uri="/Kintanareporttld" prefix="rpt" %>


These next two lines will make the connection to the database and also load any parameters in the EL style variables:





You will need to continue using the rpt tags for your database connection, but this is much better and portable for writing JSP pages within PPM. I used other OOTB reports to learn what other tags are available.
Shravan Kathuri
Senior Member

Re: Database connection information for Custom JSP page.

Hi Jason Nichols (KY),

Thanks for your quick response.I tried to use these tags, but it is giving error like "REPORT_ID" is missing. Do you have any sample JSP page which uses these tags to connect to ppm database.

Thanks
Shravan

Re: Database connection information for Custom JSP page.

Hi Shravan,

I used the follwoing code:

// get server.conf parameter
String jdbcUrl = null;
String dbUsername = null;
String dbPassword = null;
Properties serverConf = new Properties();
try {
serverConf.load(new FileInputStream("server.conf"));
} catch (IOException e) {
e.printStackTrace();
if (debug) { %> ERROR: File 'server.conf' does not exist
<% }
initFailed = true;
}
if (!initFailed) {
jdbcUrl = serverConf.getProperty("com.kintana.core.server.JDBC_URL");
if (debug) { %> JDBC Url: <%=jdbcUrl%>
<% }

dbUsername = serverConf.getProperty("com.kintana.core.server.DB_USERNAME");
if (debug) { %> DB username: <%=dbUsername%>
<% }

dbPassword = serverConf.getProperty("com.kintana.core.server.DB_PASSWORD");
}


// decrypt password
try {
PasswordManager.setPrivateKey(new ElGamalPrivateKey("security/private_key.txt"));
} catch (IOException e) {
e.printStackTrace();
if (debug) { %> ERROR: File 'security/private_key.txt' does not exist
<% }
initFailed = true;
}
try {
PasswordManager.setPublicKey(new ElGamalPublicKey("security/public_key.txt"));
} catch (IOException e) {
e.printStackTrace();
if (debug) { %> ERROR: File 'security/public_key.txt' does not exist
<% }
initFailed = true;
}
String passwordDelimiter = "#!#";
if (!initFailed) {
if (dbPassword.startsWith(passwordDelimiter)) dbPassword = dbPassword.substring(passwordDelimiter.length());
if (dbPassword.endsWith(passwordDelimiter)) dbPassword = dbPassword.substring(0, dbPassword.length() - passwordDelimiter.length());
if (PasswordManager.isElGamalEncrypted(dbPassword))
try {
dbPassword = PasswordManager.decrypt(dbPassword);
} catch (RuntimeException e) {
e.printStackTrace();
if (debug) { %> ERROR: Could not decrypt DB password
<% }
initFailed = true;
}
}

//
// connect to PPM DB
//
Connection connection = null;
try {
// Load the JDBC driver
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
// Create a connection to the database
connection = DriverManager.getConnection(jdbcUrl, dbUsername, dbPassword);
} catch (ClassNotFoundException e) {
e.printStackTrace();
if (debug) { %> ERROR: Could not find class file for database driver
<% }
initFailed = true;
} catch (SQLException e) {
e.printStackTrace();
if (debug) { %> ERROR: Could not connect to database
<% }
initFailed = true;
}


Regards,
Michael
Shravan Kathuri
Senior Member

Re: Database connection information for Custom JSP page.

Hi Michael,

Thanks for your help. Do we need to include any of the library files inorder to get the details from server.conf file?


Thanks
Shravan Kumar.K

Re: Database connection information for Custom JSP page.

Hi Shravan,

Sorry. Missing includes:

<%@ page import="java.util.Properties" %>
<%@ page import="java.io.FileInputStream" %>
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="com.kintana.core.util.PasswordManager" %>
<%@ page import="com.kintana.sc.security.ElGamalPrivateKey" %>
<%@ page import="com.kintana.sc.security.ElGamalPublicKey" %>
<%@ page import="sun.misc.BASE64Decoder" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.CallableStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="com.kintana.core.web.util.WebUtils" %>

Regards Michael
Shravan Kathuri
Senior Member

Re: Database connection information for Custom JSP page.

Hi Michael,

It is working fine. Thanks for your quick help.Thanks Jason for your help also.

Thanks
Shravan Kumar.K
Shravan Kathuri
Senior Member

Re: Database connection information for Custom JSP page.

Hi Michael,

One more doubt..
I want to set the url,username and password in one jsp and want to get these data into another JSP's to to avoid repetetion of code

How can i use these url,username and passowrd jsp variables in another JSP?

Thanks
Shravan Kumar.K

Re: Database connection information for Custom JSP page.

Hi Shravan,

I'm not a JSP guru
but perhaps you can try to include another jsp with this command:

<%@ include file="OracleStuff.jsp" %>

Regards
Michael
Sascha Mohr
Esteemed Contributor

Re: Database connection information for Custom JSP page.

Hi,
I have been doing it the way Jason described. We usually include these:
<%@ page language="java" contentType="application/pdf; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.io.FileInputStream" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.HashMap" %>
<%@ page import="com.kintana.core.logging.*" %>
<%@ page import="com.kintana.core.db.DBSession" %>
<%@ page import="java.io.FileOutputStream" %>
<%@ taglib uri="/Kintanareporttld" prefix="rpt" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

and then access the jdbc connection:



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

and in the end:
conn.close();


If you need, I can send you a stripped example file. I also have a document from Mercury called "Creating Custom Reports", but it is for release 5.5. I'm sure if it is still useful.
Chuck Crowell
Occasional Advisor

Re: Database connection information for Custom JSP page.

Hi Sascha,
When trying to use the code you supplied, I receive the error, "javax.servlet.jsp.JspException: Report ID is not available in GetJDBCConnectionTag". We have PPM 7.5. Any example or direction would be greatly appreciated.


Regards,
Chuck Crowell

//Add this to "OnDomLoad" event