Re: BYTEA - issue

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.