Thread: Bug in 7.4_213 driver: returns VARCHAR instead of LONGVARCHAR for text types

The JDBC version 7.4_213 driver has the following bug

For database SQL type:
text    (that is variable unlimited length)

The JDBC driver returns "java.sql.VARCHAR" as
the column type. (via database meta data).

This is wrong. The JDBC driver SHOULD return
java.sql.LONGVARCHAR (since the text type is
of unlimited length).

Please fix this because it is messing up my
relational mapping code.

Best regards,

--j



__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

On Tue, 27 Jul 2004, j.random.programmer wrote:

> The JDBC version 7.4_213 driver has the following bug
>
> For database SQL type:
> text    (that is variable unlimited length)
>
> The JDBC driver returns "java.sql.VARCHAR" as
> the column type. (via database meta data).
>
> This is wrong. The JDBC driver SHOULD return
> java.sql.LONGVARCHAR (since the text type is
> of unlimited length).
>

I'm not sure this is clearly wrong, but you do make a convincing argument.
My concern is backwards compatibility with existing applications that
expect text as VARCHAR.  When I tried the change in the driver, there were
4 failures and 2 errors in the drivers regression test.  Some code didn't
handle LONGVARCHAR at all and others expected text to be VARCHAR.

For example consider CallableStatements:

CallableStatement call = con.prepareCall ("{ ? = upper(?) }");
call.setString(2, "foo");
call.registerOutParameter(1, Types.VARCHAR);
call.execute();
String result = call.getString(1); // Should be "FOO"

With the proposed change this will fail because the output type of upper
is text and it's no longer mapped to Types.VARCHAR, but LONGVARCHAR, so
you'll get this exception:

 A CallableStatement Function was executed and the return was of type
(java.sql.Types=-1) however type=java.sql.Types=12 was registered.


Kris Jurka

On Thu, 29 Jul 2004, Kris Jurka wrote:

> On Tue, 27 Jul 2004, j.random.programmer wrote:
>
> > The JDBC version 7.4_213 driver has the following bug
> >
> > For database SQL type:
> > text    (that is variable unlimited length)
> >
> > The JDBC driver returns "java.sql.VARCHAR" as
> > the column type. (via database meta data).
> >
> > This is wrong. The JDBC driver SHOULD return
> > java.sql.LONGVARCHAR (since the text type is
> > of unlimited length).
> >
>
> I'm not sure this is clearly wrong, but you do make a convincing argument.

I'm not so sure anymore.  While text can be used for storing large values
it often isn't.  Further returning LONGVARCHAR could be a sign for the
caller to use something like ResultSet.getCharacterStream instead of a
simple getString.

Kris Jurka

Kris Jurka wrote:
 > I'm not so sure anymore.  While text can be used for storing large values
 > it often isn't.  Further returning LONGVARCHAR could be a sign for the
 > caller to use something like ResultSet.getCharacterStream instead of a
 > simple getString.

I would agree.  In our applications we exclusively use text everywhere
we use varchar on other databases.  I am quite sure that a change like
this would break peoples existing apps.  I also agree that LONGVARCHAR
would imply that you should use the getXXXStream methods which I would
say is rarely the correct thing to do.  In my opinion text is just a
better version of varchar (without many of the limitations imposed on
varchar in other database vendor implementations).

--Barry

Kris:

Firstly, let me say that I, as a end-user, really
appreciate
the time you and others take to reply to posts and
work
on the JDBC driver.

postgresql/jdbc has pretty much become my reference
system
these days and it's no small part due to the sense of
community
one feels in these parts.

> > The JDBC driver returns "java.sql.VARCHAR" as for
"text" but
> > SHOULD return java.sql.LONGVARCHAR (since the text
type is of
> > unlimited length).
>
> My concern is backwards compatibility with existing
> applications that expect text as VARCHAR.  When I
tried the
> change in the driver, there were 4 failures and 2
errors in
> the drivers regression test.  Some code didn't
handle
> LONGVARCHAR at all and others expected text to be
VARCHAR.

But it's always better to do the right thing, isn't it
?

Both the intuitive expectation and the JDBC spec imply
that LONGVARCHAR is the right thing in this case.
Sure, I've
worked around this particular bug in one case (I check
to
see if the max column size = -1 and if so, internally
flip
the type to LONGVARCHAR and return a
reader/inputstream if
the user wants and also ignore setting up size
validation).

> For example consider CallableStatements:
> ...
> A CallableStatement Function was executed and the
return was
> of type (java.sql.Types=-1) however
type=java.sql.Types=12 was
> registered.

I don't understand the full implications of your
CallableStatement (CallableStatement is the one part
of
JDBC I haven't had reason to use).

Is this a unfixable problem though ? Also, isn't it
good that
you are getting some problems in the regression tests,
that means
there are some hidden bugs that just got exposed and
might as
well now get fixed.

> I'm not so sure anymore.  While text can be used for
storing
> large values it often isn't.  Further returning
LONGVARCHAR
> could be a sign for the caller to use something like
> ResultSet.getCharacterStream instead of a simple
getString.

The whole point of using streams is that large strings
may not fit in memory [for multiple users, all those
large strings definitely _won't_ fit in memory]. Of
course
this may be moot in the _current_ driver
implementation
(which for all I know might horking the whole string
from
the database into memory and then returing a
StringReader
or whatever).

But the right thing for large amounts of data again is
streams,
not storing that data in memory.

> In our applications we exclusively use text
everywhere we use
> varchar on other databases.  I am quite sure that a
change
> like this would break peoples existing apps.  I also
agree
> that LONGVARCHAR would imply that you should use the
> getXXXStream methods which I would say is rarely the
correct
> thing to do.

Why are the getXXXStream methods "rarely the correct
thing" ?
Ignoring current implementation artifacts (if any),
they
are almost always the _right_ thing for large amounts
of
data.

Or am I missing something here ?

Best regards,

--j






__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail

j.random.programmer wrote:

> Why are the getXXXStream methods "rarely the correct
> thing" ?
> Ignoring current implementation artifacts (if any),
> they
> are almost always the _right_ thing for large amounts
> of
> data.

The point is that it *is* all about current implementation artifacts.
There's no way to stream a text value from the server on demand using
the current protocol. So if you use getXXXStream() with the current
driver, you always end up wrapping data that's completely on-heap anyway
with an unnecessary stream interface. Note that this is more of a
client/server protocol issue than a driver issue (and it's not obvious
how to solve it at the protocol level anyway).

As Barry said, returning LONGVARCHAR implies the preferred access method
is via getXXXStream() (see the jdbc type mapping tables), but those
aren't the best methods for accessing text columns in our current
implementation. And the metadata should reflect the characteristics of
the driver implementation actually used, surely..

-O