notBlank condition for Oracle returns always zero records

[claudiobosticco]

SmartClient Version: v11.1p_2017-09-13/Enterprise Development Only (built 2017-09-13)

Hello, while using the 'is not null' from an inline operator filter, I noticed that the query issued for a 'text' field (varchar2), with Oracle database (11g), is like this:
(my_table.text_field is not null AND my_table.text_field <> '')

but with the latter condition the query returns always zero records. Is it a bug, or am I missing some setting for SQLDataSources or other?

[Isomorphic]

As written that SQL snippet expresses "not null and not empty string", which is what's intended for the isNotBlank operator and should match any non-blank string. What are you saying is wrong with it?

[claudiobosticco]

the problem is that that query results in zero records fetched, even if there are many records with the text field different from null and from empty string.

[Isomorphic]

In Oracle varchar2 data type treats empty strings as nulls. You should probably use notNull instead of notBlank operator on fields of varchar2 type.

[Blama]

Hi Isomorphic,

I agree with claudiobosticco that this is a problem. I just tested it here and it is happening for me as well.
See also this post of yours, which is slightly related.

IMHO it would be correct to generate only IS NOT NULL as condition for notBlank serverside when the DB is Oracle.
The alternative would be that for every project on Oracle, the developers would need to do such a change to ListGrid where IMHO it should work out of the box.

Best regards
Blama

[Isomorphic]

We're looking for a way to repair isBlank for Oracle. The problem appears to be that Oracle behaves differently for varchar2 vs varchar, and we don't immediately see a way to write SQL that works for both, and further we don't want to require that you tell us which type you are using.

[claudiobosticco]

Hello, thanks for the effort, but I want to show you the behaviour on my oracle instance (11g):

sql> CREATE TABLE table_test (
  varchar_field  VARCHAR(10),
  varchar2_field VARCHAR2(10),
  char_field     CHAR(10)
)
[2017-09-20 15:06:16] completed in 17ms
sql> INSERT INTO table_test (varchar_field, varchar2_field, char_field) VALUES ('foo', 'bar', 'foobar')
[2017-09-20 15:06:16] 1 row affected in 16ms
sql> SELECT *
FROM table_test
WHERE (varchar_field IS NOT NULL AND varchar_field <> '')
      OR (varchar2_field IS NOT NULL AND varchar2_field <> '')
      OR (char_field IS NOT NULL AND char_field <> '')
[2017-09-20 15:06:16] 0 rows retrieved in 39ms (execution: 21ms, fetching: 18ms)

It seems that the condition

field IS NOT NULL AND field <> ''

fails to retrieve records for CHAR, VARCHAR and VARCHAR2.
Or is it something specific to my oracle instance?

[Blama]

Hi Isomorphic,

using claudiobosticco sample code, I can reproduce on my dev machine (11.2).

Best regards
Blama

[Isomorphic]

This is fixed and is available for download in nightly builds since Sep 27. Let us know please how it worked for you.

When working with text column types, Oracle treats empty string as null, so Oracle will actually translate the SQL expression column<>"" into column<>NULL, even though the empty string is being supplied as a literal and is not a column value. Since a comparison of NULL with anything is false, column<>NULL doesn't match anything. The workaround is to check string length greater than 0 instead.

[claudiobosticco]

SmartClient Version: v11.1p_2017-09-28/Enterprise Deployment (built 2017-09-28)

Hello, I've just tried but for the notBlank operator I still see a generated where condition as:

JTK_TITOLARI.USERID IS NOT NULL AND JTK_TITOLARI.USERID<>''

[Isomorphic]

Please try again with September 29 nightly build. This was ported to 11.1 later. I mixed up which version you are using. Sorry for that.

[claudiobosticco]

SmartClient Version: v11.1p_2017-09-30/Enterprise Deployment (built 2017-09-30)

I can confirm it's working, thank you very much.