Oracle 8i/jboss 2.4.4 findByPrimaryKey problem

[hleblanc]

I'm experiencing some very weird behavior from an application that was previously working. I'm running jboss 2.4.4, Oracle 8.1.7, RedHat Linux (7.3? not dead sure of version, but was current as of last spring).
Everything was working fine as of May, but due to political considerations, the rollout of the application was delayed till now. I popped up the application prior to database conversion, and suddenly my entity beans can't be retrieved by primary key from the database!
Here are some specifics. In this example, I'm trying to retrieve an entity bean, Company, for updating. But the jboss-generated findByPrimaryKey method is throwing the following error:
[14:48:17,037,JAWSPersistenceManager] Exists command executing: SELECT COUNT(*) FROM Company WHERE company=?
[14:48:17,076,JAWSPersistenceManager] Set parameter: idx=1, jdbcType=INTEGER, value=37855
[14:48:17,105,JAWSPersistenceManager] java.sql.SQLException: ORA-00942: table or view does not exist

[14:48:17,105,JAWSPersistenceManager] java.sql.SQLException: ORA-00942: table or view does not exist

[14:48:17,116,EmbeddedCatalinaServiceSX] StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exc
eption
javax.servlet.ServletException: InvocationTargetException: null
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:457)
at org.apache.jsp.CommitCompany$jsp._jspService(CommitCompany$jsp.java:119)
... (rest of stacktrace omitted to conserve space)


This error occurs, despite the fact that the Company bean (and all the other beans for that matter) seem to load without error. Here are some log excerpts from restarting jboss:

[14:43:28,572,ConfigurationService] PoolName set to TestDB in DefaultDomain:service=XADataSource,name=TestDB
[14:43:28,637,ConfigurationService] DataSourceClass set to org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl in D
efaultDomain:service=XADataSource,name=TestDB
[14:43:28,643,ConfigurationService] URL set to jdbc:oracle:thin:@localhost:1521:TestDB in DefaultDomain:service
=XADataSource,name=TestDB
[14:43:28,648,ConfigurationService] JDBCUser set to DEKWEB in DefaultDomain:service=XADataSource,name=TestDB
[14:43:28,652,ConfigurationService] Password set to NoneOfYourBeesWax in DefaultDomain:service=XADataSource,name=Test
...
[14:43:29,200,TestDB] Initializing
[14:43:29,200,TestDB] Initialized
...
[14:43:41,564,ContainerFactory] Company: Verified.
...
[14:43:43,532,ContainerFactory] Deploying Company
[14:43:43,533,JRMPContainerInvoker] Container Invoker RMI Port='4444'
[14:43:43,533,JRMPContainerInvoker] Container Invoker Client SocketFactory='Default'
[14:43:43,533,JRMPContainerInvoker] Container Invoker Server SocketFactory='Default'
[14:43:43,533,JRMPContainerInvoker] Container Invoker Server SocketAddr='Default'
[14:43:43,533,JRMPContainerInvoker] Container Invoker Server sslDomain='Default'
[14:43:43,534,JRMPContainerInvoker] Container Invoker Optimize='true'
[14:43:43,534,EntityInstancePool] config - MaximumSize=100, strictMaximumSize=false, feederPolicy=null
...

As you can see, everything seems to load normally; there are no errors or warnings in the log.

The relevant portion of standardjaws.xml:
java:/TestDB
<type-mapping>Oracle8</type-mapping>
true
(I just turned debugging on earlier today; previously was set to false).

The relevant portions of jboss.jcml:

oracle.jdbc.driver.OracleDriver,org.hsqldb.jdbcDriver

...

TestDB
org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
jdbc:oracle:thin:@localhost:1521:TestDB
DEKWEB
NoneOFYourBeesWax

(of course, the real password is properly set in the actual file, but I omit it here for security reasons.)

The relevant portion of ejb-jar.xml:

This represents the company, in its most generic form. A company can have one or more styles, which will indirectly put it in the appropriate subclass. For example, if you assign the Trucker style to a company, you can then retrieve a Trucker object with subclasses Company. see CompanyStyle see Style see Area see Person see Address see Contact author HARRY
<ejb-name>Company</ejb-name>
com.dek.entity.CompanyHome
com.dek.entity.CompanyRemote
<ejb-class>com.dek.entity.Company</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
<primkey-field>company</primkey-field>
False
<!-- company fields -->
<cmp-field><field-name>company</field-name></cmp-field>
<cmp-field><field-name>parentCompany</field-name></cmp-field>
<cmp-field><field-name>name</field-name></cmp-field>
<cmp-field><field-name>notes</field-name></cmp-field>
<cmp-field><field-name>active</field-name></cmp-field>



I'm omitting the portions of jboss.xml, etc, because the jndi lookup seems to work fine -- it finds the home interface, but blows up fetching the remote by primary key.

Let me stress once again that this was all working before, and I haven't changed any of the code or descriptor files in the interim. As you can imagine, this is a pretty rude surprise. Needless to say, there is a DEKWEB user/schema, with a Company table as described, and a straight jdbc connection using the same username/password works fine (my value objects read the data from the database using this account).

I'm completely stumped here, and would deeply appreciate some ideas on where to start tracking down this problem. I include the Company example, but I've tested other parts of the application, and all beans fail the same way in the same place, so I'm confident it's some sort of configuration/driver issue. Not sure of the Oracle jdbc driver version, but it was the latest and greatest as of May, and worked fine.

You can email me directly at [e-mail], or reply to this post in the forum.

Thanks,

Harry

[joelvogt]

hmmm. Well, if you drop the Company table, and restart jboss, does it re-create the table for you?

[hleblanc]

I was loath to drop the Company table, it being the parent for numerous foreign keys, so I tried your experiment with a less central table, one called ContactLog. It was recreated just fine. Here's the relevant portion of the log when restarting jboss:

[11:06:04,869,JAWSPersistenceManager] Load SQL: SELECT ContactLog.contactLog,ContactLog.contact,ContactLog.truc
kload,ContactLog.notes,ContactLog.datestamp,ContactLog.timestamp FROM ContactLog WHERE contactLog=?
[11:06:04,869,JAWSPersistenceManager] Load SQL: SELECT ContactLog.contactLog,ContactLog.contact,ContactLog.truc
kload,ContactLog.notes,ContactLog.datestamp,ContactLog.timestamp FROM ContactLog WHERE contactLog=?
[11:06:04,879,JAWSPersistenceManager] Init command executing: CREATE TABLE ContactLog (contact NUMBER(10),truck
load NUMBER(10),notes VARCHAR2(255),contactLog NUMBER(10),datestamp DATE,timestamp DATE)
[11:06:04,916,JAWSPersistenceManager] Rows affected = 0
[11:06:04,916,JAWSPersistenceManager] Table ContactLog created
[11:06:04,917,JAWSPersistenceManager] Created table 'ContactLog' successfully.
[11:06:04,917,JAWSPersistenceManager] Primary key of table 'ContactLog' is 'contactLog'

...and the table shows up in Oracle's DBA Studio just fine.

I've tried creating another schema to switch to that, but it also persists in the original problem. Another bizarre aspect to this is that the same connection object is used for some brute-force querying (to build menus and lists), and that code works just fine. Here's a code snippet:

private void initConnection()
{
try
{
DataSource source = (DataSource) this.getContext().lookup("java:/TestDB");
String username="DEKWEB1", password="NoneOfYourBeesWax";
connection = source.getConnection(username,password);
}
catch (NamingException e)
{
connection = null;
}
catch (SQLException e)
{
connection = null;
}
}


...subsequent queries through this connection work just fine. (The newly copied schema, DEKWEB1, is in the current version of the code, but it also worked in the original schema of DEKWEB.) This would seem to indicate that the jdbc connectivity, driver version, etc., are all fine.

This is baffling as all get-out. Why would the connection object work when I directly create result sets, but not in jboss's generated findByPrimaryKey? It's the same connection, isn't it?

[hleblanc]

FYI, I upgraded to 2.4.9, and the problem went away. I still don't have any idea what was causing it, but I guess I'll have to live with that.