> > Thank you. We are working on an Oracle to PostgreSQL migration 
project.
> > BLOB columns got converted to BYTEA in PostgreSQL and we ran into 
problems.
> > We used this to convert the data type to OID. Thank you.
> 
> you probably should detail the problems you ran into.   large objects 
> normally aren't used unless you need to store over 1GB objects in the 
> database, and at least speaking for myself, I'd rather not have objects 
> that large in my database at all, I'd as soon use files for things that 
big.
> 
Well, we are storing scanned images of boarding passes. We are using 
Hibernate and the insert statement generated threw this error. 
"Caused by: org.hibernate.exception.SQLGrammarException: could not insert:
        at 
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at 
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at 
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2202)
        at 
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2595)
        at 
org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
        at 
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
        at 
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
        at 
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at 
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
        at 
com.ibsplc.xibase.server.framework.persistence.hibernate.HibernateJTATxWrapper.commit(HibernateJTATxWrapper.java:93)
        ... 51 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "docdta" is of 
type bytea but expression is of type oid
  Hint: You will need to rewrite or cast the expression."

If we try rewriting, that would mean code changes in a few other places. 
We changed the data type and now 'some' data has been inserted. Once we 
fix the retrieval screen, we will know it is getting processed correctly. 
select data from pg_largeobject where loid=<id> gave us a couple of 
hundred records, so we assume data has been inserted.
Switching to file storage will mean quite a bit of changes at the code 
level, and a lot of testing of the products that runs fine on Oracle now.
Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."




Reply via email to