cfquery sql update loop problem

Report · Jul 17, 2009

Trying something a bit tricky, need some ideas.

I've got an array of objects that I pass to a cfc from flex, I'm trying to save this array to the database.

Here's what I have that doesn't work.

<cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#len(objDetails)#">
          #objDetails.Name# = '#objDetails.Value[1]#' ,
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

The error I'm getting is

faultCode:Server.Processing faultString:'Unable to invoke CFC - Complex object types cannot be converted to simple values.' faultDetail:'The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values. The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a cfif tag.'

I have the feeling it's due to the equals symbol (and maybe the comma at the end too). I've tried putting cfoutput tags around it, using ToString method... running out of ideas.

Both #objDetails.Name# and #objDetails.Value[1]# evaluate correctly.

Thanks.

Report · Jul 17, 2009

To escape the comma, put field_x = field_x after your loop.

For the error you are getting, you say you have an array of objects. That being the case, use arraylen(), not len() to in your cfloop tag.

Report · Jul 17, 2009

plz try the following

if "objDetails" is an array then use "arraylen"

objDetails

<cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#arraylen(objDetails)#">
          #objDetails.Name# = '#objDetails.Value[1]#' ,
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

Report · Jul 17, 2009

plz try the following

if "objDetails" is an array then use "arraylen"

objDetails

<cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#arraylen(objDetails)#">
          #objDetails.Name# = '#objDetails.Value[1]#' ,
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

Report · Jul 17, 2009

Hi guys thanks for the replies.

Yes I'd figured out it was ArrayLen which was the problem in the error message.

I still have problems, I think it's because Coldfusion doesn't like that equals sign... so I tried

<cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
          #objDetails.Name# & " = '" & #objDetails.Value[1]# & "',"
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

However this is giving me the following error

faultCode:Server.Processing faultString:'Unable to invoke CFC - You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.' faultDetail:''

Which also comes up when I just have

<cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
          #objDetails.Name# = '#objDetails.Value[1]#' ,
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

So maybe it's nothing to do with the equals sign.

Any suggestions?

Report · Jul 17, 2009

try this

<cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
          #objDetails.Name#   = '#objDetails.Value[1]#',
     </cfloop>

Report · Jul 17, 2009

Please make sure all fields should be string

Report · Jul 17, 2009

That's what I tried (bottom of my last post)

Report · Jul 17, 2009

<cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
          #objDetails.Name# & " = '" & #objDetails.Value[1]# & "',"
     </cfloop>

this will add an aditional "," in the end of the query

eg -field1 = '1', field2 = '2', field3 = '3', field4 = '4',

comma at the end of the string

Report · Jul 17, 2009

Yeah I knew about that, but figured that would be a SQL error.

I've changed it to

<cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
     <cfset test = #objDetails.Name# & " = '" & #objDetails.ValueTest[1]# & "'">
     <cfif i is not (ArrayLen(objDetails)-1)>
        <cfset test = test&",">
     </cfif>
     #test#
</cfloop>

With no change in outcome...

Report · Jul 17, 2009

try this


<cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
     <cfset test = #objDetails.Name# & " = '" & #objDetails.ValueTest[1]# & "'">
     <cfif i LT (ArrayLen(objDetails))>
        <cfset test = test&",">
     </cfif>
     #test#
</cfloop>

Report · Jul 17, 2009

Ok thanks, that's a better way to write it, but the comma at the end isn't the reason for my error message.

I'm still getting this error regardless:

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.

Report · Jul 17, 2009

Two questions for you m...

1. What results do you get if you add the following to the template/page that attempts to the do the update:

For me, I like to actually see what CF thinks my array of objects is (specifically the objects themselves) when I've dealt with such problems.

2. What do the AS3 classes look like (your Flex objects, that is) and how are you sending them to CF (Remote Objects, HTTP service calls, etc.)?

Report · Jul 17, 2009

I've not figured out how to see the output of cfdump through flex, which makes debugging a problem.

Though I've managed to use it when doing simple things, and just opening my browser to http://localhost:8500/test.cfc?method=getObjDetail&objid=13120

But I'm not sure how to do it when I have an array as an input...

There must be a better strategy for debugging coldfusion in a Flex app?

To answer the other part of your question; I'm passing an ArrayCollection from Flex. Basically I'm populating a advanceddatagrid dynamically, then trying to save the contents, so I pass the advanceddatagrid.dataProvider to the cfc and it seems to work.

From the debugging I've done I can access the data.

For example getting the cfc to return #objDetails[1].Name# or #objDetails[1].ValueTest[1]# as a result.

Thanks for the help, hope that answers your questions.

Report · Jul 17, 2009

To dump cf info from flex, try something like this in your cfc.

use cffile to create a file that contains the cfdump stuff and anything else you want to see.

open that file with javascript window.open

I've never actually tried this myself, but the theory seems ok.

Report · Jul 21, 2009

Thanks for your help everyone.

I managed to dump the data coming from the cfc and it looks correct. The value field isn't stuck in a array at this point, so it must be doing this on the flex side.

For those interested I've started a new thread in the Flex section http://forums.adobe.com/thread/465375 to hopefully solve this problem

Cheers.

Report · Jul 17, 2009

Craig

You're on to the right lines. I've figured out the problem.

For some reason when I generate the array to populate the datagrid, the Value, instead of just being a String, is actually an Array with one element (the string).

I couldn't resolve this problem, but I was able to continue so I ignored it.

That's why I was using


objDetails.Value[1]

So the problem I was having is because when I edited a value in the datagrid, it would become a string (not the array which I was expecting).

Basically the problem is the way I populate the datagrid. So here it is...

          <cfquery name="objDetail" datasource="test">
               SELECT * FROM ObjTable WHERE OBJID=
                <cfqueryparam value="#objID#" CFSQLType='CF_SQL_INTEGER'>
           </cfquery>
          <cfset columnList = GetMetaData( objDetail )>
          <!-- <cfset columnList = objDetail.getColumnList()> -->
          <cfset rotatedQuery = QueryNew("Name, Value") >
          <cfset newRow = QueryAddRow(rotatedQuery, len(columnList))>
          <cfloop index="i" from="1" to="#len(columnList)#">
               <cfset temp = QuerySetCell(rotatedQuery,"Name",#columnList#,i)>
               <cfset temp = QuerySetCell(rotatedQuery,"Value",#objDetail[columnList]#,i)>
          </cfloop>

In bold the relevant parts. I couldn't figure out why it was becoming an array, whereas Name wasn't.

So this is my problem now!

Ps. in the above I am rotating the result from the database so that rows becomes columns

Report · Jul 17, 2009

Sorry about that, m! That's what happens when I type faster than I think ... I forget to note that you're in Flex, not CF so dump and abort don't work. My bad.

Here's what I 'meant' to write !

In your CFML page:

</cfsavecontent>

<cffile action="write" output="#mydump#" file="/Some/Path/To/A/Place/On/Your/System/stupid_flex_and_cf.htm" />l

This would generate an HTML page you can use to see exactly what CF is getting from Flex.

Okay, back to Flex. Can you post some of your MXML/AS3 code for the following:

1. The call and handler when you get the data from CF to populate into the grid

2. The process (getting grid data to send to CF) and call when you send the grid data to CF

My sense is that it's something in item 2 causing your issue but it's good to check all possibilities!

Oh, one final question: What does your ArrayCollection consist of? In other words, what type of objects are you storing in it for the grid (AS class, etc.)

Report · Jul 17, 2009

I simply use one line of code in the result handler for getting the grid data

adgDetail.dataProvider = ev.result;

So I think it's a problem in the coldfusion code from my last post.

And, again, when I'm saving the grid data, I pass through

{adgDetail.dataProvider}

as the argument in a remoteobject call.

But I'm pretty certain the problem is before coldfusion even send the data back to flex. In other words the code from my last post where I'm rotating the query. By the way the next line of that code is simply returning the rotatedQuery.

So for some reason it's putting the data of Value in an array, instead of just making it a String.

Perhaps the problem lies here:

#objDetail[columnList]#

Report · Jul 17, 2009

I see what you're saying. Is is possible for you to dump the objDetails query that you're prepping? Something along the lines of the following:

<cfquery name="objDetail" datasource="test"> SELECT * FROM ObjTable WHERE OBJID= <cfqueryparam value="#objID#" CFSQLType='CF_SQL_INTEGER'> </cfquery> <cfset columnList = GetMetaData( objDetail )>  <cfset rotatedQuery = QueryNew("Name, Value") > <cfset newRow = QueryAddRow(rotatedQuery, len(columnList))> <cfloop index="i" from="1" to="#len(columnList)#"> <cfset temp = QuerySetCell(rotatedQuery,"Name",#columnList#,i)> <cfset temp = QuerySetCell(rotatedQuery,"Value",#objDetail[columnList]#,i)> </cfloop>

ObjDetail Query

Column List

Rotated Query

</cfsavecontent>

I think having this level of information would be very helpful in uncovering the source of your trouble. Thanks!

Adobe Community

cfquery sql update loop problem