LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 01-09-2004, 09:45 AM   #1
Travis86
Member
 
Registered: Dec 2002
Location: The land of GMT -6
Distribution: OS X, PS2 Linux, Ubuntu, IRIX 6.5
Posts: 399

Rep: Reputation: 31
Finding the name and data type of a column in SQL


I'm trying to create a web-based interface to an SQL database. First, it will display then name of the column. Then, if the user is editing a row that is a bool, it'll display a true/false/null listbox, and if it's text, it will display a textbox. phpMyAdmin is able to detect the name and data type, so I assume it is possible, but I can't find how on Goolgle.

Last edited by Travis86; 01-09-2004 at 09:46 AM.
 
Old 01-09-2004, 10:01 AM   #2
Hko
Senior Member
 
Registered: Aug 2002
Location: Groningen, The Netherlands
Distribution: ubuntu
Posts: 2,530

Rep: Reputation: 108Reputation: 108
Code:
DESCRIBE <tablename>
 
Old 01-09-2004, 10:24 AM   #3
mfeat
Member
 
Registered: Aug 2003
Location: Akron, OH
Distribution: Fedora Core 3
Posts: 185

Rep: Reputation: 30
you could also do:

select column_name, data_type
from all_tab_columns
where table_name = 'my_table'

some documentation on all_tab_columns:
http://216.239.37.104/search?q=cache...=en&ie=UTF-8#7
 
Old 01-09-2004, 10:25 AM   #4
Travis86
Member
 
Registered: Dec 2002
Location: The land of GMT -6
Distribution: OS X, PS2 Linux, Ubuntu, IRIX 6.5
Posts: 399

Original Poster
Rep: Reputation: 31
I think DESCRIBE is what I want since I just want to list the data. If I were going to compare the data to something, I'd probably use all_tab_columns.

Thanks.

On further investigation, all_tab_columns is only for MaxDB databases. I don't have mine set up like that.

Last edited by Travis86; 01-09-2004 at 10:37 AM.
 
Old 01-09-2004, 12:34 PM   #5
Strike
Member
 
Registered: Jun 2001
Location: Houston, TX, USA
Distribution: Debian
Posts: 569

Rep: Reputation: 31
If you want to use this SQL for many databases (i.e., not MySQL which I'm guessing you are using since afaik DESCRIBE is only in the MySQL spec), then DESCRIBE is not ANSI SQL standard compliant. In fact, there is no ANSI standard way of querying the column name and column data type via SQL that I know of. Why would your program need to know such things?
 
Old 01-09-2004, 09:05 PM   #6
Travis86
Member
 
Registered: Dec 2002
Location: The land of GMT -6
Distribution: OS X, PS2 Linux, Ubuntu, IRIX 6.5
Posts: 399

Original Poster
Rep: Reputation: 31
I want users to be able to add columns to a table and then input data into the table they created. Since I don't know what the name or type of the the column was that they created, I need to ask the database. At first I thought I could keep the name in the first row, but if it was numerical, that wouldn't work, and I would also need the data type in there, as well. I haven't decided exactly what I will do.

Thanks for the tip about the SQL standard. It's hard to keep track of that sort of stuff.
 
Old 01-10-2004, 09:15 AM   #7
Strike
Member
 
Registered: Jun 2001
Location: Houston, TX, USA
Distribution: Debian
Posts: 569

Rep: Reputation: 31
What language are you using? There might be an API call that allows you to obtain the structure of a given table without having to use SQL. Of course, you can just parse the returned values from the DESCRIBE call for MySQL.
 
Old 01-10-2004, 11:15 PM   #8
Travis86
Member
 
Registered: Dec 2002
Location: The land of GMT -6
Distribution: OS X, PS2 Linux, Ubuntu, IRIX 6.5
Posts: 399

Original Poster
Rep: Reputation: 31
I'm using PHP. I don't think there's an API call with that, I know there is one in Java. I may just have to rewrite it in Java. 'Course I'd have to wait until I learn Java better....
 
Old 01-11-2004, 10:09 AM   #9
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 31
As another alternative that could work for any DB system, you could just keep your own tables which describe your user-created tables for you. Kind of like the MySQL and SQL Server system tables, but in your own way so that it is stored the same for any DB backend.
 
Old 01-13-2004, 12:23 PM   #10
vasudevadas
Member
 
Registered: Jul 2003
Location: Bedford, UK
Distribution: Slackware 11.0, LFS 6.1
Posts: 519

Rep: Reputation: 30
Quote:
Originally posted by mfeat
you could also do:

select column_name, data_type
from all_tab_columns
where table_name = 'my_table'

some documentation on all_tab_columns:
http://216.239.37.104/search?q=cache...=en&ie=UTF-8#7
Does that work for all relational databases, or does that apply only to Oracle's data dictionary, I wonder?
 
Old 01-13-2004, 01:25 PM   #11
Strike
Member
 
Registered: Jun 2001
Location: Houston, TX, USA
Distribution: Debian
Posts: 569

Rep: Reputation: 31
PHP eh? You can use mysql_list_fields to get the field names. And then you can use mysql_field_type to get the type

Granted, you can "cheat" and use the MySQL-specific "DESCRIBE" query, but using API calls is the standard way of doing it across languages and RDBMSes.
 
Old 08-15-2004, 04:20 PM   #12
jchristman
Member
 
Registered: Mar 2003
Distribution: Fedora Core 3
Posts: 125

Rep: Reputation: 15
mysql_list_fields

I have been trying to use this function, but I have never been able to can any one show me some actual working code where the function mysql_list_fields is working and how they used it.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reporting Services and Non-Tabular Data carl.waldbieser Programming 1 10-11-2005 09:10 AM
SQL question: Need to add a column from another table Hivemind Programming 3 09-19-2005 06:02 PM
'Last Post' Column showing incorrect data Shade LQ Suggestions & Feedback 1 04-20-2005 01:31 AM
help on finding the number that has max occurrences in a column [sql query] zeppelin Programming 6 06-15-2004 01:41 PM
how to modify a column type in postgrest Bheki Linux - General 3 03-01-2004 03:37 AM


All times are GMT -5. The time now is 01:47 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration