ColdFusion SQL Error - [Table ....] Is Not Indexable By Name
I was helping a co-worker of mine tracking down a ColdFusion error he was getting:
[Table (rows 1 columns ID, PREFIX, FIRST_NAME, MIDDLE_NAME, LAST_NAME, SUFFIX, WORKING_NAME): [ID: coldfusion.sql.QueryColumn@c4736a] [PREFIX:
coldfusion.sql.QueryColumn@17542e7] [FIRST_NAME:
coldfusion.sql.QueryColumn@17b5ee7] [MIDDLE_NAME:
coldfusion.sql.QueryColumn@93ab1f] [LAST_NAME:
coldfusion.sql.QueryColumn@c79c37] [SUFFIX:
coldfusion.sql.QueryColumn@e0eb96] [WORKING_NAME:
coldfusion.sql.QueryColumn@8af270] ] is not indexable by name
I hadn't seen this error before, so the phrase "indexable" immediately made me think about full text indexes and primary keys. After several minutes of moving CFTRY/CFCATCH tags around, we finally found a better error message. It said something like the SELECT column 'name' was not a reference in any of the FROM tables. It was suddenly obvious; in his Query of Queries, the 'name' column was not defined. I find it very funny when simple mistakes take a lot of debugging.
On another note though, I think it is very interesting that the error thrown by the Query of Queries CFQuery tag was different than the one available in CFCATCH. Well, maybe not different at the core, but worded very differently.
Want to use code from this post? Check out the license.
Reader Comments
I go this error this morning, however I got it when I tried inserting a structure to a row of a query as in:
<cfset Tmp = StructInsert(QueryName[CurrentRow],OtherQueryName,QueryValue,true)>
I was pretty much trying my luck, half-expected the error. I hope this helps.
I'm working on work-around - thanks!
I recently was faced with the same problem, but for a different reason: I was trying to implement POIUtility into my application and in the ColumnList I left spaces after each comma. Removing the spaces fixed the problem.
@Kamil - thanks for the tip - I just ran into that.
@kamil,
You're a brain-saver. Wow. I */knew/* my column existed as it was being CFDumped in the code immediately preceeding.
For people of the future... I had
listtoarray("col1, <SPACE> col2, <SPACE> Col3")
the fix:
LtA("col1,col2,col3")
Thus, the HTMLEditFormat(Query[Columns[ColID]][Query.CurrentRow]) was being fed the array w/o spaces
You think it's funny....I think it's a pain. Thanks for the documentation though.
-A
Thought I would add to this...
I thought it was because I did not define the column in my query. I was wrong. I was outputting my query in CFScript using a for loop.
if(bldg_resultset.recordCount gt 0){
for(i=1;i<=bldg_resultset.recordCount;i++)
//ArrayAppend(bldgArray,bldg_resultset[i].bldgid); ArrayAppend(bldgArray,bldg_resultset.bldgid[i]);
}
The commented out code gave me this exact error, but instead of name it was '1', the value of the first i.
@Brad,
Good point - the column name always has to come first... which is definitely counter-intuitive if you think of queries as "arrays" of "structs".
I bounced into the same problem and it was even simpler but yet was a stumper. I had the columns listed correctly or so I thought. Only thing in my quest to have properly formatted code, I had spaces between each column. Don't ask! Thing was when it was it included the space in the error message but since i wasn't looking for it I didn't see them. Thanks for the tip!
Iain
I had this same issue as well but from a different approach. I didn't have a misspelled name and the column was defined. In fact, I wasn't even really using that particular column in my query, although I was doing SELECT * to get the whole row.
I basically had a function that took an ID and a recordset as arguments. From a QoQ the recordset gets narrowed down, but it is highly likely that I will get back multiple rows still with the same ID.
Through a bunch of calculations and whatnot, I single out the one row I am interested in, save that row number to a variable, and attempt to return that row to the calling page, and this is where that same error came from.
You cannot return a row from a recordset like:
<cfreturn queryname[row_index]>
The trick around this is to create another column in your table and have an incremental counter in it. instead of saving 'CurrentRow' to the row_index variable (to store the current index) and returning queryname[row_index], you do another QoQ to find the row that contains the value row_index in your new column.
Then, you can return this entire query and you now have your singled out row...no more errors :)
I encountered the same error, "..coldfusion is not indexable by LEFT(column name)..." in a regular query. Maybe this goes without saying to some but my original code:
SELECT art_key,left(art_title,50)
FROM cts_article
just needed a small tweak:
SELECT art_key,left(art_title,50) AS art_title
FROM cts_article
which I tried because this post mention how the query of queries had not defined the 'name' column.
[just posting in case others find their way here from a search on the same error]
Yeah Derek Versteegen!!! your post solve my problem: giving an alias to the column
i have the same proble, but, i try to show the info of 100 columns, all have the similar name, p001, p002... to p100, i put first a column name total.
ok, for not make the same code 100 times, i sink that i can do something like 'print' the name and use like a variable o something and put to the name of the query ande take that valor.
<cfset data=query.columname> and i have that error.
<table>
<tr>
<td>PREGUNTA</td>
<td>ACIERTOS</td>
</tr>
<cfset info=structNew()>
<cfloop from="1" to="100" index="x">
<cfswitch expression="#Len(x)#">
<cfcase value="1"> <cfset preg='P00'&x> </cfcase>
<cfcase value="2"> <cfset preg='P0'&x> </cfcase>
<cfcase value="3"> <cfset preg='P'&x> </cfcase>
</cfswitch>
<cfset info.campo=resultados.#campo#>
<cfset campo='resultados.'&preg>
<cfdump var="#info#">
<tr>
<td>#preg#</td>
<td>
#resultados['#campo#'][0]#</td>
</tr>
</cfloop>
</table>
if i dont make that, i have to make the same code to make % and a little bar graph 100 times LOL.
i share my similar error and.. looking for if my idea is possible, thanks, ben naddel, u are awesome!. thanks por coding!.
you make the string 'The Power of Code' make sense. thanks.
Thank you!
this save me a lot of time! =)