6 Replies Latest reply on Jun 23, 2004 3:53 PM by jae

    Can't fetch CMP Oracle CLOB fields

    Collin Novice

      Hi,

      I'm almost done porting over our application to Oracle9i from mysql/sqlserver and have run into a bit of a problem.

      Columns that were stored as text before are now stored as CLOB in Oracle9i and for some reason I can't get the CMP engine to fetch these fields -- they always return null for some reason. If I change the column type in Oracle9i to VARCHAR2, it works as expected.

      I've changed the data source type in the jbosscmp-jdbc.xml to Oracle9i.

      Any ideas?

      I'm using JBoss 3.2.3. on Windows XP Pro.

        • 1. Re: Can't fetch CMP Oracle CLOB fields
          Carlos Ortega Newbie

          Hi,

          I think that the problem is EJB persistence engine. CLOB/BLOB work requires especial code (using especific driver classes) and I think that the EJB engine of JBOSS not support these field types. It's normal that return null when you accesing directly to BLOB/CLOB field because the implementation of these field in database is a reference to a file in Oracle (or other DBMS) filesystem.

          Regards

          • 2. Re: Can't fetch CMP Oracle CLOB fields
            Collin Novice

            Thanks for the info. Is there a recommended workaround for this? I need potentially large storage capacity for an entity field, but would like it returned as a java.lang.String.

            • 3. Re: Can't fetch CMP Oracle CLOB fields
              Carlos Ortega Newbie

              Sorry, but I don't know if other persistence system (hybernate, JDO, EJB 3.0) implements these functionallity. I'll recommend you that you implements this. You can retrieve source code in Oracle or if you have installed Oracle in doc's/examples¿? directory you can find this.

              Regards

              • 4. Re: Can't fetch CMP Oracle CLOB fields
                jae Master

                you should be able to do this. we are using oracle w/ clob fields here. i know in hibernate, you need to use a work around for this, but there should be no issue using cmp.

                here is a brief snippet of what our impl class looks like, complete w/ it's xdoclet tags. you should make sure you are using the latest oracle drivers as well (i believe the lastest is the ojdbc14.jar)

                 /**
                 * Returns the data
                 *
                 * @return the data
                 *
                 * @ejb.interface-method view-type="local"
                 * @ejb.persistent-field
                 * @ejb.persistence column-name="DATA"
                 * @jboss.jdbc-type CLOB
                 * @jboss.sql-type CLOB
                 */
                 public abstract java.lang.String getData();
                
                 /**
                 * Sets the data
                 *
                 * @param data the new data value
                 * @ejb.interface-method view-type="local"
                 */
                 public abstract void setData(java.lang.String data);
                


                • 5. Re: Can't fetch CMP Oracle CLOB fields
                  Collin Novice

                  jae77 -- thanks, that worked, well, perfectly :-) I need to make sure it's going to work with my mysql text and sqlserver ntext column types, too, but I'm just really glad to see it working so far in Oracle.

                  Thanks to all...

                  Collin

                  • 6. Re: Can't fetch CMP Oracle CLOB fields
                    jae Master

                    you will have to change the sql-type and jdbc-type xdoclet attributes depending upon what database you use, but otherwise there should be no issues.