You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Øyvind Harboe <oy...@zylin.com> on 2006/05/25 14:52:10 UTC
VARCHAR and trailing space
What's the correct behaviour for VARCHAR and trailing space, i.e.
foo.setVarCharProp("abc ");
for:
foo.getVarCharProp().equals("abc")
foo.getVarCharProp().equals("abc ")
Which should yield true or false?
--
Øyvind Harboe
http://www.zylin.com
Re: VARCHAR and trailing space
Posted by Øyvind Harboe <oy...@zylin.com>.
On Thu, 2006-05-25 at 16:04 -0400, Andrus Adamchik wrote:
> On May 25, 2006, at 1:34 PM, Øyvind Harboe wrote:
>
> > NB!!! MS SQL server behaves the same way as MS Access. NB!!!
> >
> > In my tests HSQLDB will not yield a match for WHERE "foo"="foo<space>"
>
> And why should it? The two are not equal. (this is VARCHAR's, right?)
>
> Do you have that test case that actually shows what problems it
> causes in Cayenne?
Yes, search for ObjectIdTrailingSpace.java in the attached MS Access
adatper work-in-progress + JUnit test to CAY-550.
http://issues.apache.org/cayenne/secure/attachment/10393/msaccessadapterwithtestcase.txt
The fascinating thing with this test case is that it should:
- Fail with HSQLDB
- Fail with MS SQL server if constraints stop me from corrupting
the database(not yet tested....)
- Pass with MS SQL server if constraints are not used
- Pass with MS Access
Yuk! :-)
--
Øyvind Harboe
http://www.zylin.com
Re: VARCHAR and trailing space
Posted by Øyvind Harboe <oy...@zylin.com>.
On Thu, 2006-05-25 at 16:21 -0400, Andrus Adamchik wrote:
> I see. What I couldn't understand till now is how you ended up with
> such data. This explains it :-)
Did you really think that an MS Access adapter would be just another
adapter? :-)
MS Access is used for some pretty foul smelling applications out there.
Lots of nasty issues and sordid histories. Often Access applications are
written by non-programmers who get into all sorts of trouble.
My goal with the MS Access adapter is to provide something that can
aid migration to a real database.
E.g. I don't have any particular issues with adding a 3 second delay to
MSAccessPkGenerator.autoPkTableExists() as long as it works reliably
enough to run the JUnit tests. Adding support for reverse engineering
would be nice, but I shudder at the thought since I don't think
the MS Access JDBC implementation will be stable enough(evidence so
far points towards lots of problems).
--
Øyvind Harboe
http://www.zylin.com
Re: VARCHAR and trailing space
Posted by Andrus Adamchik <an...@objectstyle.org>.
I see. What I couldn't understand till now is how you ended up with
such data. This explains it :-)
Andrus
On May 25, 2006, at 4:18 PM, Øyvind Harboe wrote:
> On Thu, 2006-05-25 at 16:04 -0400, Andrus Adamchik wrote:
>> On May 25, 2006, at 1:34 PM, Øyvind Harboe wrote:
>>
>>> NB!!! MS SQL server behaves the same way as MS Access. NB!!!
>>>
>>> In my tests HSQLDB will not yield a match for WHERE
>>> "foo"="foo<space>"
>>
>> And why should it? The two are not equal. (this is VARCHAR's, right?)
>
> I prefer HSQLDB's behaviour. That MS Access & MS SQL Server yield
> a match in this case is *stopid*.
>
> The problem is that we're trying to lift an old application from MS
> Access & MS SQL server + .asp over to Cayenne + Tapestry. While we
> are waiting for the write-from-scratch & migration to complete for
> all customers, we need to be bug-by-bug compatible with the old
> database. That might not be possible, but things will go much,
> much smoother if we can achieve it(since we don't have to
> touch the .asp + vbscript code then). We have also have
> some particularly imaginative and unusual plans for those
> "developers" that "exploited" this particular quirk in MS Access/MS
> SQL
> server :-)
>
> Once the migration is complete, we'll toss out MS Access & MS SQL
> server.
>
>
> --
> Øyvind Harboe
> http://www.zylin.com
>
>
Re: VARCHAR and trailing space
Posted by Øyvind Harboe <oy...@zylin.com>.
On Thu, 2006-05-25 at 16:04 -0400, Andrus Adamchik wrote:
> On May 25, 2006, at 1:34 PM, Øyvind Harboe wrote:
>
> > NB!!! MS SQL server behaves the same way as MS Access. NB!!!
> >
> > In my tests HSQLDB will not yield a match for WHERE "foo"="foo<space>"
>
> And why should it? The two are not equal. (this is VARCHAR's, right?)
I prefer HSQLDB's behaviour. That MS Access & MS SQL Server yield
a match in this case is *stopid*.
The problem is that we're trying to lift an old application from MS
Access & MS SQL server + .asp over to Cayenne + Tapestry. While we
are waiting for the write-from-scratch & migration to complete for
all customers, we need to be bug-by-bug compatible with the old
database. That might not be possible, but things will go much,
much smoother if we can achieve it(since we don't have to
touch the .asp + vbscript code then). We have also have
some particularly imaginative and unusual plans for those
"developers" that "exploited" this particular quirk in MS Access/MS SQL
server :-)
Once the migration is complete, we'll toss out MS Access & MS SQL
server.
--
Øyvind Harboe
http://www.zylin.com
Re: VARCHAR and trailing space
Posted by Andrus Adamchik <an...@objectstyle.org>.
On May 25, 2006, at 1:34 PM, Øyvind Harboe wrote:
> NB!!! MS SQL server behaves the same way as MS Access. NB!!!
>
> In my tests HSQLDB will not yield a match for WHERE "foo"="foo<space>"
And why should it? The two are not equal. (this is VARCHAR's, right?)
Do you have that test case that actually shows what problems it
causes in Cayenne?
Andrus
Re: VARCHAR and trailing space
Posted by Øyvind Harboe <oy...@zylin.com>.
On Thu, 2006-05-25 at 10:36 -0400, Andrus Adamchik wrote:
> VARCHARs normally should not be trimmed (at least not by the
> framework). It as assumed that database does not pad VARCHARs with
> spaces, as this goes against the definition of VARCHAR (unlike CHARs
> where the behavior is undefined). So trailing spaces are assumed to
> have meaning to the user.
Good. This lines up with my understanding.
> This is why I was surprised by your report
> that MS Access does in fact pads VARCHARs. Maybe it simply doesn't
> support this data type?
NB!!! MS SQL server behaves the same way as MS Access. NB!!!
In my tests HSQLDB will not yield a match for WHERE "foo"="foo<space>"
As far as I can tell MS Access supports VARCHAR just fine.
--
Øyvind Harboe
http://www.zylin.com
Re: VARCHAR and trailing space
Posted by Andrus Adamchik <an...@objectstyle.org>.
VARCHARs normally should not be trimmed (at least not by the
framework). It as assumed that database does not pad VARCHARs with
spaces, as this goes against the definition of VARCHAR (unlike CHARs
where the behavior is undefined). So trailing spaces are assumed to
have meaning to the user. This is why I was surprised by your report
that MS Access does in fact pads VARCHARs. Maybe it simply doesn't
support this data type?
Andrus
On May 25, 2006, at 8:52 AM, Øyvind Harboe wrote:
> What's the correct behaviour for VARCHAR and trailing space, i.e.
>
> foo.setVarCharProp("abc ");
>
> for:
>
> foo.getVarCharProp().equals("abc")
> foo.getVarCharProp().equals("abc ")
>
> Which should yield true or false?
>
>
> --
> Øyvind Harboe
> http://www.zylin.com
>
>