My Foolish Question

Report · Feb 16, 2009

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

Report · Feb 16, 2009

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/

Report · Feb 16, 2009

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

Report · Feb 16, 2009

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

Report · Feb 16, 2009

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

Report · Feb 16, 2009

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?context=ColdFu...

I hope this would help.

Report · Feb 16, 2009

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

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

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

Report · Feb 16, 2009

what exactly does you cfc function return?
can you post your cfc function code?

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

Report · Feb 16, 2009

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?context=ColdFu...

Report · Feb 16, 2009

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

Report · Feb 16, 2009

oops, somehow "i" missed out, i.e inside cfoutput it shoudl be tableData of i

Report · Feb 16, 2009

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.

Report · Feb 16, 2009

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

Report · Feb 16, 2009

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

Report · Feb 16, 2009

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

Report · Feb 16, 2009

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[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#" item="key">
<td>
<cfif key eq "ColumnName">
<cfinput type="text" name="tcolumnName" value="#tableData[key]#"
readonly="yes">
<cfelseif key eq "CF_DataType">
<cfinput type="text" name="ttexttype" value="#tableData[key]#">
<cfelseif key eq "AllowNulls">
<input type="checkbox" name="tIS_NULLABLE" value="1" <cfif
tableData[key] is true>checked="checked"</cfif>>
<cfelseif key eq "length">
<cfinput type="text" size="4" name="tlength" value="#tableData[key]#">
</cfif>
</td>
</cfloop>
</tr>
</cfloop>

hth

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

Report · Feb 16, 2009

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.

Report · Feb 16, 2009

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/

Adobe Community

My Foolish Question