asked on

Dynamically listing table in MYSQL

Hi well i am running this query in <cfquert tag as:

SHOW FULL tables

the above is run in <cfquery> to get all the tables from mysql. database

ok when i use CFDUMP it comes as:

TABLES_IN_MYDATABASENAME
tabl1
table2
table3
etc

Now when i use another database i have to hard code the databaseName to get all tables like:

TABLES_IN_MYDATABASENAME2
Mytabl1
Mytable2
Mytable3
etc

I tried something like this:

<cfset dbName = listGetAt(list.columnList,3,"_")>
<cfoutput>#evaluate("tables_in_"&dbName)#</cfoutput>

to get the databaseName dynamically and get the tablesName exactly but it is not working as though.

Please help

<cfoutput query="list">
        <tr>
          <td><cfset dbName = listGetAt(list.columnList,3,"_")>
				<!---<cfoutput>#evaluate("tables_in_"&dbName)#</cfoutput>--->
                #TABLES_IN_TESTTABLE#
</cfoutput>

Open in new window

azadisaryev

try attached code - not sure if it does exactly what you need since i am not sure i correctly understood your multiple databases querying part: is the datasource name you provide to your <cfquery> tag base on some [user-selected] variable?

Azadi

<cfquery name="list" datasource="#some_variable_here_i_guess#">
SHOW FULL TABLES
</cfquery>
 
<cfset dbname = ucase(listlast(listfirst(list.columnlist), "_"))>
<!---
in my test on MySQL database the column names returned by LIST query were always 'TABLES_IN_[DB_NAME_HERE]' and 'TABLE_TYPE', thus 'TABLES_IN_...' column is always the first element in the COLUMNLIST list - hence used listfirst() in the code above
--->
 
<!--- now use array notation to output rows of table names --->
<cfoutput query="list">
#list['TABLES_IN_' & dbname]#<br />
</cfoutput>

Open in new window

Coast Line

ASKER

the datasource is dyanmic as datasource is stored in session and based on that session. if datasource is mysql

then the query will run based on the datasource selected.

Here is what i tried and get this result:

<cfoutput query="list">
<tr>
<td><cfif session.dsntype eq 'mysql'>
<cfset dbname = ucase(listlast(listfirst(list.columnlist), "_"))>
#list['TABLES_IN_' & dbname]#<br />
<cfelse>
Blah Blah Blah
</cfif>
</cfoutput>

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

azadisaryev

another ooops on my part! (too much beer i guess...)

change this:
#list['TABLES_IN_' & dbname]#

to this:
#list['TABLES_IN_' & dbname][currentrow]#

ASKER CERTIFIED SOLUTION

azadisaryev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial