Thread: how to read a long text from a text field?

Hi there,

I have a query unions two subqueries which each returns a row with an id and
a long text and "order by id asc". The row with a smaller id follows the
other row with a larger id. However, every time when I tried to read the
text of the smaller id, it was never read and the text of the larger id was
returned. The resultset pointer seemed to stay at the first row with larger
id regardless how many times rs.next() was executed (three times of
execution but not errors occurred). I figured out that the problem was with
reading the long text, or the query could not handle that. The query was
executed successfully in psql environment. My question is: how to read a
text field (particularly with long value), can I treat it as a clob but
PostgreSQL does not support clob?

Thanks.
Daniel Huang

On Tue, 9 Dec 2003, Daniel Huang wrote:

> Hi there,
>
> I have a query unions two subqueries which each returns a row with an id and
> a long text and "order by id asc". The row with a smaller id follows the
> other row with a larger id. However, every time when I tried to read the
> text of the smaller id, it was never read and the text of the larger id was
> returned. The resultset pointer seemed to stay at the first row with larger
> id regardless how many times rs.next() was executed (three times of
> execution but not errors occurred). I figured out that the problem was with
> reading the long text, or the query could not handle that. The query was
> executed successfully in psql environment. My question is: how to read a
> text field (particularly with long value), can I treat it as a clob but
> PostgreSQL does not support clob?
>

I find it hard to believe the JDBC driver is at fault here and suspect
your query is not returning exactly what you expect it to.  Could you post
your table definitions and the query in question so we could see them?

Kris Jurka

It could be something wrong with my testing driver. It works now. Thanks.

-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Tuesday, December 09, 2003 12:23 AM
To: Daniel Huang
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] how to read a long text from a text field?




On Tue, 9 Dec 2003, Daniel Huang wrote:

> Hi there,
>
> I have a query unions two subqueries which each returns a row with an id
and
> a long text and "order by id asc". The row with a smaller id follows the
> other row with a larger id. However, every time when I tried to read the
> text of the smaller id, it was never read and the text of the larger id
was
> returned. The resultset pointer seemed to stay at the first row with
larger
> id regardless how many times rs.next() was executed (three times of
> execution but not errors occurred). I figured out that the problem was
with
> reading the long text, or the query could not handle that. The query was
> executed successfully in psql environment. My question is: how to read a
> text field (particularly with long value), can I treat it as a clob but
> PostgreSQL does not support clob?
>

I find it hard to believe the JDBC driver is at fault here and suspect
your query is not returning exactly what you expect it to.  Could you post
your table definitions and the query in question so we could see them?

Kris Jurka