Re: Ordering field names in a DESC / DESCRIBE table or SHOW COLUMNS from table command
Hi Peter (and MySQL list), On Wed, Jan 13, 2010 I (Ricardo Dias Marques) asked the following : It would be convenient for me to get a list of those fields ordered by field / column name. ... and on the same day, Peter Brawley peter.braw...@earthlink.net kindly replied: SELECT * FROM information_schema.columns WHERE table_schema='db' AND table_name='tbl'; Thank you Peter! With your good example, it became very easy for me to reach a working solution, that was running the following query (replacing database_name and table_name by their real names, obviously): SELECT column_name from INFORMATION_SCHEMA.columns WHERE table_schema='database_name' AND table_name='table_name' ORDER BY column_name; This query works perfectly in the scenario that I described in my original post (Linux server running MySQL 5.0). It does NOT work for MySQL 4.x, however (I have another machine that only has MySQL 4.1.12). For MySQL 4, the best I could do was this: 1 - Run the following mysqlshow command : # mysqlshow database_name table_name -p field_list.txt 2 - Open the field_list.txt in the Vim text editor. I then did a column selection in that file (by pressing CTRL + V to enter Visual Block mode and then selecting and deleting), I removed the columns that I didn't need (basically, removed every column EXCEPT the Field column). I also removed the extra lines that the mysqlshow command adds (decorative lines, column labels, etc...). Then, I saved this changed text file. 3 - Finally, I ran the sort command on that file: # sort field_list.txt Et voilà! Thanks again for helping me Peter! :) Cheers, Ricardo Dias Marques lists AT ricmarques DOT net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Ordering field names in a DESC / DESCRIBE table or SHOW COLUMNS from table command
Hi all, I have a Linux server with MySQL 5.0 (5.0.67) installed. In that server, I have a database that has a table with many fields (around 60). It would be convenient for me to get a list of those fields ordered by field / column name. Unfortunately, a DESC tablename (DESCRIBE tablename) command, in MySQL monitor (the MySQL command line client) returns a table with that table fields, but the fields appear in a strange order. I'm guessing this strange order can be the order given by the CREATE TABLE command that was used to create that table. The first thing I tried was to write DESC tablename ORDER BY Field. That failed: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY Field' at line 1 So, I went to read the online documentation about the DESCRIBE command and the SHOW COLUMNS (or its synonym SHOW FIELDS) command: MySQL :: MySQL 5.0 Reference Manual :: 12.3.1 DESCRIBE Syntax http://dev.mysql.com/doc/refman/5.0/en/describe.html MySQL :: MySQL 5.0 Reference Manual :: 12.5.5.5 SHOW COLUMNS Syntax http://dev.mysql.com/doc/refman/5.0/en/show-columns.html Unfortunately, I'm NOT finding, in those 2 web pages, any hints about sorting the list of fields by any criteria. I did some google searches about this, but I didn't find any relevant results (maybe I'm using the wrong keywords for my searches). So, I'm asking this: does anyone know a MySQL command to get this list of fields sorted by field name? Or do you achieve this result by other means (e.g: using some GUI tool, copying the list of fields to a text file and sorting it, writing some script...)? Thanks in advance! Best wishes, Ricardo Dias Marques lists AT ricmarques DOT net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Ordering field names in a DESC / DESCRIBE table or SHOW COLUMNS from table command
It would be convenient for me to get a list of those fields ordered by field / column name. SELECT * FROM information_schema.columns WHERE table_schema='db' AND table_name='tbl'; PB - Ricardo Dias Marques wrote: Hi all, I have a Linux server with MySQL 5.0 (5.0.67) installed. In that server, I have a database that has a table with many fields (around 60). It would be convenient for me to get a list of those fields ordered by field / column name. Unfortunately, a DESC tablename (DESCRIBE tablename) command, in MySQL monitor (the MySQL command line client) returns a table with that table fields, but the fields appear in a strange order. I'm guessing this strange order can be the order given by the CREATE TABLE command that was used to create that table. The first thing I tried was to write DESC tablename ORDER BY Field. That failed: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY Field' at line 1 So, I went to read the online documentation about the DESCRIBE command and the SHOW COLUMNS (or its synonym SHOW FIELDS) command: MySQL :: MySQL 5.0 Reference Manual :: 12.3.1 DESCRIBE Syntax http://dev.mysql.com/doc/refman/5.0/en/describe.html MySQL :: MySQL 5.0 Reference Manual :: 12.5.5.5 SHOW COLUMNS Syntax http://dev.mysql.com/doc/refman/5.0/en/show-columns.html Unfortunately, I'm NOT finding, in those 2 web pages, any hints about sorting the list of fields by any criteria. I did some google searches about this, but I didn't find any relevant results (maybe I'm using the wrong keywords for my searches). So, I'm asking this: does anyone know a MySQL command to get this list of fields sorted by field name? Or do you achieve this result by other means (e.g: using some GUI tool, copying the list of fields to a text file and sorting it, writing some script...)? Thanks in advance! Best wishes, Ricardo Dias Marques lists AT ricmarques DOT net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.432 / Virus Database: 270.14.138/2618 - Release Date: 01/13/10 07:35:00
Re: MySQL 5.0.22 and show columns bug?
While you are not wrong, James, is the length member suppose to denote the maximum length of data contained in result's specified column. NOTE: The result's. I.e. why give such an arbitrary number of bytes/length when no ENUM's or SET's are even in the result. The point being, even if you create a table containing 10 INT columns, the result of show columns from should show the Type column length of 3 with a maximum data allocation for the 10 rows of 30 bytes, and not ~2MB, as is currently the case. And even, in a worse case, MySQL Dev decided to give the length back as the maximum potential length, who determined 196605 should be the magic number? An ENUM can have 64K values, each of which can be a text value/label of at least 64 characters, thus a magic number should be at least megs in size to play it safe. Thus, such an approach is simply put, stupid. Ideally, as was the case in previous versions of MySQL, the Type column's Length should be given in context of the result, i.e. if there is an ENUM in the column list and it has the longest type description, the Type column's length should reflect its contained data size. Kind Regards SciBit MySQL Team http://www.scibit.com -Original Message- From: James Harvard [EMAIL PROTECTED] To: SciBit MySQL Team [EMAIL PROTECTED] CC: mysql@lists.mysql.com mysql@lists.mysql.com Subject: [Spam-Junk]Re: MySQL 5.0.22 and show columns bug? Sent: Thu, 06 Jul 2006 13:50:33 GMT Received: Thu, 06 Jul 2006 13:50:29 GMT Read: Sat, 30 Dec 1899 00:00:00 GMT Although I know nothing about C I imagine this is because the 'type' column can contain all the possible values from an ENUM or SET field. James Harvard At 10:30 am + 6/7/06, SciBit MySQL Team wrote: Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has been returning the column length (C API) of the 'Type' column of a show columns from.. statement as being 196605 (almost 192KB), when this column only really contains data in the region of 10 bytes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.22 and show columns bug?
OK, fair enough. In that case I would think that filing a report on bugs.mysql.com would be your best way forward. At 8:32 am + 7/7/06, SciBit MySQL Team wrote: While you are not wrong, James, is the length member suppose to denote the maximum length of data contained in result's specified column. NOTE: The result's. I.e. why give such an arbitrary number of bytes/length when no ENUM's or SET's are even in the result. The point being, even if you create a table containing 10 INT columns, the result of show columns from should show the Type column length of 3 with a maximum data allocation for the 10 rows of 30 bytes, and not ~2MB, as is currently the case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Spam-Probable]MySQL 5.0.22 and show columns bug?
Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has been returning the column length (C API) of the 'Type' column of a show columns from.. statement as being 196605 (almost 192KB), when this column only really contains data in the region of 10 bytes, if even. This is not only a bug, but extremely wasteful, as most client apps will therefore provide to allocate megs of ram for the potential data in this column to display even the simpliest of tables' column listing. Not only that, but many MySQL client apps will predetermine the correct manner of data display depending on the size of the data, i.e. use a normal text display vs a blob editor to display the subsequent information. I'm refering here to the st_mysql_field C API structure and specifically the 'length' member as defined in mysql.h. However, other columns seems fine with correct lengths, it just seems the person responsible for checking the result of the show columns into this structure has not done a very good job, as the column type text can not possibly take even 1KB of space, doesnt matter which charset you use, nevermind 192KB per column row displayed! Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.22 and show columns bug?
Although I know nothing about C I imagine this is because the 'type' column can contain all the possible values from an ENUM or SET field. James Harvard At 10:30 am + 6/7/06, SciBit MySQL Team wrote: Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has been returning the column length (C API) of the 'Type' column of a show columns from.. statement as being 196605 (almost 192KB), when this column only really contains data in the region of 10 bytes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW COLUMNS Syntax Using 3.23.54 Please!
Hi, I am trying to get all field names from my table that begin with letter X or Y, however the following statement and many variations I have tried produce an error: SHOW COLUMNS FROM TABLE LIKE X% OR LIKE Y% Any help here would be much appreciated :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW COLUMNS Syntax Using 3.23.54 Please!
shaun thornburgh wrote: I am trying to get all field names from my table that begin with letter X or Y, however the following statement and many variations I have tried produce an error: SHOW COLUMNS FROM TABLE LIKE X% OR LIKE Y% According to the manual, the pattern for SHOW COLUMNS is: SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'] There is no WHERE clause or expression evaluation here, to achieve what you want you must issue the statement twice: SHOW COLUMNS FROM tbl_name LIKE X%; SHOW COLUMNS FROM tbl_name LIKE Y%; URL: http://dev.mysql.com/doc/mysql/en/SHOW_COLUMNS.html You didn't ask, but a shorter way to write this is using the DESCRIBE command, which can be abbreviated to DESC: DESC tbl_name X%; DESC tbl_name Y%; URL: http://dev.mysql.com/doc/mysql/en/DESCRIBE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW COLUMNS Syntax Using 3.23.54
Hi, I am trying to get all field names from my table that begin with letter X or Y, however the following statement and many variations I have tried produce an error: SHOW COLUMNS FROM TABLE LIKE X% OR LIKE Y% Any help here would be much appreciated :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery ( show columns from...)
Hi all, I was wondering is it possible to use SHOW COLUMNS FROM tbl_name as a subquery. I tried something like this ( w/o success ): SELECT * FROM tbl1 INNER JOIN (SHOW COLUMNS FROM tbl2) AS t2 WHERE t1.ID =t2.field; or SELECT field FROM (show columns from Research.idefix); Teemu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery ( show columns from...)
Teemu Kuulasmaa [EMAIL PROTECTED] wrote: I was wondering is it possible to use SHOW COLUMNS FROM tbl_name as a subquery. I tried something like this ( w/o success ): SELECT * FROM tbl1 INNER JOIN (SHOW COLUMNS FROM tbl2) AS t2 WHERE t1.ID =t2.field; or SELECT field FROM (show columns from Research.idefix); Nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show columns
Hi Folks - simple Q from a newbie, I'd like to return only the column names using something like show columns. I'm using PHP to display db tables in html, with the headers of the html table url'ed to sort the result by field. It's easy when you hard-code the column names into the page, but I'm going by the table name only, and need to work with an array of the column names to print out the html table header. Help appreciated; thx in advance. Steve. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ADO + show columns
I'm experiencing some unexpected and undesired behavior attempting to run SHOW COLUMNS FROM TABLE using Delphi via ADO. MDAC: 2.6 RTM MyODBC: 2.50.37 O/S:Windows 98 MySQL: 2.23.24 (Linux) I can run most queries just fine (ie SHOW TABLES, selects, etc.). However any SHOW COLUMNS queries return an error or an empty result set. If I attempt to connect the query to a visual control, the error message, Data provider or other service returned and E_FAIL status. If I just attempt to connect the query and loop through the results, and empty result set is returned. I don't think an empty result set should be possible for a SHOW COLUMNS query. If the table doesn't exist, it will return an error and if the table does exist, there must be at least one column. I've tried several different versions of MDAC/ADO on several different computers, and DESCRIBE, EXPLAIN, and SHOW COLUMNS all exhibit this behavior. Anybody else experience this behavior or have any suggestions? Is this a limitation of accessing MySQL via ADO? query,sql,mysql,homer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ADO + show columns
j.urban writes: I'm experiencing some unexpected and undesired behavior attempting to run SHOW COLUMNS FROM TABLE using Delphi via ADO. MDAC: 2.6 RTM MyODBC: 2.50.37 O/S:Windows 98 MySQL: 2.23.24 (Linux) I can run most queries just fine (ie SHOW TABLES, selects, etc.). However any SHOW COLUMNS queries return an error or an empty result set. If I attempt to connect the query to a visual control, the error message, Data provider or other service returned and E_FAIL status. If I just attempt to connect the query and loop through the results, and empty result set is returned. I don't think an empty result set should be possible for a SHOW COLUMNS query. If the table doesn't exist, it will return an error and if the table does exist, there must be at least one column. I've tried several different versions of MDAC/ADO on several different computers, and DESCRIBE, EXPLAIN, and SHOW COLUMNS all exhibit this behavior. Anybody else experience this behavior or have any suggestions? Is this a limitation of accessing MySQL via ADO? Please try to run the above via native interface, e.g. mysqlgui and via some other ODBC connection, e.g. Access. If several ODBC clients fail on the command, please send us ODBC trace, so that we could take a look at it. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ADO + show columns
Have you tried using ASTA w/Delphi and MySQL? Using the ASTA MySQL Server you connect like the native and the ASTA componets allow you to build your Client GUI from Delphi. Very Slick! MySQL, the worlds greatest databaseg, seems to work just fine with it. You can get the Server here http://www.astatech.com/support/servers.htm You can download the ASTA Eval here http://www.astatech.com/asta26.htm If you do decide to use ASTA and need any help feel free to E-mail me. Mike Benzel [EMAIL PROTECTED] for NT Linux-Palm-CASL Support Cross Platform Messaging and TDataSet Components: TAstaPDADataSets Servers (Win32,Linux,Java) Clients (Win32,Linux,Java,Palm,WinCE,Linux PDA's) ASTA Technology Group http://www.astatech.com ASTAWireless http://www.astawireless.com -Original Message- FROM: Sinisa Milivojevic TO: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] DATE: Fri 7/27/01 12:08 SUBJECT: Re: ADO + show columns j.urban writes: I'm experiencing some unexpected and undesired behavior attempting to run SHOW COLUMNS FROM TABLE using Delphi via ADO. MDAC: 2.6 RTM MyODBC: 2.50.37 O/S:Windows 98 MySQL: 2.23.24 (Linux) I can run most queries just fine (ie SHOW TABLES, selects, etc.). However any SHOW COLUMNS queries return an error or an empty result set. If I attempt to connect the query to a visual control, the error message, Data provider or other service returned and E_FAIL status. If I just attempt to connect the query and loop through the results, and empty result set is returned. I don't think an empty result set should be possible for a SHOW COLUMNS query. If the table doesn't exist, it will return an error and if the table does exist, there must be at least one column. I've tried several different versions of MDAC/ADO on several different computers, and DESCRIBE, EXPLAIN, and SHOW COLUMNS all exhibit this behavior. Anybody else experience this behavior or have any suggestions? Is this a limitation of accessing MySQL via ADO? Please try to run the above via native interface, e.g. mysqlgui and via some other ODBC connection, e.g. Access. If several ODBC clients fail on the command, please send us ODBC trace, so that we could take a look at it. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
