August 26, 2009 by burton.

        I have an application that uses the MVC approach and Oracle’s ADF.  The model and web portion of the application share a session to maintain user data and positions in the tables.  We had a problem of when the users session was timed out (set to an hour in the model and web.xml),  the J2EE Container would automatically take over and re-authenicate the user and take them back to their page. However, our application has custom code that runs when the users come through the index.jsp page that sets a view things and determines what their home page is.  Because the data-model is reset when a session times out and the page would not be in sync with the data model, causing data corruption in our system.

I searched the oracle forums looking for an answer to this problem and thought I had found the solution in this article :

http://thepeninsulasedge.com/frank_nimphius/2007/08/22/adf-faces-detecting-and-handling-user-session-expiry/

Now Frank is a guru when it comes to Jdeveloper and his solution will work for non container based applications, I confirmed this through the forums with a dialog with frank.  He also stated that the J2EE container automatically steps in and can’t be over written.  

So here I have this great solution, from frank, which is to add a custom page controller to the web.xml that compares the requested session to the curent session and redirect if they don’t match. Of course with the container stepping in, they always matched and always rendered the page.  I needed a way to set a custom session variable upon valid login and then have my page controller check it.  

Solution found, or so I thought!   The controller would go before my custom code to set the session variable, so no pages would ever render.  The solution, allow some pages to bypass the custom controller.  There might be a way to do this in the web.xml, but I found it easier to implement in code so here is what I did.

1.  First read Frank’s Article to get a better idea how all this is supposed to work.

2.  Open your web.xml file and add the following code.  Your Filter and mapping will go at the end of the existing filters and mappings.
 <filter>
        <filter-name>ApplicationSessionExpiryFilter</filter-name>
        <filter-class>

            lmit.wots.controller.custom.pagecontroller.ApplicationSessionExpiryFilter

        </filter-class>
        <init-param>
            <param-name>SessionTimeoutRedirect</param-name>
            <param-value>index.jspx</param-value>
        </init-param>
 </filter>

Make sure to use the correct path of where you create your custom classes.

  <filter-mapping>
        <filter-name>ApplicationSessionExpiryFilter</filter-name>
        <servlet-name>Faces Servlet</servlet-name>
 </filter-mapping

3.  Change the code from Franks Solution to:
   
public void doFilter(ServletRequest request, 
            ServletResponse response,
            FilterChain chain) throws IOException, ServletException {
 
boolean bypass = false;
String URL = ((HttpServletRequest)request).getRequestURL().toString();

//These are the two pages that I have allowed to be bypassed
if (URL.contains(”index.jspx”) || URL.contains(”invalidSession.jsp”) )
{
  bypass = true;
}
String data = new String();
try
{
 HttpServletRequest httpRequest = (HttpServletRequest)request;
 HttpSession session = httpRequest.getSession();
 data = session.getAttribute(”logged”) + “”;
}
catch (Exception e)
{
 e.printStackTrace();
}
// VALID is what is set as a session variable when the user

// comes in through the front door.

if (data.compareTo(”VALID”) ==0 || bypass)
{
  chain.doFilter(request, response);
}
else
{
 ((HttpServletResponse) response).sendRedirect(_filterConfig.getInitParameter (”SessionTimeoutRedirect”));
}
}
}

4. Change the redirect page to your main page or a error page telling the user they timed out.  I set mine back to my index page so the user is forced through the front door again and everything is set up again.

5.  In your main page that your users come, or if you have default code that runs for a user on the presentation layer, add code to set the session variable, here is mine:

This is my index.java code that is linked to my JSPX page.
public void beforePhase(PagePhaseEvent event)
{
super.beforePhase(event);
   FacesContext fctx = FacesContext.getCurrentInstance();
   try
   {
    HttpSession session = (HttpSession) fctx.getExternalContext().getSession(false);
      session.setAttribute(”logged”, “VALID”);
   }
   catch (Exception e)
   {
   e.printStackTrace();
   }
  }

The only downside to this method is the fact that the user will have to re-navigate to their work location again since I force them back through the front door, however this a small inconvenience in order to ensure data integrity.

Posted in JSF / ADF | Print | No Comments »

November 30, 2007 by burton.

Working with Business Intelligence Publisher Reports in Jdeveloper 10g

JDeveloper is a great tool for writing your applications with the exception of lacking a usable reporting tool. For my projects I have been using Jasper Reports with a lot of success, however jasper is limited in some ways, so I am always looking for a new report tool. Oracle’s xmlpublisher / BI Publisher in it’s newest form looks like it might do the trick.

Like any Computer Geek I got excited when I demoed the new BIP ( I always love new toys), it was easy to configure and setup, the reports are easy to create using word templates and RTF, and it came with an API to use in your java programs. Being an Oracle tool, of course it would would just drop into JDEV and work right? Wrong? The APIs do not utilize the XDO report files that BIP creates, so in order to use it in JDEV you would have to create new data templates to exatract your data and then run it though the apis to generate your reports. Since the XDO report files are xml, I sat down and wrote a class that will exactract the the items I need to generate my reports in jdev using my BIP reports.

This code is quick and to the point, still needs some error handling and polishing, but you can get the idea of how it works. Since this is my first time using the xml parser, I am sure that I am doing things the hard way. I found idea for the XML reading routines from an article I found online, which outlined using Oracle’s DOM XML parser, I figured since I am in JDEV anyways might as well use the oracle clases when I can.

Here is the Code for the class:

package yourcompany.yourapp.model.custom;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//This is a custom class to take a report from Business Intelligence Publisher and extract
//The needed data to utilize the APIs from BIP to render your reports.
//This class will take the XDO report file and extract the following items:
// The SQL Statement that generates the data XML File
// The Template File Name
// The Parameters used in the report
// these items can be then passed to the APIs to generate your reports
//
// This class still needs error handling to make it complete
// 29 Nov 2007 - Kelly Burton
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;

import java.util.ArrayList;

import oracle.xml.jaxp.JXDocumentBuilder;
import oracle.xml.jaxp.JXDocumentBuilderFactory;
import oracle.xml.parser.v2.XMLAttr;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLElement;
import oracle.xml.parser.v2.XMLNode;

import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.NodeList;

public class parseXDO {
private String sqlData;
private String xdoFileName;
private ArrayList parameters = new ArrayList();
private String template;

public parseXDO() {
this.sqlData = null;
this.xdoFileName = null;
this.template = null;
}
public parseXDO(String xdoFile){

this.setReportFileName(xdoFile);
}
public void setTemplate(String template) {
this.template = template;
}

public String getTemplate() {
if (this.template == null ) this.processReport();
return template;
}
public void setSqlData(String sqlData) {
this.sqlData = sqlData;
}

public String getSqlData() {
if (this.sqlData == null ) this.processReport();
return sqlData;
}

public void setReportFileName(String xdoFileName) {
this.xdoFileName = xdoFileName;
if (xdoFileName !=null){
this.processReport();
}
}

public String getReportFileName() {
return xdoFileName;
}

public void setParameters(ArrayList parameters) {
this.parameters = parameters;
}

public ArrayList getParameters() {
return parameters;
}

private void processReport() {
try {
JXDocumentBuilderFactory factory = (JXDocumentBuilderFactory) JXDocumentBuilderFactory.newInstance();
JXDocumentBuilder documentBuilder = (JXDocumentBuilder) factory.newDocumentBuilder();
InputStream input = new FileInputStream(new File(this.xdoFileName));
XMLDocument xmlDocument = (XMLDocument) (documentBuilder.parse(input));

XMLElement rootElement = (XMLElement) (xmlDocument.getDocumentElement());

if (rootElement.hasChildNodes()) {
NodeList nodeList = rootElement.getChildNodes();
for (int i = 0; i < nodeList.getLength(); i++) {
XMLNode node = (XMLNode) nodeList.item(i);
if (node.getNodeType() == XMLNode.ELEMENT_NODE) {
XMLElement element = (XMLElement) node;

// This is the start of the loop for getting the items from the report.
if (element.getTagName().compareTo(”templates”)== 0) {
if (element.hasChildNodes() ) {
NodeList nl = element.getChildNodes();
for (int j = 0; j < nl.getLength(); j++){
XMLNode cn = (XMLNode) nl.item(j);
if (cn.getLocalName() !=null && cn.getLocalName().compareTo(”template”)==0){
if (cn.hasAttributes ()){
NamedNodeMap xattributes = (NamedNodeMap) cn.getAttributes();
for (int l =0; l < xattributes.getLength(); l++){
XMLAttr xattribute = (XMLAttr) xattributes.item(l);
if (xattribute.getName().compareTo(”url”)==0){
this.template = xattribute.getValue().trim();
}
}
}
}
}
}
} //End if TEMPLATES
if (element.getTagName().compareTo(”dataModel”)== 0) {
if (element.hasChildNodes() ) {
NodeList nl = element.getChildNodes();
for (int j = 0; j < nl.getLength(); j++){
XMLNode cn = (XMLNode) nl.item(j);
if (cn.getLocalName() !=null &&
cn.getLocalName().compareTo(”dataSet”)==0 ){
this.sqlData = cn.getText().trim();
}
}
}
} //end if DATAMODEL
if (element.getTagName().compareTo(”parameters”)== 0) {
if (element.hasChildNodes() ) {
NodeList nl = element.getChildNodes();
for (int j = 0; j < nl.getLength(); j++){
XMLNode cn = (XMLNode) nl.item(j);
if (cn.getLocalName() !=null && cn.getLocalName().compareTo(”parameter”)==0){
if (cn.hasAttributes ()){
NamedNodeMap xattributes = (NamedNodeMap) cn.getAttributes();
for (int l =0; l < xattributes.getLength(); l++){
XMLAttr xattribute = (XMLAttr) xattributes.item(l);
if (xattribute.getName().compareTo(”id”)==0){
this.parameters.add(xattribute.getValue());
}
}
}
}
}
}
}//end if PARAMETERS
}
}
}

}catch (Exception e){
e.printStackTrace();
}
}

public static void main(String[] args) {
//Constructor file file name to create and process in one.
//Testing code

parseXDO parseXDO = new parseXDO(”c:/xmlwork/readmeFinal.xdo”);
System.out.println(parseXDO.getTemplate());
System.out.println(parseXDO.getReportFileName());
System.out.println(parseXDO.getSqlData());

//No Constructor Test
//parseXDO myXDO = new parseXDO();
//myXDO.setReportFileName(”c:/xmlwork/readmeFinal.xdo”);
//System.out.println(myXDO.getSqlData());

}

}

Using this class, you can take your XDO file and extract the SQL for the report, the parameters for your report, and the name of your template. Using this information you can pass it to the APIs to generate your reports using BIP/XMLPublisher and JDEV. Next Article I will show you how to use this class and the APIs to generate your reports for the user to download or view on the web page.

Posted in JSF / ADF | Print | 5 Comments »

November 21, 2006 by burton.

         I recently had the need to have my Jdev project apply sql packages and code to the server. The first place I looked was the Ant 1.2.6 source code, since ant has a sql task I thought it would be simple copy. While Ant can apply sql, it will tell you if the compile of the package or procedure went wrong. This article will present the class I created to apply sql to the server and track any errors produced. In the grand scheme of things this class will be implemented into our code building system that we are developing in house.

     The area of concern is what database connection to use. The easiest way is to use the connection that your application module is using, however in my case I needed the ability to create a connection to any of our databases so I used the following code:

public boolean getConn(String connectionString, String user,
String password) throws SQLException {
boolean status = false;
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
this.conn =
DriverManager.getConnection(connectionString, user, password);
if (this.conn != null) {
status = true;
} else {
status = false;
}
return status;
}

 

 

The connectionString, user and Password are private variables in the class that are set at runtime.

 

 

     Once we have established a connection, we have to parse the SQL that is passed into the class. This code is based on the Ant 1.2.6 code. I foolishly thought I could just pass the whole ball of sql to the instance and it work itself out, not the case, you have to parse and call in chunks.

 

protected void runStatements(Reader reader) throws SQLException,
IOException {
StringBuffer sql = new StringBuffer();
String line = “”;

BufferedReader in = new BufferedReader(reader);

while ((line = in.readLine()) != null) {

if (packageName == null) {
if (!line.trim().startsWith((”–”)) &&
(line.toUpperCase().contains(”CREATE OR REPLACE”) ||
line.toUpperCase().contains(”CREATE”))) {
setPackageName(findObjectName(line));
}
}

sql.append(”n” + line);
if (line.equals(delimiter)) {
execSQL(sql.substring(0, sql.length() - delimiter.length()));
sql.replace(0, sql.length(), “”);
setPackageName(null);
}
}
// Catch any statements not followed by /
if (!sql.equals(”")) {
execSQL(sql.toString());
}
}

 

 

     The main part of the code that I had to add was the error checking part, after we run the sql to create a package or other code entity, we need to check the status of that package. By looking into the user_errors table, you can find out if there were any compile errors:

 

private Integer checkSQLErrors() {
Integer numOfErrors = 0;
try {
String SQL =
“Select count(*) as errors from user_errors where upper(name) = ‘” +
getPackageName() + “‘”;
estatement = conn.createStatement();
ResultSet myResults = estatement.executeQuery(SQL);
while (myResults.next()) {
numOfErrors = myResults.getInt(”errors”);
}

} catch (SQLException s) {
System.out.println(s.getMessage());
} finally {
try {
if (estatement != null) {
estatement.close();
}
} catch (java.sql.SQLException s2) {
estatement = null;
}
}
return numOfErrors;
}

If there are errors, the code pushes them into a string array for the user to do with as they please. For my purposes, the errors are attached to the original file for later analysis. This code might still be rough around the edges, as it is still a work in progress. But duing my initial testing it works great for applying sql code to a box. Below is the full source text and the test code to run it.

 

 

package yourcompany.yourapp.model.custom.spray.tools;
import lmit.wots.model.custom.spray.tools.inteferaces.toolsInterface;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Iterator;
public class buildSQL {
private Connection conn = null;
private Statement statement = null;
private Statement estatement = null;
private String delimiter = “/”;
private String fileName = null;
private File sourceFile = null;
private String packageName = null;
private Integer errorCount = 0;
private String connectionString = null;
private String passWord = null;
private String userName = null;
private ArrayList Errors = new ArrayList();
public buildSQL() {

}

public ArrayList getErrors() {
return this.Errors;
}

public Iterator ErrorIterator() {
return this.Errors.iterator();
}

private void addError(String ErrMessage) {
Errors.add(ErrMessage);
}

public void setConnectionString(String myConn, String myName,
String myPass) {
this.connectionString = myConn;
this.userName = myName;
this.passWord = myPass;
}

private void incErrorCount(Integer addTo) {
if (addTo != null || addTo > 0)
errorCount = errorCount + addTo;
}

private void incErrorCount() {
errorCount = errorCount + 1;
}
public Integer ErrorCount() {
return errorCount;
}

public boolean getConn(String connectionString, String user,
String password) throws SQLException {
boolean status = false;
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
this.conn =
DriverManager.getConnection(connectionString, user, password);
if (this.conn != null) {
status = true;
} else {
status = false;
}
return status;
}
/**
* Add a SQL transaction to execute
*/
public void setFileName(String NameFile) {
this.fileName = NameFile;
}

public String getFileName() {
return this.fileName;
}

public void FileName(String _fileName) {
this.fileName = _fileName;
}
public String FileName(){
return this.fileName;
}
public void setPackageName(String pName) {
this.packageName = pName;
}

public String getPackageName() {
return this.packageName;
}

private Integer checkSQLErrors() {
Integer numOfErrors = 0;
try {
String SQL =
“Select count(*) as errors from user_errors where upper(name) = ‘” +
getPackageName() + “‘”;
estatement = conn.createStatement();
ResultSet myResults = estatement.executeQuery(SQL);
while (myResults.next()) {
numOfErrors = myResults.getInt(”errors”);
}

} catch (SQLException s) {
System.out.println(s.getMessage());
} finally {
try {
if (estatement != null) {
estatement.close();
}
} catch (java.sql.SQLException s2) {
estatement = null;
}
}
return numOfErrors;
}

public void getSQLErrors() {
Statement st = null;
String eName = null;
String eType = null;
String eSeq = null;
String eLine = null;
String ePos = null;
String eText = null;
String errText = “”;
try {
String SQL =
“Select * from user_errors where upper(name) = ‘” + getPackageName() +
“‘ order by SEQUENCE”;
statement = this.conn.createStatement();
ResultSet myResults = statement.executeQuery(SQL);
while (myResults.next()) {
errText = “”;
eName = myResults.getString(”NAME”);
eType = myResults.getString(”TYPE”);
eSeq = myResults.getString(”SEQUENCE”);
eLine = myResults.getString(”LINE”);
ePos = myResults.getString(”POSITION”);
eText = myResults.getString(”TEXT”);

errText = errText.concat(eType + “->” + eName + “n”);
errText =
errText.concat(”Seq->” + eSeq + ” Line->” + eLine + ” Position->” +
ePos + “n”);
errText = errText.concat(eText + “nn”);
addError(errText);
}

} catch (SQLException s) {
System.out.println(s.getMessage());
} finally {
try {
if (st != null) {
st.close();
}
} catch (java.sql.SQLException s2) {
st = null;
}
}
}

protected String findObjectName(String SQL) {
String objectName = null;
String token = SQL;
if (token.toUpperCase().contains(”CREATE OR REPLACE”) ||
token.toUpperCase().contains(”CREATE”)) {
objectName = token.toUpperCase();
objectName = objectName.replace(”CREATE OR REPLACE”, “”);
objectName = objectName.replace(”CREATE”, “”);
objectName = objectName.replace(”PACKAGE BODY”, “”);
objectName = objectName.replace(”PACKAGE”, “”);
objectName = objectName.replace(”FUNCTION”, “”);
objectName = objectName.replace(”PROCEDURE”, “”);
objectName = objectName.replace(”TABLE”, “”);
objectName = objectName.replace(”VIEW”, “”);
objectName = objectName.replace(”SEQUENCE”, “”);
objectName = objectName.replace(”TRIGGER”, “”);
objectName = objectName.replace(”INDEX”, “”);
objectName = objectName.replace(”IS”, “”);
objectName = objectName.trim();
}
return objectName;
}
/**
* read in lines and execute them
*/
protected void runStatements(Reader reader) throws SQLException,
IOException {
StringBuffer sql = new StringBuffer();
String line = “”;

BufferedReader in = new BufferedReader(reader);

while ((line = in.readLine()) != null) {

if (packageName == null) {
if (!line.trim().startsWith((”–”)) &&
(line.toUpperCase().contains(”CREATE OR REPLACE”) ||
line.toUpperCase().contains(”CREATE”))) {
setPackageName(findObjectName(line));
}
}

sql.append(”n” + line);
if (line.equals(delimiter)) {
execSQL(sql.substring(0, sql.length() - delimiter.length()));
sql.replace(0, sql.length(), “”);
setPackageName(null);
}
}
// Catch any statements not followed by /
if (!sql.equals(”")) {
execSQL(sql.toString());
}
}

/**
* Exec the sql statement.
*/
protected void execSQL(String sql) throws SQLException {
// Check and ignore empty statements
if (”".equals(sql.trim())) {
return;
}
System.out.println(”Compiling ->” + getPackageName());
ResultSet resultSet = null;
try {

boolean ret;

ret = statement.execute(sql);
SQLWarning warning = conn.getWarnings();
while (warning != null) {
System.out.println(warning + ” sql warning”);
warning = warning.getNextWarning();
}
conn.clearWarnings();
incErrorCount(checkSQLErrors());
if (checkSQLErrors() > 0) {
getSQLErrors();
System.out.println((”Failed to Compile: “));
}
} catch (SQLException e) {
System.out.println(”Failed to execute: ” + sql);
System.out.println(e.getMessage());
} finally {
if (resultSet != null) {
resultSet.close();
}
}
}

private void runSQL() throws IOException, SQLException {
if (sourceFile != null) {
System.out.println(”Executing file: ” +
sourceFile.getAbsolutePath());
Reader reader = new FileReader(sourceFile);
try {
runStatements(reader);
} finally {
reader.close();
}
}
}
public void build() {
try {
File dir = new File(fileName);
sourceFile = dir;
} catch (Exception e) {
// System.out.println(e.getMessage());
}

boolean connected;
try {
connected = getConn(connectionString, userName, passWord);
statement = conn.createStatement();
statement.setEscapeProcessing(true);

runSQL();

} catch (SQLException e) {
} catch (IOException e) {
System.out.println(”File does not Exist”);
incErrorCount();
}
}

}

The test code to give it a whirl:

package yourCompany.yourApp.model.custom.spray.tools.test;

import java.util.ArrayList;
import java.util.Iterator;

import lmit.wots.model.custom.spray.tools.buildSQL;

public class testBuildSQL {
public testBuildSQL() {
}

public static void main(String[] args) {
testBuildSQL testBuildSQL = new testBuildSQL();

ArrayList myFiles = new ArrayList();

buildSQL testCase = new buildSQL();
testCase.setConnectionString(”jdbc:oracle:thin:@server:PORT:SID”, “username”, “password”);
testCase.setFileName(”C:/tbuild/spec/naf_int_elements.pks”);

myFiles.add(testCase);

buildSQL testCase2 = new buildSQL();
testCase2.setConnectionString(”jdbc:oracle:thin:@server:PORT:SID”,
“username”, “password”);
testCase2.setFileName(”C:/tbuild/body/civ_naf_int_ar_out_pkg.pkb”);

myFiles.add(testCase2);

buildSQL currFile;
Iterator c = myFiles.iterator();
while (c.hasNext()) {
currFile = (buildSQL)c.next();
currFile.build();
if (currFile.ErrorCount() > 0) {
Iterator i = currFile.ErrorIterator();
while (i.hasNext()) {
System.out.println(i.next());
}
} else {
System.out.println(”No errors!”);
}
}
}
}

I welcome any comment, or even better improvements to the above code.