Обсуждение: BYTEA - issue

Hello all,
We recently migrated one of our products from Oracle to EnterpriseDB's
Advanced Server.  The Oracle database has tables with BLOB data type. One
example is a column where we store html templates. The product has a
feature to send e-mails and the templates are stored as html files in the
blob column, one html template per record in the table. We do a SELECT
against the column and it works fine (in Oracle). During migration, the
migration studio ended up creating BYTEA columns on the PostgreSQL side (I
am not sure if that is the correct mapping). Anyway now the application,
when it tries to query those columns, throws errors.
com.edb.util.PSQLException: Bad long: Hello
        at com.edb.jdbc2.AbstractJdbc2ResultSet.toLong
(AbstractJdbc2ResultSet.java:2772)
        at com.edb.jdbc2.AbstractJdbc2ResultSet.getLong
(AbstractJdbc2ResultSet.java:2001)
        at com.edb.jdbc3.Jdbc3ResultSet.getBlob
(Jdbc3ResultSet.java:53)
        at com.edb.jdbc2.AbstractJdbc2ResultSet.getBlob
(AbstractJdbc2ResultSet.java:344)
        at com.p6spy.engine.spy.P6ResultSet.getBlob
(P6ResultSet.java:658)
        at weblogic.jdbc.wrapper.ResultSet.getBlob(ResultSet.java:279)

By the way, the html template is directly stored in the column (as it was
in Oracle). To change that would mean making a lot of changes in quite a
few tables/columns. We tried changing the column type to TEXT and still
got errors. Any suggestions?
This is what the data content looks like
select cnt from cmntmpmst limit 1;
<font face="Arial">....
v</font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font>

Database Product -> EnterpriseDB
Database Product Version -> 8.3.0.112
Database Driver -> Postgres Plus Advanced Server Native Driver


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

On 2010-06-04, Jayadevan M <Jayadevan.Maymala@ibsplc.com> wrote:
> Hello all,
> We recently migrated one of our products from Oracle to EnterpriseDB's
> Advanced Server.

I bet Oracle is pleased to retain you as a customer :)

> The Oracle database has tables with BLOB data type. One
> example is a column where we store html templates. The product has a
> feature to send e-mails and the templates are stored as html files in the
> blob column, one html template per record in the table. We do a SELECT
> against the column and it works fine (in Oracle). During migration, the
> migration studio ended up creating BYTEA columns on the PostgreSQL side (I
> am not sure if that is the correct mapping).

if the columns don't containd the byte 0 or other sequences thar are invalid
text the "text" type might have been better.

Anyway now the application,
> when it tries to query those columns, throws errors.
> com.edb.util.PSQLException: Bad long: Hello
>         at com.edb.jdbc2.AbstractJdbc2ResultSet.toLong
> (AbstractJdbc2ResultSet.java:2772)
>         at com.edb.jdbc2.AbstractJdbc2ResultSet.getLong
> (AbstractJdbc2ResultSet.java:2001)
>         at com.edb.jdbc3.Jdbc3ResultSet.getBlob
> (Jdbc3ResultSet.java:53)
>         at com.edb.jdbc2.AbstractJdbc2ResultSet.getBlob
> (AbstractJdbc2ResultSet.java:344)
>         at com.p6spy.engine.spy.P6ResultSet.getBlob
> (P6ResultSet.java:658)
>         at weblogic.jdbc.wrapper.ResultSet.getBlob(ResultSet.java:279)

looks like a JDBC problem.

> By the way, the html template is directly stored in the column (as it was
> in Oracle). To change that would mean making a lot of changes in quite a
> few tables/columns. We tried changing the column type to TEXT and still
> got errors. Any suggestions?

try the jdbc mailing list.

> This is what the data content looks like
> select cnt from cmntmpmst limit 1;
><font face="Arial">....
> v</font></font></font></font></font></font></font></font></font></font></font></font></font></font></font></font>

I hope that's not supposed to be XHTML.