java.sql.SQLException: ORA-00904: invalid column name

[bbbrotg]

I am trying to get a simple session facade to run using JBoss 4.0.2 and Oracle 8i. The code compiles fine the beans deploy fine but when I run the client I get the following error...

2005-07-12 09:03:51,046 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.District#findNameGivenNumber] Executing SQL: SELECT t0_o.countyNbr, t0_o.districtNbr FROM DISTRICT t0_o WHERE (t0_o.districtNbr = ?)
2005-07-12 09:03:51,046 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.District#findNameGivenNumber] param: i=1, type=VARCHAR, value=99
2005-07-12 09:03:51,077 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.District#findNameGivenNumber] Find failed
java.sql.SQLException: ORA-00904: invalid column name

Does anyone know what causes this? Willing to give reward for a solution.

[schmidts]

Check whether your table DISTRICT really has mixed uppercase/lowercase column names (countyNbr, districtNbr). If so, those column names should be surrounded by quotation marks ("countyNbr", "districtNbr").

You might want to try something like this in SQLPLUS:
DESC DISTRICT
SELECT t0_o."countyNbr", t0_o."districtNbr" FROM DISTRICT t0_o WHERE (t0_o."districtNbr" = 99);

[schmidts]

"schmidts" wrote:

SELECT t0_o."countyNbr", t0_o."districtNbr" FROM DISTRICT t0_o WHERE (t0_o."districtNbr" = 99);

this should read:

SELECT t0_o."countyNbr", t0_o."districtNbr" FROM DISTRICT t0_o WHERE (t0_o."districtNbr" = '99');

(note the '99' as the excerpt of your log files suggests that "districtNbr" is a VARCHAR column.)

[schrouf]

Maybe you should enable CMP debugging, so you can see all database specific SQL statements within your server log file. Add the following entry

 <category name="org.jboss.ejb.plugins.cmp">
 <priority value="DEBUG"/>
 </category>

to your ..conf/log4j.xml.

Regards
Ulf

[bbbrotg]

In the actual Oracle databse the coulumn names are county_nbr and district_nbr. The orion-ejb-jar.xml shown below should translate the cmp-field-mapping name to the persistence-name. I placed this file next to the ejb-jar.xml file in the META-INF folder.

<?xml version = '1.0' encoding = 'windows-1252'?>
<!DOCTYPE orion-ejb-jar PUBLIC "-//Evermind//DTD Enterprise JavaBeans 1.1 runtime//EN" "http://xmlns.oracle.com/ias/dtds/orion-ejb-jar.dtd">
<orion-ejb-jar>
<enterprise-beans>
<session-deployment name="SessionEJB"/>
<entity-deployment name="District" data-source="jdbc/PlutoDS" table="PLUTO.DISTRICT">
<primkey-mapping>
<cmp-field-mapping>

<cmp-field-mapping name="countyNbr" persistence-name="COUNTY_NBR" persistence-type="CHAR(2)"/>
<cmp-field-mapping name="districtNbr" persistence-name="DISTRICT_NBR" persistence-type="CHAR(2)"/>

</cmp-field-mapping>
</primkey-mapping>
<cmp-field-mapping name="countyNbr" persistence-name="COUNTY_NBR" persistence-type="CHAR(2)"/>
<cmp-field-mapping name="districtNbr" persistence-name="DISTRICT_NBR" persistence-type="CHAR(2)"/>
<cmp-field-mapping name="districtName" persistence-name="DISTRICT_NAME" persistence-type="VARCHAR2(50)"/>
<cmp-field-mapping name="districtAddr" persistence-name="DISTRICT_ADDR" persistence-type="VARCHAR2(50)"/>
<cmp-field-mapping name="districtCity" persistence-name="DISTRICT_CITY" persistence-type="VARCHAR2(50)"/>
<cmp-field-mapping name="districtState" persistence-name="DISTRICT_STATE" persistence-type="CHAR(2)"/>
<cmp-field-mapping name="districtZipCode" persistence-name="DISTRICT_ZIP_CODE" persistence-type="CHAR(9)"/>
<cmp-field-mapping name="districtAreaCode" persistence-name="DISTRICT_AREA_CODE" persistence-type="CHAR(3)"/>
<cmp-field-mapping name="districtPhNbr" persistence-name="DISTRICT_PH_NBR" persistence-type="CHAR(7)"/>
<cmp-field-mapping name="ficaId" persistence-name="FICA_ID" persistence-type="NUMBER(9)"/>
<cmp-field-mapping name="unempInsId" persistence-name="UNEMP_INS_ID" persistence-type="NUMBER(9)"/>
<cmp-field-mapping name="stateEmprId" persistence-name="STATE_EMPR_ID" persistence-type="NUMBER(5)"/>
<cmp-field-mapping name="hideShowMgmtCodeFlag" persistence-name="HIDE_SHOW_MGMT_CODE_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="validateSacsAcctFlag" persistence-name="VALIDATE_SACS_ACCT_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="altFicaEmprPct" persistence-name="ALT_FICA_EMPR_PCT" persistence-type="NUMBER(11,6)"/>
<cmp-field-mapping name="altFicaEmpPct" persistence-name="ALT_FICA_EMP_PCT" persistence-type="NUMBER(11,6)"/>
<cmp-field-mapping name="altFicaVendorNbr" persistence-name="ALT_FICA_VENDOR_NBR" persistence-type="NUMBER(5)"/>
<cmp-field-mapping name="workerCompRate" persistence-name="WORKER_COMP_RATE" persistence-type="NUMBER(11,6)"/>
<cmp-field-mapping name="workHoursPerMonth" persistence-name="WORK_HOURS_PER_MONTH" persistence-type="NUMBER(11,2)"/>
<cmp-field-mapping name="encumbNewFiscalYearFlag" persistence-name="ENCUMB_NEW_FISCAL_YEAR_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="accrualFlag" persistence-name="ACCRUAL_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="updateUserid" persistence-name="UPDATE_USERID" persistence-type="CHAR(10)"/>
<cmp-field-mapping name="updateTimestamp" persistence-name="UPDATE_TIMESTAMP" persistence-type="DATE"/>
<cmp-field-mapping name="taxpayerIdNbr" persistence-name="TAXPAYER_ID_NBR" persistence-type="NUMBER(9)"/>
<cmp-field-mapping name="contact1099PhAreaCode" persistence-name="CONTACT_1099_PH_AREA_CODE" persistence-type="CHAR(3)"/>
<cmp-field-mapping name="contact1099PhNbr" persistence-name="CONTACT_1099_PH_NBR" persistence-type="CHAR(7)"/>
<cmp-field-mapping name="contactW2PhAreaCode" persistence-name="CONTACT_W2_PH_AREA_CODE" persistence-type="CHAR(3)"/>
<cmp-field-mapping name="contactW2PhNbr" persistence-name="CONTACT_W2_PH_NBR" persistence-type="CHAR(7)"/>
<cmp-field-mapping name="useDistrict1099TinFlag" persistence-name="USE_DISTRICT_1099_TIN_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="useDistrictW2TinFlag" persistence-name="USE_DISTRICT_W2_TIN_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="finTrnsfrApprovalReqdFlag" persistence-name="FIN_TRNSFR_APPROVAL_REQD_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="budResApprovalReqdFlag" persistence-name="BUD_RES_APPROVAL_REQD_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="reportEmprPaidFlag" persistence-name="REPORT_EMPR_PAID_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="reportLeaveBalanceFlag" persistence-name="REPORT_LEAVE_BALANCE_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="apBeginAccrualOption" persistence-name="AP_BEGIN_ACCRUAL_OPTION" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="validateFund01Flag" persistence-name="VALIDATE_FUND_01_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="sacsDistrictType" persistence-name="SACS_DISTRICT_TYPE" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="logActivityFlag" persistence-name="LOG_ACTIVITY_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="payRateDecimalFlag" persistence-name="PAY_RATE_DECIMAL_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="hwSpouseLinkAllowedFlag" persistence-name="HW_SPOUSE_LINK_ALLOWED_FLAG" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="depreciationMethod" persistence-name="DEPRECIATION_METHOD" persistence-type="CHAR(1)"/>
<cmp-field-mapping name="districtPhNbrExt" persistence-name="DISTRICT_PH_NBR_EXT" persistence-type="CHAR(5)"/>
<cmp-field-mapping name="contact1099PhNbrExt" persistence-name="CONTACT_1099_PH_NBR_EXT" persistence-type="CHAR(5)"/>
<cmp-field-mapping name="contactW2PhNbrExt" persistence-name="CONTACT_W2_PH_NBR_EXT" persistence-type="CHAR(5)"/>
<cmp-field-mapping name="sdiAccountNbr" persistence-name="SDI_ACCOUNT_NBR" persistence-type="CHAR(8)"/>
</entity-deployment>
</enterprise-beans>
</orion-ejb-jar>

[schmidts]

Here we are. What's JBoss got to do with orion-ejb-jar.xml? Nothing!

What you need is jbosscmp-jdbc.xml for customizing the database mapping.

[bbbrotg]

As it turns out JBoss ignores the orion-ejb-jar.xml that is created by JDeveloper. What I had to do was use the information in orion-ejb-jar.xml to create a jbosscmp-jdbc.xml from scratch. The container was then able to send a query to the Oracle database with the correct column names.