17 Replies Latest reply on Feb 16, 2009 10:17 AM by Newsgroup_User

    My Foolish Question

    newchickinCF Level 1
      Hi People I am using a cfffunction and returntype is array.

      I am using on my cfm page and try to get the values i am getting error:

      <cfinvoke component="showall" method="getDBTableStruct" tablename="#url.table#" returnvariable="tableData"/>
      <cfdump var="#tableData#">
      <cfset wibble = getDBTableStruct() />
      <cfoutput>#wibble[ColumnName]#</cfoutput>
      </cfif>

      The web site you are accessing has experienced an unexpected error.
      Please contact the website administrator.

      The following information is meant for the website developer for debugging purposes.
      Error Occurred While Processing Request
      Variable GETDBTABLESTRUCT is undefined.

      The error occurred in C:\Inetpub\wwwroot\p1\index.cfm: line 117

      115 : <cfinvoke component="showall" method="getDBTableStruct" tablename="#url.table#" returnvariable="tableData"/>
      116 : <cfdump var="#tableData#">
      117 : <cfset wibble = getDBTableStruct() />
      118 : <cfoutput>#wibble[ColumnName]#</cfoutput>
      119 : </cfif>


      Ok When I Dump i get The Following:

      array
      1
      struct
      AllowNulls false
      CF_DataType CF_SQL_INTEGER
      ColumnName ban_id
      Increment true
      PrimaryKey true
      Special [empty string]
      2
      struct
      AllowNulls true
      CF_DataType CF_SQL_VARCHAR
      ColumnName ban_title
      Increment false
      PrimaryKey false
      Special [empty string]
      length 255
      3
      struct
      AllowNulls true
      CF_DataType CF_SQL_VARCHAR
      ColumnName ban_img
      Increment false
      PrimaryKey false
      Special [empty string]
      length 255
      4
      struct
      AllowNulls true
      CF_DataType CF_SQL_LONGVARCHAR
      ColumnName ban_link
      Increment false
      PrimaryKey false
      Special [empty string]
      5
      struct
      AllowNulls true
      CF_DataType CF_SQL_INTEGER
      ColumnName ban_hits
      Increment false
      PrimaryKey false
      Special [empty string]
      6
      struct
      AllowNulls true
      CF_DataType CF_SQL_VARCHAR
      ColumnName ban_active
      Increment false
      PrimaryKey false
      Special [empty string]
      length 255
        • 1. Re: My Foolish Question
          Level 7
          this line in your code

          <cfset wibble = getDBTableStruct() />

          calls some cf udf/function named 'getDBTableStruct'

          i suspect you do not have such a udf/function, but you rather want to
          use the array/query returned by your <cfinvoke> call? then you need to
          change the above to:

          <cfset wibble = tableData />

          or, rather, omit this line altogether: no need to duplicate variables -
          you already have tableData var returned by your <cfinvoke>

          this next line in your code:

          <cfoutput>#wibble[ColumnName]#</cfoutput>

          will error, too - unless you have ColumnName variable defined in your
          page...

          if your cfc function returns an array, you need to cfloop over the array
          to output any of its elements.



          Azadi Saryev
          Sabai-dee.com
          http://www.sabai-dee.com/
          • 2. Re: My Foolish Question
            Manju82
            why are you doing this <cfset wibble = getDBTableStruct() /> ?
            Are you trying to see/loop through the output structure retruned by the function..?
            If so this variable tableData will have the information you require. I think...
            • 3. Re: My Foolish Question
              newchickinCF Level 1
              Okay Here i tried through the Loop way but did not work:

              <cfinvoke component="showall" method="getDBTableStruct" tablename="#url.table#" returnvariable="tableData"/>
              <cfdump var="#tableData#">
              <cfloop from="1" to="#arrayLen(tableData)#" index="i">
              <cfoutput>#tableData #<br /></cfoutput>
              </cfloop>
              </cfif>

              Errors out as:

              Complex object types cannot be converted to simple values.
              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 v
              • 4. Re: My Foolish Question
                Manju82 Level 1
                oops - you are using the wrong CFLOOP, as the variable is a straucture, you cannot loop it using the above, try using looping like a collection. Details are here http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?cont ext=ColdFusion_Documentation&file=00000296.htm
                • 5. Re: My Foolish Question
                  newchickinCF Level 1
                  Well are us saying i hsould convert it into a Structure and then loop over the Structure.

                  Well isn't there a way i can just loop over the returnvariable tableData and output the results.

                  Cheers
                  • 6. Re: My Foolish Question
                    Level 7
                    what exactly does you cfc function return?
                    can you post your cfc function code?

                    Azadi Saryev
                    Sabai-dee.com
                    http://www.sabai-dee.com/
                    • 7. Re: My Foolish Question
                      Manju82 Level 1
                      After seeing the function name getDBTableStruct(), I thought it is returning a struct value. If it is returning a result set because of a DB query call, then try using looping over a query like belwo:-
                      <cfloop query = "tableData"
                      <cfoutput>#tableData.ColumnName#</cfoutput>
                      </cfloop>

                      Details are here:-
                      http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?cont ext=ColdFusion_Documentation&file=00000294.htm

                      I hope this would help.
                      • 8. Re: My Foolish Question
                        newchickinCF Level 1
                        Ok My CFC is returning this:

                        <cffunction name="getDBTableStruct" access="public" returntype="array" output="no" hint="I return the structure of the given table in the database.">
                        <cfargument name="tablename" type="string" required="yes">
                        <cfscript>
                        var qRawFetch = 0;
                        var arrStructure = 0;
                        var tmpStruct = StructNew();
                        var i = 0;

                        var PrimaryKeys = 0;
                        var TableData = ArrayNew(1);
                        </cfscript>
                        <cfset qRawFetch = runSQL("SELECT TOP 1 * FROM #arguments.tablename#")>
                        <cfset arrStructure = getMetaData(qRawFetch)>
                        <cfif isArray(arrStructure)>
                        <cfloop index="i" from="1" to="#ArrayLen(arrStructure)#" step="1">
                        <cfset tmpStruct = StructNew()>
                        <cfset tmpStruct["ColumnName"] = arrStructure .Name>
                        <cfset tmpStruct["CF_DataType"] = getCFDataType(arrStructure
                        .TypeName)>
                        <!--- %% Ugly guess --->
                        <cfif arrStructure .TypeName eq "COUNTER" OR ( i eq 1 AND arrStructure.TypeName eq "INT" AND Right(arrStructure .Name,2) eq "ID" )>
                        <cfset tmpStruct["PrimaryKey"] = true>
                        <cfset tmpStruct["Increment"] = true>
                        <cfset tmpStruct["AllowNulls"] = false>
                        <cfelse>
                        <cfset tmpStruct["PrimaryKey"] = false>
                        <cfset tmpStruct["Increment"] = false>
                        <cfset tmpStruct["AllowNulls"] = true>
                        </cfif>
                        <!--- %% Ugly guess --->
                        <cfif isStringType(arrStructure
                        .TypeName) AND NOT tmpStruct["CF_DataType"] eq "CF_SQL_LONGVARCHAR">
                        <cfset tmpStruct["length"] = 255>
                        </cfif>
                        <cfset tmpStruct["Special"] = "">
                        <cfif Len(tmpStruct.CF_DataType)>
                        <cfset ArrayAppend(TableData,StructCopy(tmpStruct))>
                        </cfif>
                        </cfloop>
                        <cfelse>
                        <cfthrow message="Error Found." detail="NoMSAccesSupport">
                        </cfif>
                        <cfreturn TableData>
                        </cffunction>
                        • 9. Re: My Foolish Question
                          Manju82 Level 1
                          ok - it is returning an array and if I see your code (while you are looping through it as an array), you have written it as :-

                          <cfloop from="1" to="#arrayLen(tableData)#" index="i">
                          <cfoutput>#tableData#<br /></cfoutput>
                          </cfloop>

                          I think it should be:-

                          <cfloop from="1" to="#arrayLen(tableData)#" index="i">
                          <cfoutput>#tableData #<br /></cfoutput>
                          </cfloop>

                          i.e instead of tableData, try tableData
                          inside cfoutput
                          • 10. Re: My Foolish Question
                            Manju82 Level 1
                            oops, somehow "i" missed out, i.e inside cfoutput it shoudl be tableData of i
                            • 11. My Foolish Question
                              newchickinCF Level 1
                              I tried both way: like this:

                              <cfloop from="1" to="#arrayLen(tableData)#" index="i">
                              <cfoutput>#tableData[ ]#<br /></cfoutput>
                              </cfloop>

                              and like this:

                              <cfloop from="1" to="#arrayLen(tableData)#" index="i">
                              <cfoutput>#tableData#<br /></cfoutput>
                              </cfloop>

                              it works neither way it shows only the above error: Complex object types cannot be converted to simple values.
                              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.
                              • 12. My Foolish Question
                                Dan Bracuk Level 5
                                I don't see you using your index anywhere in your cfoutput.

                                By the way, on these forums, if you surround the letter i with square brackets, the rest of your text gets converted to italics and what you are trying to display does not get displayed. Use ii instead of i.
                                • 13. Re: My Foolish Question
                                  Level 7
                                  hmm... i believe in his case that would output 1, 2, 3,.... since he's
                                  looping not over an array, but over an array length

                                  Azadi Saryev
                                  Sabai-dee.com
                                  http://www.sabai-dee.com/
                                  • 14. Re: My Foolish Question
                                    Level 7
                                    it looks like what your function returns is an array of structures. thus
                                    elements of your array are structures and not simple values - hence the
                                    error.

                                    try this:

                                    <cfloop from="1" to="#arrayLen(tableData)#" index="x">
                                    <cfloop collection="#tabledata[x]#" item="key">
                                    <cfoutput>#key#<br /></cfoutput>
                                    </cfloop>
                                    </cfloop>

                                    alternative:

                                    <cfloop array="#tabledata#" index="a">
                                    <cfloop collection="#a#" item="key">
                                    <cfoutput>#key#<br /></cfoutput>
                                    </cfloop>
                                    </cfloop>

                                    Azadi Saryev
                                    Sabai-dee.com
                                    http://www.sabai-dee.com/
                                    • 15. Re: My Foolish Question
                                      newchickinCF Level 1
                                      Thanks Azadi You Rock Dude!!!!

                                      It displays the list of all stuff i am on to:

                                      i want them to appear in my tables but i am confused:

                                      here is my code:

                                      <cfloop from="1" to="#arrayLen(tableData)#" index="x">
                                      <cfloop collection="#tabledata[x]#" item="key">
                                      <tr>
                                      <td><cfinput type="text" name="tcolumnName" value="#key#" readonly="yes"></td>
                                      <td><cfinput type="text" name="ttexttype" value="#key#"></td>
                                      <td><input type="checkbox" name="tIS_NULLABLE" <cfif #key# IS 'yes'>checked="checked" value="Null"<cfelse>value="Not Null"</cfif>></td>
                                      <td><cfinput type="text" size="4" value="#key#" name="tlength"></td>
                                      </tr>
                                      </cfloop></cfloop>

                                      do i need to some way or other way:

                                      P.S I am noob in CF and would like to know more. Please guide me
                                      • 16. Re: My Foolish Question
                                        newchickinCF Level 1
                                        Thanks Azadi You Rock Dude!!!!

                                        It displays the list of all stuff i am on to:

                                        i want them to appear in my tables but i am confused:

                                        here is my code:

                                        <cfloop from="1" to="#arrayLen(tableData)#" index="x">
                                        <cfloop collection="#tabledata[x]#" item="key">
                                        <tr>
                                        <td><cfinput type="text" name="tcolumnName" value="#key#" readonly="yes"></td>
                                        <td><cfinput type="text" name="ttexttype" value="#key#"></td>
                                        <td><input type="checkbox" name="tIS_NULLABLE" <cfif #key# IS 'yes'>checked="checked" value="Null"<cfelse>value="Not Null"</cfif>></td>
                                        <td><cfinput type="text" size="4" value="#key#" name="tlength"></td>
                                        </tr>
                                        </cfloop></cfloop>

                                        do i need to some way or other way:

                                        P.S I am noob in CF and would like to know more. Please guide me

                                        it displays like

                                        i want columnName should appear under columName and datatype under datatype but not working that way

                                        Column Name: Data Type: Is Nullable: Key:












































                                        • 17. Re: My Foolish Question
                                          Level 7
                                          well, for starters, your <tr> tag should be before <cfloop
                                          collection...> tag (and the closing </tr> should be after the closing
                                          </cfloop> for the collection loop).

                                          your table rows will be created by the outer loop (over the array
                                          length), but the cells will be created by the collection loop.

                                          you will also have to either have a <td> for each key of your structure
                                          following the order they were defined in your cfc function, or, if you
                                          want to output only some keys of your structure, to <cfif> to check for
                                          the key name before you create the form element for it...

                                          and if you need to output the VALUE of the key, you have to cfoutput not
                                          #key# but #tabledata[x][key]#. #key# will output the NAME of the key,
                                          not its value...

                                          the following code should produce the output close to what you want, i
                                          think:

                                          <cfloop from="1" to="#arrayLen(tableData)#" index="x">
                                          <tr>
                                          <cfloop collection="#tabledata[x]#" item="key">
                                          <td>
                                          <cfif key eq "ColumnName">
                                          <cfinput type="text" name="tcolumnName" value="#tableData[x][key]#"
                                          readonly="yes">
                                          <cfelseif key eq "CF_DataType">
                                          <cfinput type="text" name="ttexttype" value="#tableData[x][key]#">
                                          <cfelseif key eq "AllowNulls">
                                          <input type="checkbox" name="tIS_NULLABLE" value="1" <cfif
                                          tableData[x][key] is true>checked="checked"</cfif>>
                                          <cfelseif key eq "length">
                                          <cfinput type="text" size="4" name="tlength" value="#tableData[x][key]#">
                                          </cfif>
                                          </td>
                                          </cfloop>
                                          </tr>
                                          </cfloop>

                                          hth

                                          Azadi Saryev
                                          Sabai-dee.com
                                          http://www.sabai-dee.com/