We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Dynamically listing table in MYSQL

Medium Priority
241 Views
Last Modified: 2012-06-27
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

Comment
Watch Question

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

CERTIFIED EXPERT

Author

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.
another ooops on my part! (too much beer i guess...)

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

to this:
#list['TABLES_IN_' & dbname][currentrow]#
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.