13 Replies Latest reply on Oct 31, 2006 8:59 AM by Broadchoice

    Null Values in COM Collections

    Broadchoice
      Please see this code example:

      ***

      <!--- Initialize Salesforce.com account --->
      <cfset sfUserName = "xxx">
      <cfset sfPassword = "xxx">

      <!--- Initialize Salesforce API --->
      <cfoutput>
      <cfobject action="create" type="COM" class="SForceOfficeToolkit3.SForceSession3.1" name="sfdc"/>
      <cfset x=sfdc.SetServerUrl("https://www.salesforce.com/services/Soap/c/7.0")/>
      <cfset x=sfdc.login("#sfUserName#","#sfPassword#")/>
      </cfoutput>

      <!--- Pass user last name --->
      <cfset userLastName = "Barr">

      <!--- Query Salesforce for last name --->
      <cfset soql="select ID,FirstName, LastName,accountid,email from Contact where lastname = '" & #userLastName# & "'"/>
      <cfset userResult = sfdc.query(soql,false) />

      <!--- Display results --->
      <cfloop collection="#userResult#" item="rec">
      <cfoutput>
      #rec.item("ID").value#<br />
      #rec.item("FirstName").value#<br />
      #rec.item("LastName").value#<br />
      #rec.item("accountid").value#<br />
      #rec.item("email").value#<br />
      </cfoutput>
      </cfloop>

      ***

      When I loop through the COM collection, the value of accountid is NULL. This is expected as this record has no
      Account ID.

      When I try to run this code I get the following exception:

      ***

      Element VALUE is undefined in a Java object of type class coldfusion.runtime.com.ComProxy referenced as


      The error occurred in C:\CFusionMX7\wwwroot\sftest\sfSearch.cfm: line 25

      23 : #rec.item("FirstName").value#<br />
      24 : #rec.item("LastName").value#<br />
      25 : #rec.item("accountid").value#<br />
      26 : #rec.item("email").value#<br />
      27 : </cfoutput>

      ***

      How can I handle NULL values so I dont get an exception?

      Thanks so much for helping.

      rb
        • 1. Re: Null Values in COM Collections
          dave.cozens
          Basically I guess you're going to have to check for
          #isDefined("rec.item.#accountid#.value")#

          Have you tried doing a <cfdump var="#rec.item("accountid")#"> to see what's in it, since it seems from the error message that it exists but doesn't have a value.

          • 2. Re: Null Values in COM Collections
            Broadchoice Level 1
            Thanks Dave,

            I have tried IsDefined, but the error gets thrown just trying to evaluate the variable. I have not tried a CFDUMP, but will. I suspect the same thing will happen. Whenever I try to reference that variable in any way, CF throws the same exception.

            rb
            • 3. Re: Null Values in COM Collections
              dave.cozens Level 1
              Try <cfdump var="#rec#"> and work your way down...
              • 4. Re: Null Values in COM Collections
                Broadchoice Level 1
                Well...still no luck.

                If #rec.item("accountid").value#<br /> contains a value then it display fine in the loop, but if there is no value in that field, I get an exception. No data seems to mean undefined.

                I am stumped.

                rb
                • 5. Re: Null Values in COM Collections
                  MikerRoo Level 1
                  Add <cfdump var="#userResult#"> just above the loop.
                  Then attach the results here or provide a link to the dump or a screen shot.
                  • 6. Re: Null Values in COM Collections
                    Broadchoice Level 1
                    If I attach the <cfdump var="#rec#"> before the loop the variable is undefined as the loop defines it.

                    <!--- Display results --->
                    <cfdump var="#rec#">

                    <cfloop collection="#userResult#" item="rec">
                    <cfoutput>
                    #rec.item("ID").value#<br />
                    #rec.item("FirstName").value#<br />
                    #rec.item("LastName").value#<br />
                    #rec.item("accountid").value#<br />
                    #rec.item("email").value#<br />
                    </cfoutput>
                    </cfloop>

                    ***

                    Variable REC is undefined.


                    The error occurred in C:\CFusionMX7\wwwroot\sftest\sfSearch.cfm: line 20

                    18 :
                    19 : <!--- Display results --->
                    20 : <cfdump var="#rec#">
                    21 :
                    22 : <cfloop collection="#userResult#" item="rec">


                    • 7. Re: Null Values in COM Collections
                      MikerRoo Level 1
                      Oops. Sorry.
                      You should dump userResult.
                      • 8. Re: Null Values in COM Collections
                        Broadchoice Level 1
                        Here is the dump if in the loop for <cfdump var="#rec#">:

                        object of coldfusion.runtime.com.ComProxy
                        {3f1d17c3-69d6-4b52-aba9-241bf59e0d03}
                        Methods AddRef([out, retval]UI4)
                        GetIDsOfNames([in]USERDEFINED, [in]I1, [in]UINT, [in]UI4, [out]I4, [out, retval]VOID)
                        GetTypeInfo([in]UINT, [in]UI4, [out]VOID, [out, retval]VOID)
                        GetLayoutXML([in]BSTR, [out, retval]BSTR)
                        QueryInterface([in]USERDEFINED, [out]VOID, [out, retval]VOID)
                        Clone([out, retval]USERDEFINED)
                        RecordTypeIdAvailable([in]BSTR, [out, retval]BOOL)
                        Item(VARIANT, [out, retval]USERDEFINED)
                        Update([out, retval]VOID)
                        RecordTypeLayoutId([in]BSTR, [out, retval]BSTR)
                        Delete([out, retval]VOID)
                        Release([out, retval]UI4)
                        Refresh([out, retval]VOID)
                        Create([out, retval]VOID)
                        GetTypeInfoCount([out]UINT, [out, retval]VOID)
                        Invoke([in]I4, [in]USERDEFINED, [in]UI4, [in]UI2, [in]USERDEFINED, [out]VARIANT, [out]USERDEFINED, [out]UINT, [out, retval]VOID)

                        Put Properties Tag([in]VARIANT, [out, retval]VOID)

                        Get Properties Activateable([out, retval]BOOL)
                        UrlEdit([out, retval]BSTR)
                        Replicateable([out, retval]BOOL)
                        Queryable([out, retval]BOOL)
                        Fields([out, retval]VARIANT)
                        RecordTypeSelectRequired([out, retval]BOOL)
                        Tag([out, retval]VARIANT)
                        Undeletable([out, retval]BOOL)
                        ChildRelationships([out, retval]VARIANT)
                        KeyPrefix([out, retval]BSTR)
                        Error([out, retval]USERDEFINED)
                        BulkEditLayout([out, retval]BSTR)
                        PluralLabel([out, retval]BSTR)
                        Label([out, retval]BSTR)
                        Updateable([out, retval]BOOL)
                        UrlDetail([out, retval]BSTR)
                        DefaultRecordTypeID([out, retval]BSTR)
                        Custom([out, retval]BOOL)
                        HasServerData([out, retval]BOOL)
                        Createable([out, retval]BOOL)
                        ObjectType([out, retval]BSTR)
                        Layoutable([out, retval]BOOL)
                        Searchable([out, retval]BOOL)
                        LayoutXML([out, retval]BSTR)
                        Deletable([out, retval]BOOL)
                        UrlNew([out, retval]BSTR)
                        ErrorMessage([out, retval]BSTR)

                        • 9. Re: Null Values in COM Collections
                          Broadchoice Level 1
                          Here is the dump for <cfdump var="#userResult#">

                          object of coldfusion.runtime.com.ComProxy
                          {650cc549-be8c-4f03-9153-62c41e4197c9}
                          Methods GetTypeInfoCount([out]UINT, [out, retval]VOID)
                          QueryInterface([in]USERDEFINED, [out]VOID, [out, retval]VOID)
                          Invoke([in]I4, [in]USERDEFINED, [in]UI4, [in]UI2, [in]USERDEFINED, [out]VARIANT, [out]USERDEFINED, [out]UINT, [out, retval]VOID)
                          GetTypeInfo([in]UINT, [in]UI4, [out]VOID, [out, retval]VOID)
                          Release([out, retval]UI4)
                          AddRef([out, retval]UI4)
                          GetIDsOfNames([in]USERDEFINED, [in]I1, [in]UINT, [in]UI4, [out]I4, [out, retval]VOID)

                          Put Properties Tag([in]VARIANT, [out, retval]VOID)

                          Get Properties Size([out, retval]I4)
                          Tag([out, retval]VARIANT)
                          ErrorMessage([out, retval]BSTR)
                          _NewEnum([out, retval]UNKNOWN)
                          EntityType([out, retval]BSTR)
                          Error([out, retval]USERDEFINED)

                          • 10. Re: Null Values in COM Collections
                            MikerRoo Level 1
                            Ok. Try the attached code instead of the original loop. (May need some tweaking since I don't have access to the COM collection.)
                            • 11. Re: Null Values in COM Collections
                              Broadchoice Level 1
                              Thank you so much for the help. I am still getting hung up on the empty accountID string:

                              ***

                              Element VALUE is undefined in ZDAT.


                              The error occurred in C:\CFusionMX7\wwwroot\sftest\sfSearch.cfm: line 27

                              25 : if (IsDefined ("zDat"))
                              26 : {
                              27 : sResult = zDat.value;
                              28 : }
                              29 :

                              ***

                              Somehow if the field is empty, the variable type is not being defined. I know that its a null value in the database of the webservice, but the null values are not passing well at all. Again, thank your helping me.
                              • 12. Re: Null Values in COM Collections
                                MikerRoo Level 1
                                Ok change this: if (IsDefined ("zDat"))
                                to this: if (IsDefined ("zDat") AND IsDefined ("zDat.value")).

                                This should either work or return nothing but nulls.

                                BTW, you are using COM to access SalesForce?

                                It should be easier with CF7 web calls and has already been done.
                                See http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=7&threadid=781179, etc.
                                • 13. Re: Null Values in COM Collections
                                  Broadchoice Level 1
                                  It works!!!

                                  You are the best MikerRoo!

                                  rb