?
Solved

My "show tables" mistery in linux-mysql-apache environment

Posted on 2003-03-14
18
Medium Priority
?
387 Views
Last Modified: 2013-12-24
I have used the Show tables and show databases
without any problems inside the cfquery tag from
non linux machines, running coldfusion mysql and IIS server.
But I have never seen the same code work in the 2 linux-mysql-apache servers I have tested.
Is there an issue on this. Please help. I have been in search of an answer for this for a long, long time. Why a code like this connecting to a database called MyDB would not work
when I run it in a linux-apache-mysql-coldfusion environment, but it works beautiful in windows-IIS-mysql-coldfusion machine:
<cfquery datasource="MyDB" name="getTables">
SHOW TABLES
</cfquery>
<cfoutput query="MyDB">
#Tables_in_MyDB#
</cfoutput>
0
Comment
Question by:neocode02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 

Expert Comment

by:SilverG
ID: 8143511
I am sure there is another way... but this works for me.

First check to see what Category your tables are in using
<cfquery datasource="MyDB" name="CheckDB">
  select Category, Name from sysobjects
</cfquery>
<cfdump var="#CheckDB#">

Then remove that code and use this:

<cfquery datasource="MyDB" name="getTables">
  SELECT Category, Name
  FROM sysobjects
  WHERE category = Category_Num_Of_Your_Tables
</cfquery>

<cfoutput query="MyDB">
#Name#
</cfoutput>
0
 

Author Comment

by:neocode02
ID: 8145555
It does not work. You must be running a MS SQL server.
Theres no way I can access a system table like sysobjects with Mysql server to my knowledge.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8147712
<cfquery datasource="MyDB" name="getTables">
SHOW TABLES FROM MyDB
</cfquery>
<cfoutput query="MyDB">
#Tables_in_MyDB#
</cfoutput>

---------

Also useful:

<CFQUERY datasource="MyDB" NAME="List_DB">
SHOW DATABASES
</CFQUERY>

<cfoutput query="List_DB">
#currentrow#. #DATABASE#<br>
</CFQUERY>

---------

The problem arose: I have no clue how to handle to display list of tables dynamically. Try to play somehow this way:

<CFQUERY datasource="MyDB" NAME="LDB">
SHOW DATABASES
</CFQUERY>

<cfoutput query="LDB">
#currentrow#. #DATABASE#<br>

<CFQUERY datasource="MyDB" NAME="LTB">
SHOW TABLES FROM #LDB.DATABASE#
</CFQUERY>

#LTB.columnlist#

</CFQUERY>

0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:neocode02
ID: 8151971
The error I keep getting when I use the SHOW statement in my query is: The QUERY attribute of the tag does not specify the name of an available query.
No matter how I use the SHOW sql statement I will get this error. It's quite a shocking since it doesent seem to be a problem with Mysql: I can run the query without any problems using the shell and mysql client. I can run the command using Dbtools, or mysql control center, or even MyPHPadmin. So my suspeccion would be Coldfusion for Linux doesnot support the SHOW statement at all.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8153474
My production server is Linux with CF 5.0 and mysql. And I experience no problems with the SHOW statements. Try to overview your system configuration. Especially the user priv. configs for the user you used to connect through ODBC.
0
 

Author Comment

by:neocode02
ID: 8154544
I have reviewed the configuration for the user I use to connect and it currently have all priviledges set.
And this is the same as in the phpMyadmin configuration file where the same information has to be set. Why this SQL statement is the only one I cannot query from a Coldfusion query tag?
Why the phpMyadmin and other clients have no problem with the statement.?
I am using Coldfusion 5 and mysql  3.23.41 in a linux machine, running the Apache webserver.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8156042
Probably you have a problem with your unix myodbc driver. It seems to block show statement to be send back to CF.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8156188
Oh, what MyODBC driver you use? Merant which is delivered with CF or native MyODBC from MySQL?
0
 

Author Comment

by:neocode02
ID: 8159349
I am using the Merant as it is the only mysql driver that appears in the list of drivers in the coldfusion administration.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8161248
Ah, Merant... Try to test it with native MySQL MyODBC driver (there is a hotfix described within Macromedia site that helps CF work properly with it).
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8161272
ah. and for testing perpose use debug option is query:

<cfquery name="x" debug>
show databases
</cfquery>

see if it returns records. If not, you really have Merant that blocks SHOW statement. It is executed in MySQL server but results filtered.
0
 

Author Comment

by:neocode02
ID: 8167141
It seems that this is the case as you say, as I get the following debugging info:

Queries

LDB (Records=0, Time=10ms)
SQL =
SHOW DATABASES

And afterwards the "The QUERY attribute of the tag does not specify the name of an available query" error.
I'm unsure as how to proceed to change linux-coldfusion server use the native driver in order to test this, hope you could have some comments on how to do this. I'll look for the fix in macromedia.
0
 

Author Comment

by:neocode02
ID: 8168383
I'd appreciate if you can tell me what is the hotfix you are referring to. I have looked for Merant upgrades, but the only one seems to be for solaris platform only. As I have been able to understand, Coldfusion 5 uses an adapter cfodbc45.so that in turn uses libodbc.so that is the Merant myodbc driver. How do you make it work on your linux machine with the native myodbc driver.
0
 
LVL 1

Accepted Solution

by:
Wasistdas earned 300 total points
ID: 8168678
Back up your odbc files. Download MyODBC driver from www.mysql.com. Install it. Point CF to recognize ODBC connection. Remember - CF may not work with it properly when configuring DNSs, so you may have to configure odbc.ini file manually (I used to do so). If you meet the error while connecting, download MyODBC source code and do what is described in Macromedia hotfix: http://www.macromedia.com/support/coldfusion/ts/documents/tn18300.htm

0
 

Author Comment

by:neocode02
ID: 8181129
It's going to take me a while to test this, in part because I dont have advanced linux know-how(being a recent convert from the microsoft arena); So I will have to setup another machine to do this tests in case something goes wrong.
But everything appears to indicate you must be right. So I am awarding you with the points even before I do the test.
I certainly appreciate all the help.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8182539
Thanx. Ask me if you need any farther assistance.
0
 

Author Comment

by:neocode02
ID: 8204622
Finally I was able to setup a test Machine. I was able to use the show statement by modifying the odbc.ini file manually and replacing the driver line to point from CFmysql15.so to the libmyodbc.so driver.
0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8206924
Nice to hear it :-)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question