Hi all,

I am changing the hibernate from using Oracle as datasource to Postgresql. After creating the similar database in Postgres and import the data, hibernate complains heaps of errors, like:

ERROR: function upper(bytea) does not exist
ERROR: operator does not exist: character varying = bytea
java.math.BigInteger cannot be cast to java.math.BigDecimal


I am using com.springsource.org.hibernate-3.2.6.ga.jar, postgresql-8.1-415.jdbc3.jar,...
There are heaps of @namednativequery in the source, but not sure these need to be changed or not, e.g.


@NamedNativeQuery(
name="countStoreLocationsBySearchCriteria",
query="select count(L.LOC_ID) as locCount from STORE_LOC L where (:suburb is null or UPPER(L.SUBURB_NM) = UPPER(:suburb)) " +
"AND (:country is null or UPPER(L.COUNTRY)= UPPER(:country)) " +
"AND (:state is null or UPPER(L.STATE)= UPPER(:state)) " +
"AND (:postcode is null or UPPER(L.POST_CODE) = UPPER(:postcode)) " +
"AND (:divlimit is null or L.DIV_ID = :division)" +
"AND (:stalimit is null or L.ACTIVE_FLAG = :status) " +
"AND (:storeNo is null or UPPER(L.LOC_NO) = UPPER(:storeNo)) " +
"AND (:storeName is null or UPPER(L.LOC_NM) LIKE UPPER(:storeName)) " +
"AND (:storeType is null or UPPER(L.LOC_TYPE) = UPPER(:storeType)) " +
"AND (L.DIV_ID in (:divisionList)) " +
"ORDER BY L.DIV_ID, L.STATE, L.SUBURB_NM ",
resultSetMapping="locCountResult"
)


Most of the discussion out there used hibernate mapping xml, however, Spring Roo is used to generate AspectJ files against all tables is the case in my project. I hope someone can point me to a right direction about what needs to be changed.

I have set org.postgresql.ds.PGConnectionPoolDataSource as the class for connectionPoolDataSource in applicationContext.xml and set hibernate.dialect to org.hibernate.dialect.PostgreSQLDialect. I am not sure whether the data types need to change or not when database is changed from Oracle to Postgres or not.

Not sure these information is enough to allow some of you to help me out or not, as this is my first time to post a question in a forum.

3.2.6 seems very old, maybe you should upgrade to the latest and give it a try

stliu,

Thank you very much for the suggestion, finally got it runs in newer versions:

<properties>
<spring.version>3.1.1.RELEASE</spring.version>
<aspectj.version>1.6.12</aspectj.version>
<sloc.version>1</sloc.version>
<spring-security.version>3.0.1.RELEASE</spring-security.version>
</properties>

However, it stills has problem in querying Postgresql with those @NamedNativeQuery, e.g.
Hibernate: select L.* from STORE_LOC L where (? is null or UPPER(L.SUBURB_NM) = UPPER(?)) AND (? is null or UPPER(L.COUNTRY)= UPPER(?)) AND (? is null or UPPER(L.STATE)= UPPER(?)) AND (? is null or UPPER(L.POST_CODE) = UPPER(?)) AND (? is null or L.DIV_ID = ?)AND (? is null or L.ACTIVE_FLAG = ?) AND (? is null or UPPER(L.LOC_NO) = UPPER(?)) AND (? is null or UPPER(L.LOC_NM) LIKE UPPER(?)) AND (? is null or UPPER(L.LOC_TYPE) = UPPER(?)) AND (L.DIV_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) ORDER BY L.LOC_NO
ERROR: function upper(bytea) does not exist

If I remove all upper(), it complained:
Hibernate: select count(L.LOC_ID) as locCount from STORE_LOC L where (? is null or L.SUBURB_NM = ?) AND (? is null or L.COUNTRY= ?) AND (? is null or L.STATE= ?) AND (? is null or L.POST_CODE = ?) AND (? is null or L.DIV_ID = ?)AND (? is null or L.ACTIVE_FLAG = ?) AND (? is null or L.LOC_NO = ?) AND (? is null or L.LOC_NM LIKE ?) AND (? is null or L.LOC_TYPE = ?) AND (L.DIV_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) ORDER BY L.DIV_ID, L.STATE, L.SUBURB_NM
ERROR: operator does not exist: character varying = bytea


Do I need to change the SQL? My plan is to make the application switchable between Oracle and Postgres. Sorry that my knowledge is very limited, so I don't know where the problem is. If you, or someone out there, can give me some suggestion, it would be much appreciated.