Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 32977 invoked from network); 19 Jan 2009 18:28:31 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 19 Jan 2009 18:28:31 -0000 Received: (qmail 54372 invoked by uid 500); 19 Jan 2009 18:28:28 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 54360 invoked by uid 500); 19 Jan 2009 18:28:28 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 54351 invoked by uid 99); 19 Jan 2009 18:28:28 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Jan 2009 10:28:28 -0800 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.138.151.122] (HELO fm200.sybase.com) (192.138.151.122) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Jan 2009 18:28:19 +0000 Received: from smtp2.sybase.com (sybgate2.sybase.com [10.22.97.85]) by fm200.sybase.com with ESMTP id n0JIRvZ08577 for ; Mon, 19 Jan 2009 10:27:57 -0800 (PST) Received: from gwwest.sybase.com (localhost [127.0.0.1]) by smtp2.sybase.com with ESMTP id n0JIRuF08134 for ; Mon, 19 Jan 2009 10:27:56 -0800 (PST) In-Reply-To: <583d4dff0901170714m736f33fx6761a24c02fd0332@mail.gmail.com> To: user-java@ibatis.apache.org Subject: Re: Sybase issue... TEXT column when null insert/updates fail, but when defined as VARCHAR nulls work? MIME-Version: 1.0 X-Mailer: Lotus Notes Release 6.5 September 26, 2003 Message-ID: From: Christopher.Mathrusse@sybase.com Date: Mon, 19 Jan 2009 10:27:52 -0800 X-MIMETrack: Serialize by Router on gwwest/SYBASE(Release 6.5.5|November 30, 2005) at 01/19/2009 10:27:56 AM Content-Type: multipart/mixed; boundary="=_mixed 00656E5D88257543_=" X-Virus-Checked: Checked by ClamAV on apache.org --=_mixed 00656E5D88257543_= Content-Type: multipart/alternative; boundary="=_alternative 00656E5D88257543_=" --=_alternative 00656E5D88257543_= Content-Type: text/plain; charset="US-ASCII" Sorry, I overlooked the fact that I didn't provide it. As far as I know, there are no plans to change the implementation in JConnect. On occasion, I can sometimes get them to correct a bug when I scout it out, but issues like this are up to the powers that be that decide the priority of the issues. If enough people make noise about a particular problem they will get resolved though, and this might be one of those issues as I have seen quite a few people run into this. I personally stayed away from the jTDS driver due to the lack of XA support and the fact that I like my job here at Sybase. I hope this helps.... Rick 01/17/2009 07:14 AM Please respond to user-java@ibatis.apache.org To user-java@ibatis.apache.org cc Subject Re: Sybase issue... TEXT column when null insert/updates fail, but when defined as VARCHAR nulls work? Care to also share your StreamTypeHandlerCallback class? (I don't believe that's a base class in ibatis, only the interface of TypeHandlerCallback.) I could probably figure out how to write it, but hey, you know... stand on the shoulders of giants... ok at least that's my excuse for being lazy and copy and pasting:) On Sat, Jan 17, 2009 at 9:59 AM, Rick wrote: > Thanks for this Christopher. > > Interestingly, I tried out the jtds driver and it allowed the null > inserts on TEXT datatype. . (Then again jtds had the problem with the > DATE field, putting things in off a few seconds unless I did > convert(DATETIME, bd.broadcastDate) ). Just curious, do you know if > they have plans to fix this in the jconnect driver? Seems like a bug? > > I'll probably use the handler you provided and change my TEXT types to > LONGVARCHAR. Thanks again, this was really helpful. > > > On Fri, Jan 16, 2009 at 5:56 PM, wrote: >> >> This is a JConnect Driver issue. It doesn't like it when you attempt to set >> a NULL for a TEXT field. This is something that I have run into many times. >> One easy solution is rather than assigning NULL try assigning an empty >> string. This gets around the issue in the driver. >> >> As a side note, you may want to perform some tests with this type of >> definition in iBatis. My experience has show that a TEXT field that is >> defined as VARCHAR will usually get truncated after a certain number of >> characters. ( I can't remember the size) >> >> To work around both these issues I defined a custom type handler. Code >> below: >> >> public class ClobTypeHandlerCallback extends >> StreamTypeHandlerCallback { >> >> public Object getResult(ResultGetter getter) throws SQLException { >> java.sql.ResultSet rs = getter.getResultSet(); >> java.io.OutputStream os = >> getAsOutputStream(rs.getAsciiStream(getter.getColumnName())); >> >> return os != null ? os.toString() : null; >> } >> >> public void setParameter(ParameterSetter setter, Object parameter) >> throws SQLException { >> super.setAsciiStream(setter, parameter); >> } >> } >> >> And register it as follows: >> > callback="com.....ClobTypeHandlerCallback" /> >> >> >> I hope this helps you. >> >> >> >> Rick >> >> 01/16/2009 02:45 PM >> >> Please respond to >> user-java@ibatis.apache.org >> To >> ibatis-user-java@incubator.apache.org >> cc >> Subject >> Sybase issue... TEXT column when null insert/updates fail, but when defined >> as VARCHAR nulls work? >> >> >> >> >> I'm a bit curious about this. I'm using the jconnect driver( jconn3 >> version 6.) I have a column defined as: >> >> datatype: 2005 >> type_name: TEXT >> Column size: 2147483647 >> Buffer Length: 16 >> >> On retrievals TEXT works just fine, and on updates/inserts TEXT is >> fine also IF the field is not null, but if the field is null, Sybase >> complains when it's declared as a TEXT (or CLOB) ( #spec:TEXT# ). >> Yet if I change it to VARCHAR the null value is accepted and the >> insert/update works. Shouldn't TEXT jdbc type work for all phases of >> the CRUD? >> >> >> >> -- >> Rick >> >> >> > > > > -- > Rick > -- Rick --=_alternative 00656E5D88257543_= Content-Type: text/html; charset="US-ASCII"
Sorry, I overlooked the fact that I didn't provide it.

As far as I know, there are no plans to change the implementation in JConnect. On occasion, I can sometimes get them to correct a bug when I scout it out, but issues like this are up to the powers that be that decide the priority of the issues. If enough people make noise about a particular problem they will get resolved though, and this might be one of those issues as I have seen quite a few people run into this.

I personally stayed away from the jTDS driver due to the lack of XA support and the fact that I like my job here at Sybase.

I hope this helps....





Rick <rickcr@gmail.com>

01/17/2009 07:14 AM
Please respond to
user-java@ibatis.apache.org
To
user-java@ibatis.apache.org
cc
Subject
Re: Sybase issue... TEXT column when null insert/updates fail, but when defined as VARCHAR nulls work?





Care to also share your StreamTypeHandlerCallback class? (I don't
believe that's a base class in ibatis, only the interface of
TypeHandlerCallback.)
I could probably figure out how to write it, but hey, you know...
stand on the shoulders of giants... ok at least that's my excuse for
being lazy and copy and pasting:)

On Sat, Jan 17, 2009 at 9:59 AM, Rick <rickcr@gmail.com> wrote:
> Thanks for this Christopher.
>
> Interestingly, I tried out the jtds driver and it allowed the null
> inserts on TEXT datatype. . (Then again jtds had the problem with the
> DATE field, putting things in off a few seconds unless I did
> convert(DATETIME, bd.broadcastDate) ). Just curious, do you know if
> they have plans to fix this in the jconnect driver? Seems like a bug?
>
> I'll probably use the handler you provided and change my TEXT types to
> LONGVARCHAR. Thanks again, this was really helpful.
>
>
> On Fri, Jan 16, 2009 at 5:56 PM,  <Christopher.Mathrusse@sybase.com> wrote:
>>
>> This is a JConnect Driver issue. It doesn't like it when you attempt to set
>> a NULL for a TEXT field. This is something that I have run into many times.
>> One easy solution is rather than assigning NULL try assigning an empty
>> string. This gets around the issue in the driver.
>>
>> As a side note, you may want to perform some tests with this type of
>> definition in iBatis. My experience has show that a TEXT field that is
>> defined as VARCHAR will usually get truncated after a certain number of
>> characters. ( I can't remember the size)
>>
>> To work around both these issues I defined a custom type handler. Code
>> below:
>>
>> public class ClobTypeHandlerCallback extends
>>                 StreamTypeHandlerCallback {
>>
>>         public Object getResult(ResultGetter getter) throws SQLException {
>>                 java.sql.ResultSet rs = getter.getResultSet();
>>                 java.io.OutputStream os =
>>  getAsOutputStream(rs.getAsciiStream(getter.getColumnName()));
>>
>>                 return os != null ? os.toString() : null;
>>         }
>>
>>         public void setParameter(ParameterSetter setter, Object parameter)
>>                         throws SQLException {
>>                 super.setAsciiStream(setter, parameter);
>>         }
>> }
>>
>> And register it as follows:
>>   <typeHandler javaType="java.lang.String" jdbcType="LONGVARCHAR"
>>     callback="com.....ClobTypeHandlerCallback" />
>>
>>
>> I hope this helps you.
>>
>>
>>
>> Rick <rickcr@gmail.com>
>>
>> 01/16/2009 02:45 PM
>>
>> Please respond to
>> user-java@ibatis.apache.org
>> To
>> ibatis-user-java@incubator.apache.org
>> cc
>> Subject
>> Sybase issue... TEXT column when null insert/updates fail, but when defined
>> as VARCHAR nulls work?
>>
>>
>>
>>
>> I'm a bit curious about this. I'm using the jconnect driver( jconn3
>> version 6.) I have a column defined as:
>>
>> datatype: 2005
>> type_name: TEXT
>> Column size: 2147483647
>> Buffer Length: 16
>>
>> On retrievals TEXT works just fine, and on updates/inserts TEXT is
>> fine also IF the field is not null, but if the field is null, Sybase
>> complains when it's declared as a TEXT (or CLOB) (   #spec:TEXT#  ).
>> Yet if I change it to VARCHAR the null value is accepted and the
>> insert/update works.  Shouldn't TEXT jdbc type work for all phases of
>> the CRUD?
>>
>>
>>
>> --
>> Rick
>>
>>
>>
>
>
>
> --
> Rick
>



--
Rick
--=_alternative 00656E5D88257543_=-- --=_mixed 00656E5D88257543_= Content-Type: application/octet-stream; name="StreamTypeHandlerCallback.java" Content-Disposition: attachment; filename="StreamTypeHandlerCallback.java" Content-Transfer-Encoding: base64 LyoqDQogKg0KICovDQpwYWNrYWdlIGNvbS5zeWJhc2UuaXQuY29zbW9zLmRhby5pbXBsLmliYXRp cy5leHRlbnRpb25zOw0KDQppbXBvcnQgamF2YS5pby5JT0V4Y2VwdGlvbjsNCmltcG9ydCBqYXZh LnNxbC5TUUxFeGNlcHRpb247DQoNCmltcG9ydCBjb20uaWJhdGlzLnNxbG1hcC5jbGllbnQuZXh0 ZW5zaW9ucy5QYXJhbWV0ZXJTZXR0ZXI7DQppbXBvcnQgY29tLmliYXRpcy5zcWxtYXAuY2xpZW50 LmV4dGVuc2lvbnMuVHlwZUhhbmRsZXJDYWxsYmFjazsNCg0KLyoqDQogKiBAYXV0aG9yIGNtYXRo cnVzDQogKg0KICovDQpwdWJsaWMgYWJzdHJhY3QgY2xhc3MgU3RyZWFtVHlwZUhhbmRsZXJDYWxs YmFjayBpbXBsZW1lbnRzIFR5cGVIYW5kbGVyQ2FsbGJhY2sgew0KDQoJLyoqDQoJICogQ29udmVy dHMgdGhlIHBhc3NlZCBpbiBPYmplY3QgcGFyYW1ldGVyIGludG8gYSBqYXZhLmlvLklucHV0U3Ry ZWFtIHJlYWR5IGZvciBjb25zdW1wdGlvbi4NCgkgKiBUaGUgYXNzdW1wdGlvbiBoZXJlIGlzIHRo YXQgdGhlIHBhc3NlZCBpbiBPYmplY3Qgc2hvdWxkIGJlDQoJICogb2YgdHlwZSBqYXZhLmxhbmcu U3RyaW5nIG9yIGEgYnl0ZVtdLiBJbiBhZGRpdGlvbiwgbnVsbCBpcyBzdXBwb3J0ZWQuIElmIGEg bnVsbA0KCSAqIGlzIHBhc3NlZCBpbiB0aGVuIGFuIGVtcHR5IElucHV0U3RyZWFtIHdpbGwgc3Rp bGwgYmUgcmV0dXJuZWQuDQoJICoNCgkgKiBAcGFyYW0gcGFyYW1ldGVyDQoJICogQHJldHVybiBU aGUgSW5wdXRTdHJlYW0gdGhhdCBpcyB3cmFwcGluZyB0aGUgcGFzc2VkIGluIG9iamVjdC4NCgkg KiBAdGhyb3dzIFNRTEV4Y2VwdGlvbiBJZiB0aGUgcGFzc2VkIGluIG9iamVjdCBpcyBub3Qgb25l IG9mIHRoZSBkZWZpbmVkIHR5cGVzIGxpc3RlZCBhYm92ZS4NCgkgKi8NCglwcm90ZWN0ZWQgamF2 YS5pby5JbnB1dFN0cmVhbSBjb252ZXJ0VG9JbnB1dFN0cmVhbShPYmplY3QgcGFyYW1ldGVyKQ0K CQl0aHJvd3MgU1FMRXhjZXB0aW9uIHsNCgkJYnl0ZVtdIGJ5dGVzID0gbnVsbDsNCg0KCQlpZihw YXJhbWV0ZXIgPT0gbnVsbCkgew0KCQkJYnl0ZXMgPSBudWxsOw0KCQl9DQoJCWVsc2UgaWYocGFy YW1ldGVyLmdldENsYXNzKCkuaXNBc3NpZ25hYmxlRnJvbShieXRlW10uY2xhc3MpKSB7DQoJCQli eXRlcyA9IChieXRlW10pIHBhcmFtZXRlcjsNCgkJfQ0KCQllbHNlIGlmKHBhcmFtZXRlci5nZXRD bGFzcygpLmlzQXNzaWduYWJsZUZyb20oU3RyaW5nLmNsYXNzKSkgew0KCQkJYnl0ZXMgPSAoKFN0 cmluZylwYXJhbWV0ZXIpLmdldEJ5dGVzKCk7DQoJCX0NCgkJZWxzZSB7DQoJCQl0aHJvdyBuZXcg amF2YS5zcWwuU1FMRXhjZXB0aW9uKCJVbnN1cHBvcnRlZCBkYXRhdHlwZSEgRXhwZWN0ZWQgamF2 YS5sYW5nLlN0cmluZyBvciBieXRlW10gYnV0IGZvdW5kOiAiICsgcGFyYW1ldGVyLmdldENsYXNz KCkpOw0KCQl9DQoNCgkJcmV0dXJuIG5ldyBqYXZhLmlvLkJ5dGVBcnJheUlucHV0U3RyZWFtKGJ5 dGVzKTsNCgl9DQoNCgkvKioNCgkgKiBDb252ZXJ0cyB0aGUgcGFzc2VkIGluIGphdmEuaW8uSW5w dXRTdHJlYW0gaW50byBhIEJ5dGVBcnJheU91dHB1dFN0cmVhbSB0aGF0IGlzIHJlYWR5IGZvciBj b25zdW1wdGlvbi4NCgkgKiBAcGFyYW0gaXMNCgkgKiBAcmV0dXJuDQoJICogQHRocm93cyBqYXZh LnNxbC5TUUxFeGNlcHRpb24gSWYgYW4gSU9FeGNlcHRpb24gb2NjdXJzIGl0IHdpbGwgYmUgd3Jh cHBlZCBpbiBhbiBTUUxFeGNlcHRpb24NCgkgKi8NCglwcm90ZWN0ZWQgamF2YS5pby5CeXRlQXJy YXlPdXRwdXRTdHJlYW0gIGdldEFzT3V0cHV0U3RyZWFtKGphdmEuaW8uSW5wdXRTdHJlYW0gaXMp IHRocm93cyBqYXZhLnNxbC5TUUxFeGNlcHRpb24gew0KDQoJCWlmKGlzID09IG51bGwpIHsNCgkJ CXJldHVybiBudWxsOw0KCQl9DQoNCgkJamF2YS5pby5CeXRlQXJyYXlPdXRwdXRTdHJlYW0gb3Mg PSBuZXcgamF2YS5pby5CeXRlQXJyYXlPdXRwdXRTdHJlYW0oKTsNCg0KCQlieXRlIGJ5dGVzW10g PSBuZXcgYnl0ZVsxMDAwMF07DQoJCXRyeSB7DQoJCQlmb3IgKGludCBudW1SZWFkID0gaXMucmVh ZChieXRlcyk7IG51bVJlYWQgIT0gLTE7IG51bVJlYWQgPSBpcy5yZWFkKGJ5dGVzKSkgew0KCQkJ CW9zLndyaXRlKGJ5dGVzLCAwLCBudW1SZWFkKTsNCgkJCX0NCgkJfSBjYXRjaCAoSU9FeGNlcHRp b24gZSkgew0KCQkJdGhyb3cgbmV3IGphdmEuc3FsLlNRTEV4Y2VwdGlvbihlLmdldE1lc3NhZ2Uo KSk7DQoJCX0gZmluYWxseSB7DQoJCQl0cnkgew0KCQkJCWlzLmNsb3NlKCk7DQoJCQl9IGNhdGNo IChqYXZhLmlvLklPRXhjZXB0aW9uIGUpIHsNCgkJCQkvLyBpZ25vcmUNCgkJCX0NCgkJfQ0KDQoJ CXJldHVybiBvczsNCgl9DQoNCgkvKioNCgkgKiBDb252ZXJ0cyB0aGUgcGFzc2VkIGluIHBhcmFt ZXRlciBvYmplY3QgaW50byBhIGphdmEuaW8uSW5wdXRTdHJlYW0gdGhhdCBjYW4gYmUgY29uc3Vt ZWQNCgkgKiBieSB0aGUgcGFzc2VkIGluIHNldHRlci4gVGhlIElucHV0U3RyZWFtIGlzIHNldCB1 c2luZyB0aGUgc2V0dGVycyBzZXRBc2NpaVN0cmVhbSBtZXRob2QuDQoJICogQHBhcmFtIHNldHRl cg0KCSAqIEBwYXJhbSBwYXJhbWV0ZXINCgkgKiBAdGhyb3dzIFNRTEV4Y2VwdGlvbiBJZiBhbiBJ T0V4Y2VwdGlvbiBvY2N1cnMgaXQgaXMgd3JhcHBlZCB3aXRoaW4gYW4gU1FMRXhjZXB0aW9uLg0K CSAqLw0KCXB1YmxpYyB2b2lkIHNldEFzY2lpU3RyZWFtKFBhcmFtZXRlclNldHRlciBzZXR0ZXIs IE9iamVjdCBwYXJhbWV0ZXIpDQoJCXRocm93cyBTUUxFeGNlcHRpb24gew0KDQoJCWlmKHBhcmFt ZXRlciA9PSBudWxsKSB7DQoJCQlzZXR0ZXIuc2V0TnVsbChqYXZhLnNxbC5UeXBlcy5MT05HVkFS Q0hBUik7DQoJCX0NCgkJZWxzZSB7DQoJCQlqYXZhLmlvLklucHV0U3RyZWFtIGlzID0gY29udmVy dFRvSW5wdXRTdHJlYW0ocGFyYW1ldGVyKTsNCgkJCXRyeSB7DQoJCQkJc2V0dGVyLnNldEFzY2lp U3RyZWFtKGlzLCBpcy5hdmFpbGFibGUoKSk7DQoJCQl9IGNhdGNoIChJT0V4Y2VwdGlvbiBlKSB7 DQoJCQkJdGhyb3cgbmV3IGphdmEuc3FsLlNRTEV4Y2VwdGlvbihlLmdldE1lc3NhZ2UoKSk7DQoJ CQl9DQoJCX0NCgl9DQoNCgkvKioNCgkgKiBDb252ZXJ0cyB0aGUgcGFzc2VkIGluIHBhcmFtZXRl ciBvYmplY3QgaW50byBhIGphdmEuaW8uSW5wdXRTdHJlYW0gdGhhdCBjYW4gYmUgY29uc3VtZWQN CgkgKiBieSB0aGUgcGFzc2VkIGluIHNldHRlci4gVGhlIElucHV0U3RyZWFtIGlzIHNldCB1c2lu ZyB0aGUgc2V0dGVycyBzZXRCaW5hcnlTdHJlYW0gbWV0aG9kLg0KCSAqIEBwYXJhbSBzZXR0ZXIN CgkgKiBAcGFyYW0gcGFyYW1ldGVyDQoJICogQHRocm93cyBTUUxFeGNlcHRpb24gSWYgYW4gSU9F eGNlcHRpb24gb2NjdXJzIGl0IGlzIHdyYXBwZWQgd2l0aGluIGFuIFNRTEV4Y2VwdGlvbi4NCgkg Ki8NCglwdWJsaWMgdm9pZCBzZXRCaW5hcnlTdHJlYW0oUGFyYW1ldGVyU2V0dGVyIHNldHRlciwg T2JqZWN0IHBhcmFtZXRlcikNCgkJdGhyb3dzIFNRTEV4Y2VwdGlvbiB7DQoNCgkJaWYocGFyYW1l dGVyID09IG51bGwpIHsNCgkJCXNldHRlci5zZXROdWxsKGphdmEuc3FsLlR5cGVzLkxPTkdWQVJC SU5BUlkpOw0KCQl9DQoJCWVsc2Ugew0KCQkJamF2YS5pby5JbnB1dFN0cmVhbSBpcyA9IGNvbnZl cnRUb0lucHV0U3RyZWFtKHBhcmFtZXRlcik7DQoJCQl0cnkgew0KCQkJCXNldHRlci5zZXRCaW5h cnlTdHJlYW0oaXMsIGlzLmF2YWlsYWJsZSgpKTsNCgkJCX0gY2F0Y2ggKElPRXhjZXB0aW9uIGUp IHsNCgkJCQl0aHJvdyBuZXcgamF2YS5zcWwuU1FMRXhjZXB0aW9uKGUuZ2V0TWVzc2FnZSgpKTsN CgkJCX0NCgkJfQ0KCX0NCg0KCS8qDQoJICogKG5vbi1KYXZhZG9jKQ0KCSAqDQoJICogQHNlZSBj b20uaWJhdGlzLnNxbG1hcC5jbGllbnQuZXh0ZW5zaW9ucy5UeXBlSGFuZGxlckNhbGxiYWNrI3Zh bHVlT2YoamF2YS5sYW5nLlN0cmluZykNCgkgKi8NCglwdWJsaWMgT2JqZWN0IHZhbHVlT2YoU3Ry aW5nIHMpIHsNCgkJcmV0dXJuIHM7DQoJfQ0KfQ0K --=_mixed 00656E5D88257543_=--