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

[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.

[Broadchoice]

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

[dave_cozens]

Try <cfdump var="#rec#"> and work your way down...

[Broadchoice]

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

[MikerRoo]

Add <cfdump var="#userResult#"> just above the loop.
Then attach the results here or provide a link to the dump or a screen shot.

[Broadchoice]

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">

[MikerRoo]

Oops. Sorry.
You should dump userResult.

[Broadchoice]

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)

[Broadchoice]

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)

[MikerRoo]

Ok. Try the attached code instead of the original loop. (May need some tweaking since I don't have access to the COM collection.)

[Broadchoice]

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.

[MikerRoo · Accepted solution]

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.

[Broadchoice]

It works!!!

You are the best MikerRoo!

rb