jooq condition to select empty and null values for column

[abhijit...@gmail.com]

Hi,

I have a table in H2 having column of type string with null as well as empty value.

So like to know jooq condition to select empty values for a row.

jooq --> field.isNull() check allows me to select null value

however for empty value tried following, but its not working.

field.length.eq(0) or field.eq("")

Could you please provide any pointers, whats going wrong here?

Thanks

[Lukas Eder]

Hello,

Both field.length().eq(0) and field.eq("") would be correct

Can you provide some sample data to reproduce this issue?

Also, in case you're using H2 in Oracle compatibility mode, there's a chance that you're using Oracle behaviour, in case of which empty strings and NULL would be the same thing. See:

http://www.h2database.com/javadoc/org/h2/engine/Mode.html?highlight=oracle&search=oracle#treatemptystringsasnull

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[abhijit...@gmail.com]

isNull() though form correct SQL query for H2 - it returns '0' records.

same query when executed directly using H2 UI console, returns correct records.

[abhijit...@gmail.com]

Please find sample H2 DB file attached.

or can use

1. CREATE TABLE SAMPLE (USER VARCHAR (20))

2. then insert, '',NULL,'xyz' value.

both empty and null conditions are not working for me via JOOQ API, however SQL formed by JOOQ does execute correctly on H2 Console and return results.

even tried - DSLContext.select("select user from SAMPLE where user is null") is not working.

Thanks

[Lukas Eder]

I'm very sorry, I don't fully understand the issue you're experiencing yet.

When you write

even tried - DSLContext.select("select user from SAMPLE where user is null") is not working.

Did you try your query exactly like this? There is no "select(String)" method on the DSLContext type. Perhaps you meant "DSLContext.fetch()"?

In any case, please provide us with all necessary steps to help you. What I'm still missing to understand your issue is:

What (exact) jOOQ queries did you write? What did they return? What did you expect them to return?

Best,

Lukas

2014-11-04 21:07 GMT+01:00 <abhijit...@gmail.com>:

Please find sample H2 DB file attached.

or can use

1. CREATE TABLE SAMPLE (USER VARCHAR (20))

2. then insert, '',NULL,'xyz' value.

both empty and null conditions are not working for me via JOOQ API, however SQL formed by JOOQ does execute correctly on H2 Console and return results.

even tried - DSLContext.select("select user from SAMPLE where user is null") is not working.

Thanks

On Tuesday, November 4, 2014 11:14:20 AM UTC-8, Lukas Eder wrote:

Hello,

Both field.length().eq(0) and field.eq("") would be correct

Can you provide some sample data to reproduce this issue?

Also, in case you're using H2 in Oracle compatibility mode, there's a chance that you're using Oracle behaviour, in case of which empty strings and NULL would be the same thing. See:

http://www.h2database.com/javadoc/org/h2/engine/Mode.html?highlight=oracle&search=oracle#treatemptystringsasnull

Cheers,

Lukas

2014-11-04 19:51 GMT+01:00 <abhijit...@gmail.com>:

Hi,

I have a table in H2 having column of tyI'm pe string with null as well as empty value.

So like to know jooq condition to select empty values for a row.

jooq --> field.isNull() check allows me to select null value

however for empty value tried following, but its not working.

field.length.eq(0) or field.eq("")

Could you please provide any pointers, whats going wrong here?

Thanks

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Abhijit Dhariya]

sorry, yes I mean - DSLContext.fetch(<SQL>)

And here is what I'm trying :

final SelectJoinStep<Record> step = dslContext

            .select( (Collection<? extends Field<?>>) (Collection) fields ).from( table( USER ) );

Condition condition  = field("user").length().eq(0); 

step.where(condition)

step.fetch()

In this case this case expecting fetch() to return Records matching for empty user.

which does not seems working for H2 DB.

However if ran SQL query on H2 console - select * from user where user='' 

return results (or using LENGTH function of H2 to compare empty string).

In addition to empty value check, field.isNull() is also not working.

And like above - select * from user where user is null --> works fine directly on H2 console.

In short trying to find out null and empty user values in DB.

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/NxD98CsKpHs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[abhijit...@gmail.com]

Could you please let me know if there is solution to get around this issue?

Thanks

Lukas

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

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/NxD98CsKpHs/unsubscribe.

To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

Hello,

I'm sorry, I really cannot reproduce this issue. I created the table you suggested:

DROP TABLE IF EXISTS SAMPLE;

CREATE TABLE SAMPLE (USER VARCHAR (20));

INSERT INTO SAMPLE VALUES('');

INSERT INTO SAMPLE VALUES(NULL);

INSERT INTO SAMPLE VALUES('xyz');

I ran these queries (trying to guess what you really meant by "fields", and table( USER ):

// Query 1

List fields = Arrays.asList(field("user"));

SelectJoinStep<Record> step = create()

    .select( fields ).from( table( "sample" ) );

Condition condition  = field("user").isNull();

step.where(condition);

System.out.println(step.fetch());

// Query 2

List fields = Arrays.asList(field("user"));

SelectJoinStep<Record> step = create()

    .select( fields ).from( table( "sample" ) );

Condition condition  = field("user").length().eq(0);

step.where(condition);

System.out.println(step.fetch());

The queries produced the following DEBUG log output:

// Query 1

20:33:22,913 DEBUG [org.jooq.tools.LoggerListener                     ] - Executing query          : select user from sample where user is null -- SQL rendered with a free trial version of jOOQ 3.5.0

20:33:22,973 DEBUG [org.jooq.tools.StopWatch                          ] - Query executed           : Total: 122.35ms

20:33:24,838 DEBUG [org.jooq.tools.LoggerListener                     ] - Fetched result           : +------+

20:33:24,838 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : |user  |

20:33:24,838 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : +------+

20:33:24,838 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : |{null}|

20:33:24,838 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : +------+

20:33:24,838 DEBUG [org.jooq.tools.StopWatch                          ] - Finishing                : Total: 1.986s, +1.864s

// Query 2

20:35:33,447 DEBUG [org.jooq.tools.LoggerListener                     ] - Executing query          : select user from sample where char_length(cast(user as varchar)) = cast(? as int) -- SQL rendered with a free trial version of jOOQ 3.5.0

20:35:33,447 DEBUG [org.jooq.tools.LoggerListener                     ] - -> with bind values      : select user from sample where char_length(cast(user as varchar)) = 0 -- SQL rendered with a free trial version of jOOQ 3.5.0

20:35:33,491 DEBUG [org.jooq.tools.StopWatch                          ] - Query executed           : Total: 122.772ms

20:35:34,931 DEBUG [org.jooq.tools.LoggerListener                     ] - Fetched result           : +----+

20:35:34,931 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : |user|

20:35:34,931 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : +----+

20:35:34,931 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : |    |

20:35:34,931 DEBUG [org.jooq.tools.LoggerListener                     ] -                          : +----+

20:35:34,931 DEBUG [org.jooq.tools.StopWatch                          ] - Finishing                : Total: 1.554s, +1.432s

Everything behaves correctly...

Are you sure that...

- You're querying the correct database?

- The database has all data in it and you committed it?

- You didn't confuse some USER and SAMPLE tables in your examples?

- Etc...

Cheers

Lukas