You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Rick <ri...@gmail.com> on 2009/01/16 23:45:20 UTC
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
Re: Sybase issue... TEXT column when null insert/updates fail, but when
defined as VARCHAR nulls work?
Posted by Ch...@sybase.com.
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 <ri...@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 <ri...@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, <Ch...@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 <ri...@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
Re: Sybase issue... TEXT column when null insert/updates fail, but when defined as VARCHAR nulls work?
Posted by Rick <ri...@gmail.com>.
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 <ri...@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, <Ch...@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 <ri...@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
Re: Sybase issue... TEXT column when null insert/updates fail, but when defined as VARCHAR nulls work?
Posted by Rick <ri...@gmail.com>.
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, <Ch...@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 <ri...@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
Re: Sybase issue... TEXT column when null insert/updates fail, but when
defined as VARCHAR nulls work?
Posted by Ch...@sybase.com.
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 <ri...@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