Re: 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 Crystal Reports / Crystal Enterprise. This seems to be largely possible from our Linux servers through the use of your JDBC drivers, with the one exception 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. Can anyone please update me on the status of this issue? It seems a small stumbling block, but could 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 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.

Thanks again,

--Ken

>>> On 12/2/2008 at 4:40 PM, in message
<Pine(dot)BSO(dot)4(dot)64(dot)0812021736080(dot)1073(at)leary(dot)csoft(dot)net>, Kris Jurka
<books(at)ejurka(dot)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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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> |&macr;&macr;|&macr;&macr;| <b>IKOffice GmbH Daniel Migowski</b>
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)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>
</body>
</html>

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