Empty string in H2 and Oracle

[Jürg]

Hi
in a project I am replacing Oracle with H2 and I am facing the empty
string problem of Oracle. When I save a row with an empty string,
Oracle is replacing it with null in case the column is nullable. H2
does not, what really would be correct. Even if I am using
MODE=Oracle, H2 does not behave like Oracle in this case. What can I
do to let H2 behave like Oracle, because the application software on
reading the column is now expecting Oracle's null and not H2's
(correct) empty string. Thanks for your help. Jürg

[Thomas Mueller]

Hi,

I will add this as a feature request, but I will not have time to implement it. I guess a partial implementation would be relatively simple (converting literals and parameter values), but usually in such cases there are things you don't consider first, so complete compatibility is usually quite hard. I can't tell when I will have time. You are the first to ask for this feature, so it doesn't look like it's a popular feature :-)

If I was you, I would try to change your application so that it doesn't expect this behavior. Oracle is the only database I know that works like this. But I understand it's hard to change a large application in this way.

Regards,
Thomas

[Jürg]

Hi Thomas

thanks for your answer. I understand that this is not an important
issue for H2. I made a workaround with triggers to fake Oracle's
strange behaviour.

Bye Jürg

[Bryan]

+1

I am also interested in this. Although unlike Jürg, being a long-time
Oracle DBA/programmer (since version 7), data-wise I don't understand
what an empty string could be besides a NULL (as it is no-data), and
think Oracle handles this correctly and anything else is simply
illogical. We'll just write an adapter to correct the empty strings as
they are returned for now.

Cheers,
Bryan

[Rami Ojares]

-1

Dealing with strings I encounter 3 common cases

1) NULL (I call this a NULL string)
2) "" (I call this EMPTY string)
3) "<ONE_OR_MORE_WHITESPACE_CHARACTERS>" (I call this BLANK string)

My naming is non-standard but since I encounter these 3 different situations
I have come up with different names for each of them.

It is completely context dependent whether they should be treated the
same or differently.

Of course I have nothing against acting exactly the same way as Oracle
in Oracle mode.
But generally it is not advised.

- rami

On 6.7.2011 19:44, Bryan wrote:
> +1
>

> I am also interested in this. Although unlike J�rg, being a long-time

[Lukas Eder]

+1 as far as compatibility is concerned. In Oracle, NULL and '' are
the same. Even on popular request, Oracle never corrected that well-
known "flaw". Probably, a lot of the database's internals depend on
this "feature", too. While it is clearly better to be able to
distinguish NULL and '' in a more modern database, such a
compatibility mode would be a very interesting feature.

Note also, that -among other reasons- Oracle has introduced the
VARCHAR2 type precisely to be able not to adhere to the SQL standard
for all time and remain backwards compatible, whereas the Oracle
VARCHAR type might eventually treat '' and NULL as distinct values.
This Stack Overflow question nicely treats this question:
http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null

In many senses, both Rami and Bryan are correct, depending on the
point of view :-)

[Thomas Mueller]

Hi,

> being a long-time Oracle DBA/programmer (since version 7), data-wise I don't understand what an empty string could be besides a NULL

That's understandable. You have been brainwashed :-)

Anyway, I will increase the priority of this feature request. Patches
are welcome of course! By the way: does Oracle convert empty byte
arrays to NULL as well?

Regards,
Thomas

[Lukas Eder]

> > being a long-time Oracle DBA/programmer (since version 7), data-wise I don't understand what an empty string could be besides a NULL
>
> That's understandable. You have been brainwashed :-)

We all have!

> By the way: does Oracle convert empty byte
> arrays to NULL as well?

Interestingly, yes:

---------------------------------------------------------------------------
create table lob_test(lob blob);

PreparedStatement stmt;
stmt = connection.prepareStatement("insert into lob_test
values (?)");
stmt.setBytes(1, null);
stmt.execute();

stmt.setBytes(1, new byte[0]);
stmt.execute();

stmt.setBytes(1, "abc".getBytes());
stmt.execute();

-- This doesn't work (NullPointerException)
stmt.setBlob(1, new oracle.sql.BLOB((OracleConnection)
connection));
stmt.execute();

-- Neither does this (NullPointerException)
stmt.setBlob(1, new oracle.sql.BLOB((OracleConnection)
connection), null);
stmt.execute();

-- But this does
stmt.setBlob(1, new oracle.sql.BLOB((OracleConnection)
connection, new byte[0]));
stmt.execute();

-- returns three records
select * from lob_test where lob is null;

-- returns one record
select * from lob_test where lob is not null;
---------------------------------------------------------------------------

[Lukas Eder]

For completeness, I have made another check. The closest match for
H2's ARRAY type in Oracle is the VARRAY type. An example:

------------------------------------------------------------------------
create type numbers as varray(5) of number(7);
create table numbers_table (n numbers);

insert into numbers_table values (null);
insert into numbers_table values (numbers());
insert into numbers_table values (numbers(1, 2));

-- This returns only one record
select * from numbers_table where n is null;

-- This returns two records
select * from numbers_table where n is not null;
------------------------------------------------------------------------

So a NULL VARRAY is not treated the same as an empty VARRAY.

Another hint, if you want to get compatibility right is the handling
of the CHAR type as discussed here:
http://groups.google.com/group/h2-database/browse_thread/thread/a01dd6aefd286c44

Also, in Oracle, the predicate COLUMN = '' is always false, because it
is the same as COLUMN = NULL...

This will become quite a complex task :-)

[Thomas Mueller]

Hi,

Thanks a lot! I have added the information about VARRAY and byte array
to the roadmap.

Regards,
Thomas

[Mike Cobo]

Hi Thomas,

The documentation says that for Oracle compatibility mode:

• Empty strings are treated like NULL values.

But in the following cases an empty string in Oracle compatibility mode seems not to be treated like NULL.

The following query in Oracle DB returns true, whereas in H2 with MODE=Oracle doesn't: 

SELECT true AS result WHERE '' IS NULL;

And the following query in Oracle DB returns nothing (empty result set), whereas in H2 with MODE=Oracle returns true:

SELECT true AS result WHERE '' = '';

The same behaviour appears when reading the values from a table. 

And when inserting an empty string in H2 with MODE=Oracle it is not converted to NULL whereas in Oracle DB NULL is stored and not an empty string.

The statements where executed on H2 1.3.174 (2013-10-19)

Do I need do set any other parameter for the compatibility mode to work correctly or is it simply not working  yet?

Regards,

Mike

[Thomas Mueller]

Hi,

This feature was added in version 1.3.175. You are using 1.3.174, where it is not yet available.

See also http://www.h2database.com/html/changelog.html "Issue 522: Treat empty strings like NULL in Oracle compatibility mode, patch by Daniel Gredler."

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Mike Cobo]

Hi Thomas

You're right, with version 1.3.175 an empty string is treated like NULL in Oracle compatibility mode :-)

What is strange, is that after closing the connection with Oracle compatibility mode and reconnecting to H2 (Embedded or Generic) the following statement also retruns TRUE:

select true as result where '' is NULL;

Only after a shutdown of h2 the behavior in H2 (Embedded or Generic) is as expected, i.e. the statement above returns nothing. This not only happens when using the web H2 console but also when connecting from another client (eg. DBeaver).

Regards,

Mike

[Noel Grandin]

On 2014-02-18 13:06, Mike Cobo wrote:
>
> What is strange, is that after closing the connection with Oracle compatibility mode and reconnecting to H2 (Embedded or
> Generic) the following statement also retruns TRUE:
>

The compatibility mode is global to the database, so the first connection sets it, and it stays that way afterwards.

Basically, this feature is for unit-testing only, not for production use.

[Mike Cobo]

Hi Noel,

That explains it. We use H2 for unit-testing and Oracle (or SQL Server) is the production database.

- Mike