EntityBean in sync with underlying table data

[sat.ena]

Looking for some help to figure-out this issue:

Description:

We have an entitybean which has a custom method that uses datasource to execute a select query to retrieve a set of values from the table. There is an another independent oracle procedure which updates this table by creating new rows or updating existing rows.

Now the issue, after the procedure does its job and when we try to retrieve those records from the entitybean, we don't get any.

We are now restarting Jboss server to get the reflected in entitybeans. Please help me understand the issue and is there any way we can eliminate server restart

Using: JBOSS 4.2.2 GA, Oracle 10g, <xa-datasource>

Many thanks in advance for your help with this.

[wdfink]

What version of JEE do you use? EJB2 or EJB3?

I assume EJB2.x, in this case it is very difficult to keep the Beans in sync.

How you access your entity-beans? via finder or relation?

[sat.ena]

Sorry for getting back late.

I was wrong above, it is not an entity bean. It is actually a simple session bean.

[wdfink]

If you use a SLSB with a direct JDBC access to the database there should no cache in between and you should get the committed data from the database (nevertheless whether you use direct connection or connection pool via JNDI lookup)

[sat.ena]

Thats what I am too wondering about. Below is the code-snippet of the Bean class.

public class GetDataBean implements javax.ejb.SessionBean{

public java.util.Properties getDataSet(java.lang.String id) {
       
        Properties props = new Properties();
        java.sql.Connection c = null;
        java.sql.Statement s = null;
        java.sql.ResultSet r = null;
        try {
            javax.sql.DataSource ds = null;
            javax.naming.Context ctx = new javax.naming.InitialContext();
            javax.naming.Context envCtx = (javax.naming.Context) ctx.lookup("java:comp/env");
            String tableName = (String)envCtx.lookup("tablename");
            String dataSourceName = (String)envCtx.lookup("datasource");
           
            ds = (javax.sql.DataSource) envCtx.lookup(dataSourceName);
            c = ds.getConnection();
            s = c.createStatement();
            r = s.executeQuery("select * from "+tableName+" where id='"+id+"'");
            if (!r.isBeforeFirst()) throw new Exception("error msg");
            while (r.next()) {
                try {
                    java.sql.Clob clob = r.getClob("value");
                    if (!r.wasNull()) {
                        String value = clob.getSubString((long)1, (int)clob.length());
                        props.setProperty(r.getString("key"), clob.getSubString((long)1, (int)clob.length()));
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {r.close();} catch (Exception e) {}
            try {s.close();} catch (Exception e) {}
            try {c.close();} catch (Exception e) {}
        }
        return props;
    }

}

snippet from ejb-jar.xml:

  <env-entry>

  <description>table with which to pull data from</description>

  <env-entry-name>tablename</env-entry-name>

  <env-entry-type>java.lang.String</env-entry-type>

  <env-entry-value>pr.dataStore</env-entry-value>

  </env-entry>

- <env-entry>

  <description>database connection identifier</description>

  <env-entry-name>datasource</env-entry-name>

  <env-entry-type>java.lang.String</env-entry-type>

  <env-entry-value>jdbc/repository</env-entry-value>

  </env-entry>

jdbc/repository is mapped to its respective datasource JNDI.

Is there any thing to do with Jboss 4.2.2 GA settings to resolve this? Many thanks for your help with this.

[wdfink]

How do you set the transaction for this bean?

What Tx isolation level you have?

I did not see a problem from source side. The connecion is taken from pool, all is closed correct.

A plain SQL should not cache anything.