4

I am trying to loop over the tables of a database using the following code getting an error java.lang.UnsupportedOperationException. i have even tried with cfloop query and other attributes got errors complex value can't convert to simple value. can anyone tell me how shall i have to loop over this query ? Thanks.

<cfquery name="q" datasource="datasource">
    SHOW TABLES FROM datasource
</cfquery>
<cfloop collection ="#q#" item="i">
   #q[i]#
</cfloop>
Suleman khan
  • 1,038
  • 4
  • 14
  • 34

3 Answers3

7

You are getting that error because cfloop collection expects a structure, not a query object. Hence the "UnsupportedOperation..." error.

Instead you should use a query loop. The generated column name is dynamic, based on the database name you supply. You can either hard code it or access it dynamically:

   <cfset colNames = listToArray(q.columnList)>
   <cfoutput query="q">
      <cfloop array="#colName#" index="col">
            #q[col][currentRow]#
      </cfloop>
      <br>
   </cfoutput>

That said, I find it easier to use the metadata INFORMATION_SCHEMA views. You can query them just like any table. Then output the static column names as usual.

    <cfquery name="yourQueryName" ...>
       SELECT  TABLE_NAME
       FROM    INFORMATION_SCHEMA.TABLES
       WHERE   TABLE_SCHEMA = 'YourDatabaseName'
    </cfquery>

    <cfoutput query="yourQueryName">
         #TABLE_NAME# <br>
    </cfoutput>
Leigh
  • 28,765
  • 10
  • 55
  • 103
  • Thanks Leigh... this is really awesome answer. can you tell me how can we get the column names of all the tables ? – Suleman khan Feb 11 '14 at 21:41
  • 2
    Query the [`INFORMATION_SCHEMA.COLUMNS`](http://dev.mysql.com/doc/refman/5.0/en/columns-table.html) view instead. To get the columns for a single table, just filter on the `TABLE_NAME` instead of schema. ie `...WHERE TABLE_NAME = 'Some Table'` – Leigh Feb 11 '14 at 21:57
  • 2
    Actually, I take that back. You should Filter on *both* `table_name` and `table_schema`. Just in case the same `table_name` exists in multiple databases. – Leigh Feb 11 '14 at 23:03
  • Thanks a lot, this too helpful. – Suleman khan Feb 12 '14 at 09:24
1

have you tried CFDBINFO? It supports a type of 'tabes' and ought to return you a query of table name, type and remarks for each table.

barnyr
  • 5,678
  • 21
  • 28
0

How about

<cfoutput query="q">
#tables#
<cfoutput>
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • tables are in tables_in_database column.... its not working. when i dump it i didn't find any column name except tables_in_nameofdb. – Suleman khan Feb 11 '14 at 20:34