[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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

0
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 3
1 Solution
 
azadisaryevCommented:
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

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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.
0
 
azadisaryevCommented:
another ooops on my part! (too much beer i guess...)

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

to this:
#list['TABLES_IN_' & dbname][currentrow]#
0
 
azadisaryevCommented:
also, just fyi, SHOW FULL TABLES command is MySQL-specific - so no need for that dbtype chack you have there.

for commands to use on other rdbms:
http://forums.whirlpool.net.au/forum-replies-archive.cfm/523219.html

Azadi
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now