Search Postgresql Archives

Re: How to convert ByteA to Large Objects

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> > 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."






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux