8 Replies Latest reply on Mar 24, 2014 10:05 AM by Sai Gujja

    Field with NULL value is replaced by a non-NULL field during modify

    Sai Gujja Newbie

      We are using the Database Explorer plugin in eclipse to modify two fields, of which one had a value that we were trying to remove and adding a value to the other one, which did not have a value to begin with. Let's say the fields are CONSID and EXPDATE. CONSID is a vachar that accepts a one letter word, such as 's','a' etc. EXPDATE is a date field. CONSID had a value in it already before we started our modify function and EXPDATE did not have any value in it. So our modification included removing the value for CONSID(making it NULL) and adding a date for EXPDATE. So the update Query we sent looked like:


      Execute UPDATE tssadmingrp=acids,host=de30_de29,o=cai,c=us SET EXPDATE ={d '2013-12-12'}, CONSID = NULL WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSACID,tssadmingrp=acids,host=de30_de29,o=cai,c=us"'


      We use a extended LDAP translator to send these modify queries to LDAP server. The update query that is passed over (the one created in our translator)seemed right, but the final query that reached LDAP was wrong. The values that were being sent to LDAP were same for CONSID and EXPDATE (both of them had date values in them). Since LDAP doesn't recognize date value in CONSID, it failed.


      This is where we don't understand what is going wrong between the update query being generated and that is sent to LDAP. Can you please advice us?

        • 1. Re: Field with NULL value is replaced by a non-NULL field during modify
          Steven Hawkins Master

          Can you post your extension or detail how/when it is modifying the source query?

          • 2. Re: Field with NULL value is replaced by a non-NULL field during modify
            Sai Gujja Newbie

            I am issuing the following SQL from Teiid Designer Plugin on Eclipse.

            UPDATE security_ldap.BASEUSER SET EXPDATE = {d '2014-05-05'}, CONSID = NULL WHERE security_ldap.BASEUSER.DN = 'TSSWOLF' and SECURITY_LDAP.BASEUSER.SYSID = 'DE29'

            (Note: Prior to issuing this command, I had a value 'A' in CONSID field(it is a varchar), I am trying to null the value out now)


            This will go through the extended translator we have for LDAP and convert the SQL to the DN, attributes, user information, in a way LDAP understands and sends it to the LDAP we have running in the background.

            So the above query is converted to the following for LDAP server.




            replace: EXPDATE

                one value, length 10

                 field(EXPDATE), value(2014-05-05)

            replace: CONSID

                one value, length 10

                 field(CONSID), value(2014-05-05)




            As you can see from the above LDAP information, CONSID and EXPDATE both have date in their value. From the SQL query we sent, notice that we sent CONSID=NULL, but in the outcome we have CONSID=2014-05-05.


            Why was this converted from a NULL to date value that is for the other field, is something I am trying to understand.

            • 3. Re: Field with NULL value is replaced by a non-NULL field during modify
              Steven Hawkins Master

              > From the SQL query we sent, notice that we sent CONSID=NULL, but in the outcome we have CONSID=2014-05-05.


              I am still somewhat confused by your scenario/description.  Perhaps it would be best to start at the beginning.  Use an unmodified ldap translator and a source model insert with no data roles etc. to first validate that Teiid is working the way you would expect.


              You can see in the LDAPUpdateExecution that the translator is quite straight-forward in producing - https://github.com/teiid/teiid/blob/master/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPUpdateExecution.java


              Can you provide a query plan or command log showing the pushdown command sent to the translator?


              Otherwise you'll have to being posting your customization that would assist in reproducing what you are seeing.

              • 4. Re: Field with NULL value is replaced by a non-NULL field during modify
                Sai Gujja Newbie



                Sorry for the delay in my response. Was caught up with other work.

                Coming back to this issue, here is what we do to reproduce it:


                We are modifying two fields from our home grown application (UI), which talks to TEIID table, which then talks to our another home grown product via CA LDAP server.

                Here is the snippet of our initial UI.


                As you can see, the console ID field is empty, and there is a value in Expiration Date.


                The next step we do is put a value(Y) for Console ID and remove the Expiration Date.


                We send this request through the UI to LDAP. It goes in and modifies the changed fields successfully.


                As you can see, the value in Console ID is Y and nothing is put in Expiration Date.


                Now, when we change those again, by removing Y from Console ID and put a Date in Expiration date as follows:


                …and submit the request. It now fails with an LDAP message, which is a valid error, based on what is going to LDAP from UI.

                This is the error.



                When the request is submitted, here is what we see in the UI log, as what is coming through TEIID.

                Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET EXPDATE = {d '2014-12-12'}, CONSID = NULL WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"' against org.teiid.translator.ldap.LDAPUpdateExecution@305a822e with userid: allje05

                But when we see in the LDAP log, here is what is coming through LDAP.

                dn (tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US)

                 0000:  30 1c 0a 01 02 30 17 04  07 45 58 50 44 41 54 45   0....0...EXPDATE                                                                                                                                                        
                 daemon: select: listen=6 active_threads=0 tvp=NULL                                                                                                                                                                                                                
                 0010:  31 0c 04 0a 32 30 31 34  2d 31 32 2d 31 32 30 1b   1...2014-12-120.                                                                                                                                                                                      
                 daemon: select: listen=7 active_threads=0 tvp=NULL                                                                                                                                                                                                                
                 0020:  0a 01 02 30 16 04 06 43  4f 4e 53 49 44 31 0c 04   ...0...CONSID1..                                                                                                                                                                                      
                 0030:  0a 32 30 31 34 2d 31 32  2d 31 32                  .2014-12-12                                                                                                                                                                                           
                 conn=1738 op=6 modifications:                                                                                                                                                                                                                                     
                 replace: EXPDATE                                                                                                                                                                                                                                                 
                 one value, length 10                                                                                                                                                                                                                                            
                 replace: CONSID                                                                                                                                                                                                                                                  
                 one value, length 10                           

                As you can see here, CONSID and EXPDATE, both are getting date in them. This is what is inputted from the UI to LDAP, even though we see the correct SQL.


                One thing we noticed during our testing is very interesting and thought this might help in determining the problem.

                We entered the same query in two different ways from the TEIID Designer plugin, and got two different results.

                The first case is when we entered:

                Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET CONSOLE=NULL, EXPDATE = {d '2014-12-12'} WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"'

                This works, I mean the query is passed in correctly to LDAP, and we get the right results back.

                Here, notice the CONSOLE=NULL first and the EXPDATE, with a value second, just before the "WHERE" keyword.

                The second case is when we entered:

                Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET CONSOLE=Y, EXPDATE = NULL WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"'

                This doesn't work and gives the error described above.

                Here, notice the CONSOLE with value is first and EXPDATE with NULL second and just before "WHERE" keyword.

                We also have a third case, where we flipped the order.

                Execute UPDATE tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US SET EXPDATE = {d '2014-12-12'}, CONSOLE=NULL WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = '"tssacid=TSSWOLF,tssadmingrp=acids,HOST=DE30_DE29,O=CAI,C=US"'

                This case fails as well. Notice the NULL at the end before "WHERE" keyword.

                I am not sure if this is the case, but some how having a NULL field at the end, before the "WHERE" keyword seems to cause this problem.

                We had a different error an year ago regarding MALFORMED DATABASE ERROR, as discussed in:

                Re: Malformed Attribute Value - LDAP Translator - is this a bug?

                I could be wrong, could this fix have caused this issue now? I can't remember exactly if this used to work before, but we certainly started noticing this behavior after the Patch was applied.





                • 5. Re: Field with NULL value is replaced by a non-NULL field during modify
                  Steven Hawkins Master

                  Yes, I see the issue now as well.  The null value is picking up the value of the previous set.  Can you log this as an issue?


                  Thanks for providing a detailed post,


                  • 7. Re: Field with NULL value is replaced by a non-NULL field during modify
                    Sai Gujja Newbie

                    Thank you Steve,


                    We originally had this issue logged for the above problem. It was closed at that time, as we did not have enough details.