Обсуждение: text type handling

Hello pgsql-jdbc--

I am working on a project where my organization would like to migrate a load of existing Oracle databases to
PostgreSQL. There is a requirement, however, that we be able to continue to write reports against the databases using
CrystalReports / Crystal Enterprise.  This seems to be largely possible from our Linux servers through the use of your
JDBCdrivers, with the one exception that postgres text datatypes appear to Crystal as String fields of length -1, which
confusesthe software and prohibits their use.  This is particularly problematic given postgresql's proclivity for
castingthe results of concatenations, unions, etc. to text on-the-fly. 

I see a long thread related to this topic from about a year ago here:
http://archives.postgresql.org/pgsql-jdbc/2007-12/msg00145.php, but it seems to have run its course without coming to
anyspecific conclusions.  Can anyone please update me on the status of this issue?  It seems a small stumbling block,
butcould prove to be a show-stopper if we can't get around it somehow... 

Thanks,

--Ken Boss
  Minnesota DNR

On Tue, 2 Dec 2008, Ken Boss wrote:

> ... that postgres text datatypes appear to Crystal as String fields of
> length -1, which confuses the software and prohibits their use.  This is
> particularly problematic given postgresql's proclivity for casting the
> results of concatenations, unions, etc. to text on-the-fly.
>
> I see a long thread related to this topic from about a year ago here:
> http://archives.postgresql.org/pgsql-jdbc/2007-12/msg00145.php , but it
> seems to have run its course without coming to any specific conclusions.

The conclusion that Oliver and I seemed to come to was that the type
returned should not be changed, but instead the precision should be
changed from -1 to Integer.MAX_VALUE.  This change hasn't been made yet,
but that will happen before the 8.4 release.  This change won't be
backpatched to earlier driver releases for backwards compatibility
reasons.

Kris Jurka

Thanks much for your response, Kris.  I have been able to make things work for my purposes by applying the patch
suggestedby Daniel Migowski in the thread referenced below.  I tried to implement your suggestion as well, but know
onlyenough to be dangerous.  Returning Integer.MAX_VALUE from the Oid.TEXT case in the getPrecision method of
TypeInfoCache.javahad no discernible effect - I must have been coding in the wrong place.  At any rate, I will look
forwardto that fix in the next release. 

Thanks again,

--Ken

>>> On 12/2/2008 at 4:40 PM, in message
<Pine.BSO.4.64.0812021736080.1073@leary.csoft.net>, Kris Jurka
<books@ejurka.com> wrote:

>
> On Tue, 2 Dec 2008, Ken Boss wrote:
>
>> ... that postgres text datatypes appear to Crystal as String fields of
>> length -1, which confuses the software and prohibits their use.  This is
>> particularly problematic given postgresql's proclivity for casting the
>> results of concatenations, unions, etc. to text on-the-fly.
>>
>> I see a long thread related to this topic from about a year ago here:
>> http://archives.postgresql.org/pgsql-jdbc/2007-12/msg00145.php , but it
>> seems to have run its course without coming to any specific conclusions.
>
> The conclusion that Oliver and I seemed to come to was that the type
> returned should not be changed, but instead the precision should be
> changed from -1 to Integer.MAX_VALUE.  This change hasn't been made yet,
> but that will happen before the 8.4 release.  This change won't be
> backpatched to earlier driver releases for backwards compatibility
> reasons.
>
> Kris Jurka

On Wed, 3 Dec 2008, Ken Boss wrote:

> Thanks much for your response, Kris.  I have been able to make things
> work for my purposes by applying the patch suggested by Daniel Migowski
> in the thread referenced below.  I tried to implement your suggestion as
> well, but know only enough to be dangerous.  Returning Integer.MAX_VALUE
> from the Oid..TEXT case in the getPrecision method of TypeInfoCache.java
> had no discernible effect - I must have been coding in the wrong place.
> At any rate, I will look forward to that fix in the next release.
>

That's basically what I had in mind, but for a couple more types as well
(see the attached patch).  If this doesn't work we need to know that so we
can come up with something else, so your testing is appreciated.  Can you
be more clear about what Crystal Reports does when it gets confused?  Is
it possible that CR is trying to calculate a total row size in an integer
value and overflows when it sees Integer.MAX_VALUE?  If so, returning some
other large, but not huge value might work.

Kris Jurka

Daniel Migowski wrote:
> I vote for my patch, too! At least make it an option to the driver to
> behave correct, please.
>

If changing the precision doesn't work, that may be necessary, but I'd
like to understand what Crystal Reports is doing and why changing the
precision doesn't work.  No one has said anything other than "it fails"
so I'd like to be making an informed decision.

Kris Jurka

I vote for my patch, too! At least make it an option to the driver to behave correct, please.<br /><br /> With best
regards,<br/> Daniel Migowski<br /><br /><div class="moz-signature">-- <br /><pre> |¯¯|¯¯|    <b>IKOffice GmbH
  Daniel Migowski</b>|  |  |/|                            Mail: <a
href="mailto:dmigowski@ikoffice.de">dmigowski@ikoffice.de</a>| | // |  Nordstr. 10               Tel.: 0441 21 98 89
52| | \\ |  26135 Oldenburg           Fax.: 0441 21 98 89 55|__|__|\|  <a
href="http://www.ikoffice.de">http://www.ikoffice.de</a>   Mob.: 0176 22 31 20 76</pre></div> 

OK, this is the exactly the data we need.  So neither Daniel's nor my
solution are completely adequate.  It seems what we need is an option to
specify the precision value returned for unknown length items.  That
will allow you to set it to whatever value you want.

Daniel, do you have a specific case where LONGVARCHAR is needed instead
of being able to set the precision?

Kris Jurka

Ken Boss wrote:
> OK, bear with me here - I don't have access to Crystal's internals, I
> can only report empirical observations...
>
> The Crystal report designer has a feature they call the Field
> Explorer that lets you browse the fields in the tables that are
> accessible to the report, and displays what Crystal considers to be
> the field type for each.
>
> Using an unmodified 8.4dev-700 driver, both pg text and
> varchar(unlimited) types show up in the Field Explorer as types
> String[-1], and simply fail to display in the report.
>
> After applying Kris's patch (plus one more line for the Oid.TEXT
> case), the situation remains the same.  As suggested, presumably
> values approaching Integer.MAX_VALUE push things into an overflow
> situation, so Crystal reverts to the String[-1] representation.
>
> Substituting an integer < 65535 in place of Integer.MAX_VALUE in the
> patch causes pg text/varchar(unlimited) fields to show up in Crystal
> as String fields of the specified length, which will then display in
> the report.
>
> Substituting an integer > 65535 but less than Integer.MAX_VALUE (eg.,
> 1000000) yields String[65534] for those pg types, and they continue
> to display in the report (presumably up to 65334 chars, anyway).
>
> Daniel's patch (which hasn't addressed varchar(unlimited)), OTOH,
> causes pg text fields to appear in the Field Explorer as type Memo
> (which is what Crystal produces from Oracle CLOBs).  Memo fields are
> marginally easier to display in a report (they auto-wrap until their
> contents are fully output, where that has to be arranged in a
> separate step for long String fields), but are constrained primarily
> to display purposes, and cannot be used in Crystal formulas, as
> variables, etc., so IMHO might be a slightly less desirable option
> than long strings.
>
> Clearly you don't want to be designing your drivers to conform to
> some commercial software package's usage, but if there's a
> reasonable, spec-conforming solution that would work in light of the
> above, I for one would be grateful to see it implemented.
>
> Thanks,
>
> --Ken
>
>
>>>> Kris Jurka <books@ejurka.com> 12/03/08 1:53 PM >>>
>
>
> On Wed, 3 Dec 2008, Ken Boss wrote:
>
>> Thanks much for your response, Kris.  I have been able to make
>> things work for my purposes by applying the patch suggested by
>> Daniel Migowski in the thread referenced below.  I tried to
>> implement your suggestion as well, but know only enough to be
>> dangerous.  Returning Integer.MAX_VALUE from the Oid..TEXT case in
>> the getPrecision method of TypeInfoCache.java had no discernible
>> effect - I must have been coding in the wrong place. At any rate, I
>> will look forward to that fix in the next release.
>>
>
> That's basically what I had in mind, but for a couple more types as
> well (see the attached patch).  If this doesn't work we need to know
> that so we can come up with something else, so your testing is
> appreciated.  Can you be more clear about what Crystal Reports does
> when it gets confused?  Is it possible that CR is trying to calculate
> a total row size in an integer value and overflows when it sees
> Integer.MAX_VALUE?  If so, returning some other large, but not huge
> value might work.
>
> Kris Jurka

On Wed, 3 Dec 2008, Kris Jurka wrote:

> OK, this is the exactly the data we need.  So neither Daniel's nor my
> solution are completely adequate.  It seems what we need is an option to
> specify the precision value returned for unknown length items.  That will
> allow you to set it to whatever value you want.

I have added a new URL parameter, unknownLength, to allow the user to
specify the desired return value for types of unknown length.

Kris Jurka

Hi,

Sorry for digging up an old post but I'm facing the descibed problem and
have some wondering about.
I'm using Crytal Report whith postgresql via the driver jdbc4 9 0 801.
The text columns are shown in CR as String of length -1 (hence producing
hazardous character in report)

I've read the discussion but could not understand if any correction has been
implemented or not. (discussion dates from 8.3 or 8.4)
I don't see how to deal with the above mentioned parameter unknownLength.
Can I access it and set it myself?

Thanks for your time
Marc
--
View this message in context: http://postgresql.1045698.n5.nabble.com/text-type-handling-tp2175473p3331613.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

> I've read the discussion but could not understand if any correction has been
> implemented or not. (discussion dates from 8.3 or 8.4)
> I don't see how to deal with the above mentioned parameter unknownLength.
> Can I access it and set it myself?

Yes, this is a jdbc URL parameter as of 8.4 (?)--e.g.,
jdbc:postgresql://localhost/postgres?unknownLength=500 . See
http://jdbc.postgresql.org/documentation/84/connect.html .
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com