neocode02
asked on
My "show tables" mistery in linux-mysql-apache environment
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-coldfus
<cfquery datasource="MyDB" name="getTables">
SHOW TABLES
</cfquery>
<cfoutput query="MyDB">
#Tables_in_MyDB#
</cfoutput>
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-coldfus
<cfquery datasource="MyDB" name="getTables">
SHOW TABLES
</cfquery>
<cfoutput query="MyDB">
#Tables_in_MyDB#
</cfoutput>
ASKER
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.
Theres no way I can access a system table like sysobjects with Mysql server to my knowledge.
<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>
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>
ASKER
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.
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.
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.
ASKER
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.
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.
Probably you have a problem with your unix myodbc driver. It seems to block show statement to be send back to CF.
Oh, what MyODBC driver you use? Merant which is delivered with CF or native MyODBC from MySQL?
ASKER
I am using the Merant as it is the only mysql driver that appears in the list of drivers in the coldfusion administration.
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).
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.
<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.
ASKER
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Thanx. Ask me if you need any farther assistance.
ASKER
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.
Nice to hear it :-)
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_Table
</cfquery>
<cfoutput query="MyDB">
#Name#
</cfoutput>