JDBC adaptor issue
От | btoback@mac.com |
---|---|
Тема | JDBC adaptor issue |
Дата | |
Msg-id | 200106232332.QAA04745@smtpout.mac.com обсуждение исходный текст |
Список | pgsql-hackers |
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@optc.com | -- Edna St. Vincent Millay
В списке pgsql-hackers по дате отправления: