Home » Developer & Programmer » JDeveloper, Java & XML » 'Invalid Column Index' error - Java Web applications
'Invalid Column Index' error - Java Web applications [message #547211] Tue, 13 March 2012 06:13 Go to next message
morrisman27
Messages: 4
Registered: March 2012
Junior Member
Hi! I need some help. If you could shed some light, that would really help!

I'm working on a Java Web Application
(User input(jsp)<-->Controller<----->DAO<----->Database) project where the user submits certain fields (partyId, DocId, cbrxPath, cbrValue, nodeId, TRANSLATION_Class and so on)and they are saved in 3 different tables (Party_Document_Node table, Routing and Routing_Node table).

Here's the portion of the DAO code below where the problem is:

public class AssociationsDAOOracleImpl implements AssociationsDAO {
private static final String PARTIES_WITH_ASSOCIATIONS = " Select distinct(party_id) from PARTY_DOCUMENT ORDER BY 1";

private static final String ASSOCIATION_FETCH_QUERY = " Select PARTY_ID, DOCUMENT_ID, NODE_ID, APPLICATION_CONTEXT_XPATH, DOCUMENT_LOGGING FROM PARTY_DOCUMENT_NODE WHERE PARTY_ID = ? ORDER BY PARTY_ID ";

private static final String ROUTING_FETCH_QUERY = " Select CONTENT_ROUTING_PATH FROM ROUTING WHERE PARTY_ID = ? AND DOCUMENT_ID =? ORDER BY PARTY_ID ";

private static final String ROUTINGNODE_FETCH_QUERY = " Select CONTENT_ROUTING_VALUE, TRANSLATION_CLASS FROM ROUTING_NODE WHERE PARTY_ID = ? AND DOCUMENT_ID =? AND NODE_ID =? ORDER BY PARTY_ID ";

private static final String CONTEXT_FETCH_QUERY = " Select CONTEXT_VALUE, CONTEXT_INSTANCE, CONTEXT_KEY FROM APPLICATION_CONTEXT where party_id = ? and document_id = ? and node_id = ? ";

private static final String NODEID_FETCH_QUERY = " Select NODE_ID FROM PARTY_DOCUMENT_NODE WHERE PARTY_ID = ? AND DOCUMENT_ID =? AND NODE_ID =? ORDER BY PARTY_ID ";

private static final String DEL_ASSOCIATIONS = "DELETE FROM PARTY_DOCUMENT_NODE where party_id = ? ";

private static final String DEL_ROUTING_PATH = "DELETE FROM ROUTING where party_id = ? ";

private static final String DEL_ROUTING_NODE = "DELETE FROM ROUTING_NODE where party_id = ? ";

private static final String DEL_APP_CONTEXTS = " Delete from APPLICATION_CONTEXT WHERE PARTY_ID = ? ";

private static final String ISRT_ASSOCIATIONS = " Insert into PARTY_DOCUMENT_NODE( PARTY_ID, DOCUMENT_ID, NODE_ID, TRANSLATION_CLASS, APPLICATION_CONTEXT_XPATH, DOCUMENT_LOGGING) VALUES (?,?,?,?,?,?)";

private static final String ISRT_ROUTING_PATH = " Insert into ROUTING( PARTY_ID, DOCUMENT_ID, CONTENT_ROUTING_PATH) VALUES (?,?,?)";

private static final String ISRT_ROUTING_NODE = " Insert into ROUTING_NODE( PARTY_ID, DOCUMENT_ID, NODE_ID, CONTENT_ROUTING_VALUE, TRANSLATION_CLASS) VALUES (?,?,?,?,?)";

private static final String ISRT_APP_CONTEXTS = "Insert into APPLICATION_CONTEXT(PARTY_ID, DOCUMENT_ID, NODE_ID, CONTEXT_VALUE, CONTEXT_INSTANCE, CONTEXT_KEY) VALUES (?,?,?,?,?,?)";



public PartyDocumentNodeVO[] fetchAllAssociations(String partyId)
throws SQLException {
ArrayList<PartyDocumentNodeVO> docs = new ArrayList<PartyDocumentNodeVO>();
PreparedStatement pStmt = null;
PreparedStatement innerSt = null;
PreparedStatement xpathSt = null;
PreparedStatement valueSt = null;
Connection con = DatabaseUtility.getDatabaseConnection();
try {
pStmt = con.prepareStatement(ASSOCIATION_FETCH_QUERY);
pStmt.setString(1, partyId);
innerSt = con.prepareStatement(CONTEXT_FETCH_QUERY);
xpathSt = con.prepareStatement(ROUTING_FETCH_QUERY);
valueSt = con.prepareStatement(ROUTINGNODE_FETCH_QUERY);


ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
PartyDocumentNodeVO thisVO = new PartyDocumentNodeVO();
String thisPartyId = rs.getString("PARTY_ID");
String thisDocumentId = rs.getString("DOCUMENT_ID");
String thisNodeId = rs.getString("NODE_ID");

String thisAppCtxXP = rs.getString("APPLICATION_CONTEXT_XPATH");

//adding new field
// cwc 05/05/2009
String thisDocLog = rs.getString("DOCUMENT_LOGGING");
thisVO.setDocumentLogging(thisDocLog);


thisVO.setPartyId(thisPartyId);
thisVO.setDocumentId(thisDocumentId);
thisVO.setNodeId(thisNodeId);

thisVO.setApplicationContextXpath(thisAppCtxXP);

//adding CBR fields on 01/24/12

xpathSt.setString(1, thisPartyId);
xpathSt.setString(2, thisDocumentId);
ResultSet xpathRs = xpathSt.executeQuery();
while (xpathRs.next()) {
String thisCbrXP = xpathRs.getString("CONTENT_ROUTING_PATH");
thisVO.setContentRoutingPath(thisCbrXP);
}
xpathRs.close();


valueSt.setString(1, thisPartyId);
valueSt.setString(2, thisDocumentId);
valueSt.setString(3, thisNodeId);
ResultSet valueRs = valueSt.executeQuery();
while (valueRs.next()) {
String thisCbrValue = valueRs.getString("CONTENT_ROUTING_VALUE");
String cbrTC = valueRs.getString("TRANSLATION_CLASS");
thisVO.setCbrValue(thisCbrValue);
thisVO.setTranslationClass(cbrTC);

}
valueRs.close();



// get the context params for this combo.
innerSt.setString(1, thisPartyId);
innerSt.setString(2, thisDocumentId);
innerSt.setString(3, thisNodeId);
ArrayList<ApplicationContextVO> contextParms = new ArrayList<ApplicationContextVO>();
ResultSet innerRs = innerSt.executeQuery();
while (innerRs.next()) {
String contextVal = innerRs.getString("CONTEXT_VALUE");
String contextInstance = innerRs
.getString("CONTEXT_INSTANCE");
String contextKey = innerRs.getString("CONTEXT_KEY");
ApplicationContextVO thisCtxVO = new ApplicationContextVO();
thisCtxVO.setContextValue(contextVal);
thisCtxVO.setContextInstance(contextInstance);
thisCtxVO.setContextKey(contextKey);
contextParms.add(thisCtxVO);
}
innerRs.close();
thisVO.setContextParams(contextParms);
docs.add(thisVO);
}
rs.close();
xpathSt.close();
valueSt.close();
innerSt.close();
pStmt.close();

PartyDocumentNodeVO[] theReturn = new PartyDocumentNodeVO[docs
.size()];
docs.toArray(theReturn);
return theReturn;
} catch (SQLException se) {
se.printStackTrace();
throw se;
} finally {
try {
con.close();
} catch (SQLException sq) {
}
}
}



Here are the details of the tables:
1. ROUTING table:
PARTY_ID, DOCUMENT_ID, CONTENT_ROUTING_PATH

mainly, the jsp gets the value for CBR XPATH from CONTENT_ROUTING_PATH.

2. ROUTING_NODE table:
PARTY_ID, DOCUMENT_ID, NODE_ID, CONTENT_ROUTING_VALUE, TRANSLATION_CLASS

mainly, the jsp gets the value for CBR value from CONTENT_ROUTING_VALUE.

3.PARTY_DOCUMENT_NODE table:
PARTY_ID, DOCUMENT_ID, NODE_ID, TRANSLATION_CLASS, APPLICATION_CONTEXT_XPATH, DOCUMENT_LOGGING

other columns (APPLICATION_CONTEXT_XPATH etc.) in the jsp gets populated from this table.




I'm getting the error, Invalid Column Index.

Can u help?
Re: 'Invalid Column Index' error - Java Web applications [message #547215 is a reply to message #547211] Tue, 13 March 2012 06:22 Go to previous message
morrisman27
Messages: 4
Registered: March 2012
Junior Member
I'm sorry. This (below) is the code now since I've changed things around.


private static final String PARTIES_WITH_ASSOCIATIONS = " Select distinct(party_id) from PARTY_DOCUMENT ORDER BY 1";

private static final String ASSOCIATION_FETCH_QUERY = " Select PARTY_ID, DOCUMENT_ID, NODE_ID, TRANSLATION_CLASS, APPLICATION_CONTEXT_XPATH, DOCUMENT_LOGGING FROM PARTY_DOCUMENT_NODE WHERE PARTY_ID = ? ORDER BY PARTY_ID ";

private static final String ROUTING_FETCH_QUERY = " Select CONTENT_ROUTING_PATH, DOCUMENT_ID FROM ROUTING WHERE PARTY_ID = ? ORDER BY PARTY_ID ";

private static final String ROUTINGNODE_FETCH_QUERY = " Select CONTENT_ROUTING_VALUE, TRANSLATION_CLASS, NODE_ID FROM ROUTING_NODE WHERE PARTY_ID = ? AND DOCUMENT_ID =? ORDER BY PARTY_ID ";

private static final String CONTEXT_FETCH_QUERY = " Select CONTEXT_VALUE, CONTEXT_INSTANCE, CONTEXT_KEY FROM APPLICATION_CONTEXT where party_id = ? and document_id = ? and node_id = ? ";

private static final String NODEID_FETCH_QUERY = " Select NODE_ID FROM PARTY_DOCUMENT_NODE WHERE PARTY_ID = ? AND DOCUMENT_ID =? AND NODE_ID =? ORDER BY PARTY_ID ";

private static final String DEL_ASSOCIATIONS = "DELETE FROM PARTY_DOCUMENT_NODE where party_id = ? ";

private static final String DEL_ROUTING_PATH = "DELETE FROM ROUTING where party_id = ? ";

private static final String DEL_ROUTING_NODE = "DELETE FROM ROUTING_NODE where party_id = ? ";

private static final String DEL_APP_CONTEXTS = " Delete from APPLICATION_CONTEXT WHERE PARTY_ID = ? ";

private static final String ISRT_ASSOCIATIONS = " Insert into PARTY_DOCUMENT_NODE( PARTY_ID, DOCUMENT_ID, NODE_ID, TRANSLATION_CLASS, APPLICATION_CONTEXT_XPATH, DOCUMENT_LOGGING) VALUES (?,?,?,?,?,?)";

private static final String ISRT_ROUTING_PATH = " Insert into ROUTING( PARTY_ID, DOCUMENT_ID, CONTENT_ROUTING_PATH) VALUES (?,?,?)";

private static final String ISRT_ROUTING_NODE = " Insert into ROUTING_NODE( PARTY_ID, DOCUMENT_ID, NODE_ID, CONTENT_ROUTING_VALUE, TRANSLATION_CLASS) VALUES (?,?,?,?,?)";

private static final String ISRT_APP_CONTEXTS = "Insert into APPLICATION_CONTEXT(PARTY_ID, DOCUMENT_ID, NODE_ID, CONTEXT_VALUE, CONTEXT_INSTANCE, CONTEXT_KEY) VALUES (?,?,?,?,?,?)";



public PartyDocumentNodeVO[] fetchAllAssociations(String partyId)
throws SQLException {
ArrayList<PartyDocumentNodeVO> docs = new ArrayList<PartyDocumentNodeVO>();
PreparedStatement pStmt = null;
PreparedStatement innerSt = null;
PreparedStatement xpathSt = null;
PreparedStatement valueSt = null;
Connection con = DatabaseUtility.getDatabaseConnection();
try {
pStmt = con.prepareStatement(ASSOCIATION_FETCH_QUERY);
pStmt.setString(1, partyId);
innerSt = con.prepareStatement(CONTEXT_FETCH_QUERY);
xpathSt = con.prepareStatement(ROUTING_FETCH_QUERY);
valueSt = con.prepareStatement(ROUTINGNODE_FETCH_QUERY);


ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
PartyDocumentNodeVO thisVO = new PartyDocumentNodeVO();
String thisPartyId = rs.getString("PARTY_ID");
String thisDocumentId = rs.getString("DOCUMENT_ID");
String thisNodeId = rs.getString("NODE_ID");
String thisTC = rs.getString("TRANSLATION_CLASS");
String thisAppCtxXP = rs.getString("APPLICATION_CONTEXT_XPATH");

//adding new field
// cwc 05/05/2009
String thisDocLog = rs.getString("DOCUMENT_LOGGING");
thisVO.setDocumentLogging(thisDocLog);


thisVO.setPartyId(thisPartyId);
thisVO.setDocumentId(thisDocumentId);
thisVO.setNodeId(thisNodeId);
thisVO.setTranslationClass(thisTC);
thisVO.setApplicationContextXpath(thisAppCtxXP);

// get the context params for this combo.
innerSt.setString(1, thisPartyId);
innerSt.setString(2, thisDocumentId);
innerSt.setString(3, thisNodeId);
ArrayList<ApplicationContextVO> contextParms = new ArrayList<ApplicationContextVO>();
ResultSet innerRs = innerSt.executeQuery();
while (innerRs.next()) {
String contextVal = innerRs.getString("CONTEXT_VALUE");
String contextInstance = innerRs
.getString("CONTEXT_INSTANCE");
String contextKey = innerRs.getString("CONTEXT_KEY");
ApplicationContextVO thisCtxVO = new ApplicationContextVO();
thisCtxVO.setContextValue(contextVal);
thisCtxVO.setContextInstance(contextInstance);
thisCtxVO.setContextKey(contextKey);
contextParms.add(thisCtxVO);
}
innerRs.close();
thisVO.setContextParams(contextParms);
docs.add(thisVO);
}
rs.close();
//xpathSt.close();
//valueSt.close();
innerSt.close();
pStmt.close();


//New CBR Logic

xpathSt.setString(1, partyId);
ResultSet xpathRs = xpathSt.executeQuery();
while (xpathRs.next()) {
PartyDocumentNodeVO foundVO = new PartyDocumentNodeVO();
String thisCbrXP = xpathRs.getString("CONTENT_ROUTING_PATH");
String thisCbrDoc = xpathRs.getString("DOCUMENT_ID");
foundVO.setContentRoutingPath(thisCbrXP);
foundVO.setDocumentId(thisCbrDoc);


valueSt.setString(1, partyId);
valueSt.setString(2, thisCbrDoc);
ResultSet valueRs = valueSt.executeQuery();
if (valueRs.next()) {
String thisCbrValue = valueRs.getString("CONTENT_ROUTING_VALUE");
String thisTC = valueRs.getString("TRANSLATION_CLASS");
String thisCbrNode = valueRs.getString("NODE_ID");
foundVO.setCbrValue(thisCbrValue);
foundVO.setTranslationClass(thisTC);
foundVO.setNodeId(thisCbrNode);

}


while (valueRs.next()) {
PartyDocumentNodeVO newVO = new PartyDocumentNodeVO();
String thisCbrValue = valueRs.getString("CONTENT_ROUTING_VALUE");
String thisTC = valueRs.getString("TRANSLATION_CLASS");
String thisCbrNode = valueRs.getString("NODE_ID");
newVO.setCbrValue(thisCbrValue);
newVO.setTranslationClass(thisTC);
newVO.setNodeId(thisCbrNode);
newVO.setPartyId(partyId);
newVO.setDocumentId(thisCbrDoc);
newVO.setContentRoutingPath(thisCbrXP);

}


valueRs.close();
}
xpathRs.close();
xpathSt.close();
valueSt.close();


//End New CBR Logic


PartyDocumentNodeVO[] theReturn = new PartyDocumentNodeVO[docs
.size()];
docs.toArray(theReturn);
return theReturn;
} catch (SQLException se) {
se.printStackTrace();
throw se;
} finally {
try {
con.close();
} catch (SQLException sq) {
}
}
}




Please note section (New CBR Logic), this is where the problems is.









Previous Topic: BigDecimal issue
Next Topic: ORA-1460 while working with XML (merged 3)
Goto Forum:
  


Current Time: Thu Mar 28 09:01:38 CDT 2024