Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] JDBC adaptor issue

From: Barry Lind <barry(at)xythos(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] JDBC adaptor issue
Date: 2001-06-25 15:43:10
Message-ID: 3B375C0E.2040406@xythos.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc

Barry Lind wrote:

> This is an interesting problem.  And I can't think a any easy solution. 
>  But given TOAST in 7.1 the existing implementation doesn't make sense 
> IMHO  My suggestion would be that the get/setXXXStream methods work on 
> TOASTed data types and get/setBlob be used for Blobs.
> 
> As far as your patch, I don't see that as a generic solution.  It is 
> equally likely that a Blob could contain less than 8190 characters, or a 
> varchar could contain more that 8190 characters in 7.1.  Using this 
> number as a magic switch to decide whether the driver uses the BLOB API 
> or not just won't work in the general case.
> 
> thanks,
> --Barry
> 
> 
> btoback(at)mac(dot)com wrote:
> 
>> Hi all,
>>
>> I've been trying to get PostgreSQL to work with Apple's WebObjects 
>> application server. WebObjects uses JDBC as an interface to back-end 
>> databases, translating between SQL and a pure object model.
>>
>> I had a problem with incorrect SQL being generated and sent to the 
>> PostgreSQL back end. After some work, I tracked it down. I have a fix, 
>> but the fix has ramifications for the way that others use PostgreSQL, 
>> so I decided to post here and see what people think.
>>
>> It turns out that WebObjects uses the 
>> PreparedStatement.setCharacterStream method in order to set the values 
>> of some character parameters in prepared statements, and thus the 
>> generated SQL. It's not at all clear why it does this for some 
>> parameters but not others; the reason doesn't seem to have anything to 
>> do with the declared length of the parameters. This seems odd, because 
>> setCharacterStream is a very high-overhead operation, but in any case, 
>> that's what it does.
>>
>> The PostgreSQL JDBC driver, however, makes the assumption that any 
>> JDBC client class that's using the set/get...stream methods wants to 
>> exchange information with a field that's been explicitly typed as a 
>> BLOB. It therefore does what PostgreSQL requires: it creates a new 
>> object containing the data, then uses the object ID of the new object 
>> as the value to stuff into the query. This has the effect of 
>> generating queries like
>>
>>    SELECT ...
>>     WHERE some_text_field = 57909 ...
>>
>> 57909 is an object ID. The comparison doesn't work because 
>> some_text_field is an ordinary char or varchar, not a BLOB.
>>
>> It's kind of hard to figure out the "right" solution to this problem. 
>> I've patched the PostgreSQL JDBC implementation of 
>> PreparedStatement.setCharacterStream to treat any stream smaller than 
>> 8190 bytes as a string. I chose 8190 because of the old limit of 8192 
>> bytes per tuple in versions prior to 7.1, so this change is least 
>> likely to cause compatibility problems with systems using 
>> setCharacterStream the way that the PostgreSQL developers anticipated. 
>> I can provide the patch to anyone who needs it.
>>
>> The WebObjects use of JDBC is in line with the JDBC 2.0 specification; 
>> that spec does not place any restrictions on the types of fields that 
>> can be accessed via get/set...stream. Whether it's a good use is a 
>> different question, of course, but it's still legal. My little kludge 
>> with an 8190-byte "switch" to the old behavior really can't be the 
>> last word.
>>
>> I was hoping that someone could look at the PostgreSQL back end to see 
>> if there's any reason to keep the 8190-byte limiting behavior in the 
>> JDBC driver. The limit needs to be removed so that character streams 
>> and strings are symmetric in order to comply with JDBC 2.0. The effect 
>> of switching will simply be the possibility that the back end will 
>> have to deal with very long (>8k) quoted strings. I got the impression 
>> from reading TOAST project documents that all such limitations had 
>> been removed, but I wanted to check before submitting my patch for 
>> inclusion in the distribution.
>>
>> Thanks,
>> -- Bruce
>>
>> -------------------------------------------------------------------------- 
>>
>> Bruce Toback    Tel: (602) 996-8601| My candle burns at both ends;
>> OPT, Inc.            (800) 858-4507| It will not last the night;
>> 11801 N. Tatum Blvd. Ste. 142      | But ah, my foes, and oh, my 
>> friends -
>> Phoenix AZ 85028                   | It gives a lovely light.
>> btoback(at)optc(dot)com                   |     -- Edna St. Vincent Millay
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
> 
> 



In response to

Responses

pgsql-hackers by date

Next:From: Barry LindDate: 2001-06-25 15:43:36
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC
Previous:From: Barry LindDate: 2001-06-25 15:42:47
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC

pgsql-jdbc by date

Next:From: Barry LindDate: 2001-06-25 15:43:36
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC
Previous:From: Barry LindDate: 2001-06-25 15:42:47
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group