openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Joe Weinstein (JIRA)" <j...@apache.org>
Subject [jira] Updated: (OPENJPA-982) ERROR PERFORMING QUERIES ON ORACLE TABLES WITH CLOB COLUMN
Date Mon, 30 Mar 2009 18:35:50 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-982?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Joe Weinstein updated OPENJPA-982:
----------------------------------


After review and consultation, here is a simpler fix, changing only the
OracleDictionary, adding the method as:

public String getPlaceholderValueString(Column col) {
      if (col.getType() == Types.CLOB)
          return "TO_CLOB('')";
      else
          return super.getPlaceholderValueString(col);
  }
 


> ERROR PERFORMING QUERIES ON ORACLE TABLES WITH CLOB COLUMN
> ----------------------------------------------------------
>
>                 Key: OPENJPA-982
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-982
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: sql
>            Reporter: Joe Weinstein
>
>  I created a simple project with two persistent classes that extends from
>  the same abstract class (inheritance strategy='new-table').
>  ii) I run the enhancer and then the mapping tool (target db is oracle). The
>  schema is then created accordingly: a table called INSTANCEIMPL1 and a table
>  called INSTANCEIMPL2.
>  The column FIELDIMPL2 type in the table INSTANCEIMPL2 is CLOB.
>  In the MAPPING table, the field _fieldImpl2 corresponds to FIELDIMPL2 of
>  type CLOB:
>  <field name='_fieldImpl2'><column name='FIELDIMPL2' jdbc-type='clob'/>
>  So far all works as expected.
>  iii) Then I seeded the db with some dummy data and I tried to perform a
>  query as follow:
>  Query newQuery = pm.newQuery(AbstractInstance.class);
>  newQuery.execute();
>  iv) Here I got the following exception:
>  Exception in thread 'main' <1.0.0-SNAPSHOT-SNAPSHOT nonfatal store error>
>  kodo.jdo.DataStoreException: ORA-01790: expression must have same datatype
>  as corresponding expression
>  {prepstmnt 17824568 SELECT 0, t0.ID, t0.TYP, t0.VERSN, t0.STATUS,
>  t0.FIELDIMPL1, '' FROM INSTANCEIMPL1 t0 UNION ALL SELECT 1, t0.ID, t0.TYP,
>  t0.VERSN, t0.STATUS, '', t0.FIELDIMPL2 FROM INSTANCEIMPL2 t0 [reused=0]}
>  [code=1790, state=42000]
>  at
>  org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3
>  784)
>  at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:97)
>   ...
>  This query fails because t0.FIELDIMPL2 is CLOB whereas '' (the last column
>  before 'FROM' in the first SELECT) is implicitly VARCHAR2.
>  As I'm querying the super class, the persistent layer needs to gather column
>  from its subclasses (which are mapped in their own tables). To do this, Kodo
>  (or openJPA under the hood) has to 'merge' the two tables through an sql
>  union. A constraint with unions is that the select on the left and the one
>  on the right must return the same number of column with compatible type.
>  Now, the field called FIELDIMPL1 (of type VARCHAR2) is only defined in
>  INSTANCEIMPL1. So the select statement on INSTANCEIMPL2 is filled with a
>  constant column ''. So far so good.
>  The problem is with FIELDIMPL1 (of type CLOB) which is combined with ''.
>  Matter of fact CLOB are objects and not strings.
> This works:
>  SELECT 0, t0.ID, t0.TYP, t0.VERSN, t0.STATUS, t0.FIELDIMPL1, TO_CLOB('')
>  FROM INSTANCEIMPL1 t0
>  UNION ALL
>  SELECT 1, t0.ID, t0.TYP, t0.VERSN, t0.STATUS, '', t0.FIELDIMPL2 FROM
>  INSTANCEIMPL2 t0
>    I suggest (and have tested) we supply the Oracle-specific conversion
> function, TO_CLOB(''). This requires the Oracle-specific subclass of
> DBDictionary to override this generic method to provide the Oracle SQL
> where necessary.
>    Attached are the two files changed and tested as I would recommend.
> These are at the 645589 revision, plus the fix.
> The diffs are:
> DBDictionary.java: (three private constants made protected
>                    so OracleDictionary can use them in the
>                    same way DBDictionary does)
> 152c152
> <     private static final String ZERO_DATE_STR =
> ---
> >     protected static final String ZERO_DATE_STR =
> 154,155c154,155
> <     private static final String ZERO_TIME_STR = "'" + new Time(0) + "'";
> <     private static final String ZERO_TIMESTAMP_STR =
> ---
> >     protected static final String ZERO_TIME_STR = "'" + new Time(0) + "'";
> >     protected static final String ZERO_TIMESTAMP_STR =
> OracleDictionary.java: (A straight copy of the DBDictionary method except
>                         for the CLOB case)
> 1103a1104,1138
> >
> >     /**
> >      * Return a SQL string to act as a placeholder for the given column.
> >      */
> >     public String getPlaceholderValueString(Column col) {
> >         switch (col.getType()) {
> >             case Types.BIGINT:
> >             case Types.BIT:
> >             case Types.INTEGER:
> >             case Types.NUMERIC:
> >             case Types.SMALLINT:
> >             case Types.TINYINT:
> >                 return "0";
> >             case Types.CHAR:
> >                 return (storeCharsAsNumbers) ? "0" : "' '";
> >             case Types.LONGVARCHAR:
> >             case Types.VARCHAR:
> >                 return "''";
> >             case Types.CLOB:
> >                 return "TO_CLOB('')";  // Oracle-specific
> >             case Types.DATE:
> >                 return ZERO_DATE_STR;
> >             case Types.DECIMAL:
> >             case Types.DOUBLE:
> >             case Types.FLOAT:
> >             case Types.REAL:
> >                 return "0.0";
> >             case Types.TIME:
> >                 return ZERO_TIME_STR;
> >             case Types.TIMESTAMP:
> >                 return ZERO_TIMESTAMP_STR;
> >             default:
> >                 return "NULL";
> >         }
> >     }
> Thanks,
> Joe

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message