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 22:49:01
Message-ID: 3B37BFDD.5030200@xythos.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
 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

pgsql-hackers by date

Next:From: Barry LindDate: 2001-06-25 22:49:51
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC
Previous:From: Barry LindDate: 2001-06-25 22:48:15
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC

pgsql-jdbc by date

Next:From: Barry LindDate: 2001-06-25 22:49:51
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC
Previous:From: Barry LindDate: 2001-06-25 22:48:15
Subject: Re: [HACKERS] Instrumenting and Logging in JDBC

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