SQL Anywhere Feedback - new database option for empty string and null

Author

new database option for empty string and null

Daigo Moriwaki

2005-12-12, 3:23 am

# This was a request from a system integrator of a project.

High compatibility of SQL Anywhere with Oracle would help Mobilink synchronization
with Oracle.
The most serious incompatibility was empty string. Oracle considers empty string as
null value, while SQL Anywhere does not.
A new database option to regard empty string as null would be best.

Thanks,
Daigo

--
Daigo Moriwaki
iAnywhere Solutions K.K. [Tokyo]
daigo.moriwaki at ianywhere dot com

Dmitri

2005-12-12, 3:23 am

Daigo Moriwaki wrote:

> The most serious incompatibility was empty string. Oracle considers empty string as
> null value, while SQL Anywhere does not.
> A new database option to regard empty string as null would be best.

I'm strongly against introducing of weird Oracle bug-features into the ASA.

Dmitri.

M. Searer

2005-12-12, 1:23 pm

I agree that the empty string is a null is a weird bug feature in Oracle.

The question, however, is how to get the sync feature to work 'properly' with
Oracle.
It seems that maybe the requested functionality from the OP is backwards?

Would a sync feature of sending an empty string to the outside database (oracle)
as a single space work (this will save in oracle as an empty string) for the OP?
This could be a connection based option perhaps?

"Dmitri" <NOdimSPAM@mail15.com> wrote in message news:439d2d8f$1@foru
ms-1-dub...
> Daigo Moriwaki wrote:
>
>
> I'm strongly against introducing of weird Oracle bug-features into the ASA.
>
> Dmitri.

Daigo Moriwaki

2005-12-14, 3:23 am

M. Searer wrote:
> I agree that the empty string is a null is a weird bug feature in Oracle.

Yes, it's weird. However, there are much more Oracle users.

They are used to using empty string as null. A row that they insert with empty string
on a remote db turns null after synchronizations, and comparing the row with empty
string is not successful, which was successful before the synchronization.

> Would a sync feature of sending an empty string to the outside database (oracle)
> as a single space work (this will save in oracle as an empty string) for the OP?
> This could be a connection based option perhaps?

# Sorry, I don't know what OP means.

Yes, it could, as long as a single space (or something special) is never used and
users always use empty strings (never null).

Thanks,
Daigo

--
Daigo Moriwaki
iAnywhere Solutions K.K. [Tokyo]
daigo.moriwaki at ianywhere dot com

Breck Carter [TeamSybase]

2005-12-14, 9:23 am

Daigo does have a point... bug or not, there are a LOT MORE Oracle
people out there and they all think it's a feature. Daigo isn't asking
for a change in behavior, only an *option* to change the behavior.

Heaven knows, there are a SACKLOAD of stupid options in ASA to
accomodate bugs in Transact SQL, and IMO Oracle PL/SQL is much nicer
than Transact SQL... maybe an option or two to accomodate those less
fortunate than us (Oracle users) would be justified :)

MSS isn't the only competition.

Breck The Contrarian

On 11 Dec 2005 21:17:06 -0800, Daigo Moriwaki
<daigo.moriwaki@ianywhere.com> wrote:

># This was a request from a system integrator of a project.
>
>High compatibility of SQL Anywhere with Oracle would help Mobilink synchronization
>with Oracle.
>The most serious incompatibility was empty string. Oracle considers empty string as
>null value, while SQL Anywhere does not.
>A new database option to regard empty string as null would be best.
>
>Thanks,
>Daigo

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

David Kerber

2005-12-15, 7:23 am

In article < shd0q156d6kkgk1rgq1r
oq6s5kjl2a9sf0@4ax.com>,
NOSPAM__bcarter@risi
ngroad.com says...
> Daigo does have a point... bug or not, there are a LOT MORE Oracle
> people out there and they all think it's a feature. Daigo isn't asking
> for a change in behavior, only an *option* to change the behavior.

IMO that "feature" would be equivalent to having a zero in a numeric
field be treated as a null (or vice-verse), but whether we like it or
not, Oracle isn't the only database which treats an empty string as a
null.

.....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Breck Carter [TeamSybase]

2005-12-15, 9:23 am

If you knew how I feel about NULLs (hate 'em) you would be surprised
at my stand on this :)

You are right... I dunno about other DBMS products, but I do know that
some other client-side programming tools can turn '' into NULL; e.g.,
the PowerBuilder DataWindow column-level property "Empty String is
NULL".

Breck

On 15 Dec 2005 04:49:31 -0800, David Kerber
< ns_dkerber@ns_wraenv
iro.com> wrote:

>In article < shd0q156d6kkgk1rgq1r
oq6s5kjl2a9sf0@4ax.com>,
> NOSPAM__bcarter@risi
ngroad.com says...
>
>IMO that "feature" would be equivalent to having a zero in a numeric
>field be treated as a null (or vice-verse), but whether we like it or
>not, Oracle isn't the only database which treats an empty string as a
>null.
>
>....

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

M. Searer

2005-12-15, 8:24 pm

OP is original poster. That would be you.

I don't think you understand my point.
In oracle, if you enter a single space in a varchar2 field, it will be saved as
an EMPTY string in oracle, not as a null.

So, if ASA on replication sent a single space whenever it found an empty string,
then oracle would store an empty string. This way, oracle and ASA would store
the same value rather than different ones. I assumed that is what you wanted,
for both databases to store the same value.

However, if what you really want is to store a null value, I would think you
should change your application to store a null.

"Daigo Moriwaki" <daigo.moriwaki@ianywhere.com> wrote in message
news:hmm473-gvs.ln1@hermes.sybase.com...[color=darkred]
> M. Searer wrote:
>
> Yes, it's weird. However, there are much more Oracle users.
>
> They are used to using empty string as null. A row that they insert with empty
> string
> on a remote db turns null after synchronizations, and comparing the row with
> empty
> string is not successful, which was successful before the synchronization.
>
>
>
> # Sorry, I don't know what OP means.
>
> Yes, it could, as long as a single space (or something special) is never used
> and
> users always use empty strings (never null).
>
>
> Thanks,
> Daigo
>
> --
> Daigo Moriwaki
> iAnywhere Solutions K.K. [Tokyo]
> daigo.moriwaki at ianywhere dot com

Daigo Moriwaki

2005-12-16, 3:23 am

M. Searer wrote:
> In oracle, if you enter a single space in a varchar2 field, it will be saved as
> an EMPTY string in oracle, not as a null.

Please correct me. Is it Oracle's nature? Users define (read) it as an empty string?
I can not find any note on Oracle's manual:
http://www.oracle.com/technology/do...tion/index.html
-> Oracle Database 10g Release 2
-> SQL Reference
-> Single Characters or VARCHAR2 Datatype

Thanks,
Daigo

--
Daigo Moriwaki
iAnywhere Solutions K.K. [Tokyo]
daigo.moriwaki at ianywhere dot com

Breck Carter [TeamSybase]

2005-12-16, 9:23 am

A test shows this statement is not correct for Oracle and SQL Plus:

>In oracle, if you enter a single space in a varchar2 field, it will be saved as
>an EMPTY string in oracle, not as a null.

The test script and results are shown below; when run on Oracle 9i via
SQL Plus it shows that a single space is stored as a single space, not
an empty string. It also shows that an empty string is stored as a
null.

Perhaps the results are different for another client interface, but
that would not reflect *Oracle* behavior.

Breck

CREATE TABLE e (
pkey NUMBER PRIMARY KEY,
s VARCHAR2 ( 10 ) );

INSERT INTO e VALUES ( 1, 'x' );
INSERT INTO e VALUES ( 2, '' );
INSERT INTO e VALUES ( 3, ' ' );
COMMIT;

SELECT pkey,
COALESCE ( s, '[NULL]' ),
LENGTH ( s )
FROM e ORDER BY pkey;

PKEY COALESCE(S LENGTH(S)
---------- ---------- ----------
1 x 1
2 [NULL]
3 1

On 15 Dec 2005 16:12:25 -0800, "M. Searer" <nospam@nospam.com> wrote:

>OP is original poster. That would be you.
>
>I don't think you understand my point.
>In oracle, if you enter a single space in a varchar2 field, it will be saved as
>an EMPTY string in oracle, not as a null.
>
>So, if ASA on replication sent a single space whenever it found an empty string,
>then oracle would store an empty string. This way, oracle and ASA would store
>the same value rather than different ones. I assumed that is what you wanted,
>for both databases to store the same value.
>
>However, if what you really want is to store a null value, I would think you
>should change your application to store a null.
>
>
>
>"Daigo Moriwaki" <daigo.moriwaki@ianywhere.com> wrote in message
>news:hmm473-gvs.ln1@hermes.sybase.com...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Greg Fenton

2005-12-17, 8:23 pm

Breck Carter [TeamSybase] wrote:
>
> Perhaps the results are different for another client interface, but
> that would not reflect *Oracle* behavior.
>

Depends on whether they wrote/distribute the client interface causing
the problem ;-)

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-12-18, 9:23 am

On 17 Dec 2005 14:41:52 -0800, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:

>Depends on whether they wrote/distribute the client interface causing
>the problem ;-)

Doubt it... AFAIK treating '' as NULL has been Oracle behavior forever
and ever, they think it is a feature :)

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

M. Searer

2005-12-19, 8:24 pm

mea culpa! you are correct.

The work around I am using is to set all empty strings to a single space to get
Oracle to work like other databases.
Since mine is a multi-database application (ASE, MS SQL, ASA, Oracle), the
others I simply leave as a empty string. For whatever reason I thought oracle
was trimming off the space and storing as empty string.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in message
news:ifl5q1dfv9vcpm2
ebes4miorlv6ai6bf3j@
4ax.com...
>A test shows this statement is not correct for Oracle and SQL Plus:
>
>
> The test script and results are shown below; when run on Oracle 9i via
> SQL Plus it shows that a single space is stored as a single space, not
> an empty string. It also shows that an empty string is stored as a
> null.
>
> Perhaps the results are different for another client interface, but
> that would not reflect *Oracle* behavior.
>
> Breck
>
> CREATE TABLE e (
> pkey NUMBER PRIMARY KEY,
> s VARCHAR2 ( 10 ) );
>
> INSERT INTO e VALUES ( 1, 'x' );
> INSERT INTO e VALUES ( 2, '' );
> INSERT INTO e VALUES ( 3, ' ' );
> COMMIT;
>
> SELECT pkey,
> COALESCE ( s, '[NULL]' ),
> LENGTH ( s )
> FROM e ORDER BY pkey;
>
> PKEY COALESCE(S LENGTH(S)
> ---------- ---------- ----------
> 1 x 1
> 2 [NULL]
> 3 1
>
> On 15 Dec 2005 16:12:25 -0800, "M. Searer" <nospam@nospam.com> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Sponsored Links