Why fetching empty value fields as null by JOOQ?

[Sha]

Hi Lukas,

  When I try to fetch records of any table, when there is no value for a field JOOQ select statement fetching it as null.

In many databases , especially SQLServer no value is treated as empty value. 

But by JOOQ why it is null , which in turn showing NullPointerException.

And how to evaluate it to empty value ?

Regards,

~Shyam

[Lukas Eder]

Hi Shyam,

In general, jOOQ maps Java null to SQL NULL/UNKNOWN just like JDBC does as well. I wasn't aware of SQL Server providing a way to distinguish NULL from "EMPTY". Could you provide an example where you think that jOOQ should change its behaviour?

Cheers

Lukas

2014-02-03 Sha <shate...@gmail.com>:

--
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/groups/opt_out.

[Sha]

Sorry for confusion Lukas,

Let me give more details.

I have a scenarios here

Result a =  ctx.select(cn.field("ID")).from(cn)

 .join(hp)

 .on( hp.PLANE_ID.eq(cn.field("PLANE_ID")) )

 .join(cd)

 .on( (cn.field("HC1_LEAF_ID")).eq((cd.HC1_LEAF_ID).nullif(0)))

 .fetch();

In the above , there is a condition .on( (cn.field("HC1_LEAF_ID")).eq((cd.HC1_LEAF_ID).nullif(0))).

In which currently cn.field("HC1_LEAF_ID") = null. So i am not able to compare it if it is equal to cd.HC1_LEAF_ID.

Is there any easy way/build-in-method to check , if the  cn.field("HC1_LEAF_ID") not equal null then only check if it is equal to cd.HC1_LEAF_ID ? In one statement.

Thank you.

~Shyam

[Lukas Eder]

Hi Shyam,

I understand, so your cn table doesn't contain the field "HC1_LEAF_ID". From how I understand your requirement, you shouldn't join the cd table at all, in that case. This is a regular "dynamic SQL" problem, which can be resolved like this:

Table<?> t = cn.join(hp).on(hp.PLANE_ID.eq(cn.field("PLANE_ID")));

// Join the cd table only if necessary / possible

if (cn.field("HC1_LEAF_ID") != null) {

    t = t.join(cd).on(cn.field("HC1_LEAF_ID").eq(cd.HC1_LEAF_ID));

}

Result a = ctx.select(cn.field("ID"))

              .from(t)

              .fetch();

Does this answer your question?

[Sha]

Thanks for reply Lukas,

  No no ,cn table DOES contain the field "HC1_LEAF_ID", but its value is null for that row.

Hence it is giving a "Null pointer exception".

It would be very tedious job do as you shown below to check NULL condition and its not natural way.

What if ".eq(cd.HC1_LEAF_ID)" is also NULL. ? What if my query has many such conditions with 'and' ?

So there should be any easy way / function to handle this type of scenarios. Otherwise developer life would be hell.

Hope you consider this in a positive way.

Thank you.

~Shyam

[Lukas Eder]

Hi Shyam,

Otherwise developer life would be hell.

Hell is right around the corner, these days, right ;-)

I'm still not sure if I understand your question. How would a SQL statement produce a Java NullPointerException if the HC1_LEAD_ID column is available? Can you provide me with a stack trace?

So, if you're sure that the HC1_LEAF_ID is contained in your cn table, then do you want to perform a LEFT OUTER JOIN? So how would you write your statement in SQL?

[Sha]

Sorry for all confusion created Lukas,

 Here are exact details 

jOOQ Code :

 

Table cn = TEMP_CATALOG_NODE_TEMP.as("cn"); 

 Result a = ctx.select(cn.field("ID")).from(cn) .join(hp) .

           on( hp.PLANE_ID.eq(cn.field("PLANE_ID")) ) 

          .join(cd) 

           .on( ((cn.field("HC1_LEAF_ID"))).eq((cd.HC1_LEAF_ID).nullif(0)))

          .and(((cd.HC3_LEAF_ID).nullif(0)).eq(cn.field("HC3_ITEM_ID"))) 

         .where((hp.NAME).eq("BASE_MAT~~ALL~~SRC_PLNT").and(cd.PLANE_TYPE.eq("DETAIL"))) .fetch();

Exception in thread "main" java.lang.NullPointerException

at org.jooq.samples.SampleTemp_phantom_add_detail_New.main(SampleTemp_phantom_add_detail_New.java:355)

ERROR: JDWP Unable to get JNI 1.2 environment, jvm->GetEnv() return code = -2

JDWP exit error AGENT_ERROR_NO_JNI_ENV(183):  [../../../src/share/back/util.c:820]

DB records :

select cd.HC1_LEAF_ID from CATALOG_DETAIL cd where cd.PLANE_TYPE='DETAIL'

HC1_LEAF_ID

104

107

104

107

9449

15129

9449

15129

Please help me how to resolve it.

~Shyam

[Sha]

And in DB :

select * from TEMP_CATALOG_NODE_TEMP

HC1_ITEM_ID HC2_ITEM_ID HC3_ITEM_ID PLANE_ID already_exists

9449                18164                   22                    370 0

15129         18160          22                     370 0

Thank you.

~Shyam

[Sha]

sorry for my mistake Lukas.

There was a space after column hence it is not able find the column and giving error.

 i corrected it.

Sorry for troubling you.

I have one request.

I guess sooner or later jOOQ should support " UPDATE ...SELECT ...FROM clause" , otherwise going round about way for it is tedious and time consuming.

Hope this makes sense.

Thank you.

~Shyam

[Lukas Eder]

Hi Shyam,

Can you please tell me *exactly* what expression caused the NullPointerException? From what you've supplied me so far, I don't know where line 355 is. If line 355 is this line here:

 .on( ((cn.field("HC1_LEAF_ID"))).eq((cd.HC1_LEAF_ID).nullif(0)))

Then, quite clearly, cn does not hold a field called "HC1_LEAD_ID" (watch case-sensitivity!), which was my original assumption. In order for me to assess this, of course, I will also need to see what cn really is.

Shyam, it generally helps to provide as much information as possible from the beginning of such a support request, to avoid running in circles.

Regards,

Lukas

[Lukas Eder]

Hi Shyam

2014-02-19 14:20 GMT+01:00 Sha <shate...@gmail.com>:

sorry for my mistake Lukas.

There was a space after column hence it is not able find the column and giving error.

 i corrected it.

Sorry for troubling you.

That's what I thought :-) Thanks for the update

 

I have one request.

I guess sooner or later jOOQ should support " UPDATE ...SELECT ...FROM clause" , otherwise going round about way for it is tedious and time consuming.

Hmm, why "SELECT"?

UPDATE .. FROM is implemented in jOOQ 3.3.0:

https://github.com/jOOQ/jOOQ/issues/1018

Apparently, this issue was forgotten in the release notes. I'll have this fixed, soon. But as you can see, the from() method is now available from the UPDATE DSL:

http://www.jooq.org/javadoc/3.3.x/org/jooq/UpdateFromStep.html

Cheers

Lukas

[Sha]

Thanks a lot Lukas.

Let me comment on these once I implement it in my POC.

~Shyam