Native query issue

[michael haithcock]

I am forced to use a native query on a multi table select because of a relationship that is not enforced on the database.

The query works fine in a query analyzer, but within the list that is returned, one of the columns is truncated.

I am trying to return a participants ssn (defined as varchar(9)), why does it only return the first number?

Here is the code:

public List findPartByName(PartParticipant ppt) {
    	String query = " SELECT DISTINCT PPT_ID, "
		+ "         CNA_FST_NM, " 
	    + "         CNA_MID_NM, "
	    + "         CNA_LST_NM, "
	    + "         CNA_SUFFIX_TXT, "
	    + "         PSS_SSN_TXT, "
	    + "         PPT_OF_BRTH_DTE, "
		+ "         RCP.FRA_TYPE_TXT as AcctType "
		+ " FROM part_participant "
		+ " INNER JOIN part_name_xref ON PPT_ID = PNX_PPT_ID "
		+ " INNER JOIN cmmn_name ON PNX_CNA_ID = CNA_ID " 
		+ "  LEFT JOIN part_ssn ON PPT_ID = PSS_PPT_ID "
		+ " WHERE 1 = 1 ";
    	
    	String whereClause = "";
    	
    	if(ppt.getPptOfBrthDte() != null) {
    		whereClause = whereClause + " AND PPT_OF_BRTH_DTE = '" + ppt.getPptOfBrthDte() + "'";
    	}
    	
    	if(ppt.getCommonNames() != null) {
    		
    		Set<CommonName> entityNames = ppt.getCommonNames();
			Object[] entityNamesArray = new CommonName[entityNames
					.size()];
			entityNamesArray = entityNames.toArray();
			//setToArray(entityNamesArray, entityNames);
			CommonName cna = (CommonName) entityNamesArray[0];

    		if (cna.getCnaFstNm() != null && cna.getCnaFstNm().length() > 0) {
    			whereClause = whereClause + " AND CNA_FST_NM LIKE '" + cna.getCnaFstNm() + "%'";
    		}
    		if (cna.getCnaMidNm() != null && cna.getCnaMidNm().length() > 0) {
    			whereClause = whereClause + " AND CNA_MID_NM LIKE '" + cna.getCnaMidNm() + "%'";
    		}
    		if (cna.getCnaLstNm() != null && cna.getCnaLstNm().length() > 0) {
    			whereClause = whereClause + " AND CNA_LST_NM LIKE '" + cna.getCnaLstNm() + "%'";
    		}
    		if (cna.getCnaSuffixTxt() != null && cna.getCnaSuffixTxt().length() > 0) {
    			whereClause = whereClause + " AND getCnaSuffixTxt = '" + cna.getCnaSuffixTxt() + "'";
    		}	
    	}
    	
    	
    	if(ppt.getPartSSNs() != null) {
    		Set<PartSSN> entitySSNs = ppt.getPartSSNs();
    		Object[]  entitySSNArray = new PartSSN[entitySSNs.size()];
			entitySSNArray = entitySSNs.toArray();
    		//setToArray(entitySSNArray, entitySSNs);
			PartSSN ssn = (PartSSN) entitySSNArray[0];
			
			
			if (ssn.getPssSsnTxt() != null && ssn.getPssSsnTxt().length() > 0) {
    			whereClause = whereClause + " AND PSS_SSN_TXT = '" + ssn.getPssSsnTxt() + "'";
    		}
    	}
    	
    	query = query + whereClause;
    	
    	
		EntityManager manager = getEntityManager();
		List list = manager.createNativeQuery(query).getResultList();
		return list;
}

Please help.

Thank you

Mike

[Cameron Wallace McKenzie]

  PartSSN ssn = (PartSSN) entitySSNArray[0];   

Is that pulling out the String, or just the first character of the String?