|
Posted by Zhiliang Hu on October 15, 2004, 1:02 pm
Please log in for more thread options
I hope this is not new but I didn't find it on archive.
I am writing a perl program to display column names of a table from
MySQL. I thought this is simple (would you agree? :) But several
trials throw me into fog.
The query is: $query = $dbh->prepare("desc eventbl}"). The way to get
things out -- I tried several approaches but no luck so far:
(1)
A test output:
while (@content = $query->fetchrow_array) {
print "@contentn";
}
This prints out things like:
------------------------------
Item_ID int(3) PRI 0
Invent_ID varchar(8) YES MUL
Design varchar(255) YES
------------------------------
OKey, it appears good! Go on ...
(2)
I thought I could parse each line to get the first field (column
names):
while (@content = $query->fetchrow_array) {
foreach $line(@content) {
@parts = split(/s+/,$line);
print "$parts[0]n";
}
}
but I got:
------------------------------
Item_ID
int(3)
PRI
0
Invent_ID
varchar(8)
YES
MUL
Design
etc.
------------------------------
(3)
Then I tried to see what is in each line of "describe" output:
while (@content = $query->fetchrow_array) {
foreach $line(@content) {
print "$linen";
}
}
I got exactly the same output as in trial (2) above.
I wonder what's going on here? I am lost as how the MySQL output is
textured.
Could someone shed some lights on how to extract the column names out?
("Simple" as that. But I think the way to get it work has more
general applications).
Thanks in advance!
Zhiliang
|
|
Posted by Bill Karwin on October 15, 2004, 2:05 pm
Please log in for more thread options
Zhiliang Hu wrote:
> Could someone shed some lights on how to extract the column names out?
If nothing else works more satisfactorily, you could get a sample row as
a hashref:
my $hashref = $dbh->selectrow_hashref("SELECT * FROM myTable LIMIT 1");
my @column_names = keys(%$hashref);
Regards,
Bill K.
|
|
Posted by Jonathan Leffler on October 16, 2004, 5:47 am
Please log in for more thread options Bill Karwin wrote:
> Zhiliang Hu wrote:
>
>> Could someone shed some lights on how to extract the column names out?
>
>
> If nothing else works more satisfactorily, you could get a sample row as
> a hashref:
>
> my $hashref = $dbh->selectrow_hashref("SELECT * FROM myTable LIMIT 1");
> my @column_names = keys(%$hashref);
That'll work (though it is conventional to use $dbh for a database
handle and not for statement handles), but you can also interrogate
the handle directly:
$sth->
See 'perldoc DBI'.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
|
|
Posted by Rhesa Rozendaal on October 16, 2004, 7:08 pm
Please log in for more thread options Jonathan Leffler wrote:
> Bill Karwin wrote:
>
>> Zhiliang Hu wrote:
>>
>>> Could someone shed some lights on how to extract the column names out?
>>
>> my $hashref = $dbh->selectrow_hashref("SELECT * FROM myTable LIMIT 1");
>> my @column_names = keys(%$hashref);
>
> That'll work
> (though it is conventional to use $dbh for a database handle and not for
statement handles),
Ah, but "selectrow_*" are database handle methods. The corresponding statement
handle methods are called "fetchrow_*".
> but you can also interrogate the
> handle directly:
>
> $sth->
That would be $sth-> or $sth-> or $sth->
> See 'perldoc DBI'.
right back at ya :)
To get back to the original question, getting the column names for a specific
table is best done with the metadata functions, such as column_info.
Something like this should do it:
my $sth = $dbh->column_info(undef, undef, 'eventbl', '%');
while(my $r = $sth->fetchrow_hashref) {
print "$r->n";
}
Other interesting attributes might be TYPE_NAME, COLUMN_SIZE, NULLABLE. This
method is more portable.
Rhesa
|
|
Posted by ___cliff rayman___ on October 15, 2004, 9:03 pm
Please log in for more thread options Zhiliang Hu wrote:
>The query is: $query = $dbh->prepare("desc eventbl}"). The way to get
>things out -- I tried several approaches but no luck so far:
>
>(1)
>A test output:
>
>while (@content = $query->fetchrow_array) {
> print "@contentn";
>}
>
>This prints out things like:
>------------------------------
> Item_ID int(3) PRI 0
> Invent_ID varchar(8) YES MUL
> Design varchar(255) YES
>------------------------------
>
>
so, you know that @content is an array, because your are referencing it
correctly. therefore, you need to access the array elements using array
notation:
print $content[0]; # this will print Item_ID for the first line
print $content[1]; # this willprint int(3) for the first line, etc..
the print command happens to be smart enough to print the array as text
for you, but that is not how it is stored or individually accessed.
remember,
what you see is not what you get with computers. what you see is what
the software has been programmed to show you. in this case, the print
command was programmed to show you the array as simple text.
you may need to read one of the O'Reilly perl books before continuing
much further, or the perl documenation itself:
perldoc perl
perldoc perldata
perldoc perlvar
cliff
--
_____cliff_rayman_____cliff_@_rayman_._com_____
|
| Similar Threads | Posted | | Using Algorithm::NaiveBayes Properly | January 18, 2008, 11:06 am |
| Averaging data | September 27, 2005, 9:47 pm |
| [RFC] Data::Counter | May 8, 2008, 10:42 am |
| Excel Data conversion | July 27, 2004, 2:37 pm |
| data structure from XML::LibXML | October 6, 2004, 6:22 pm |
| Image data parsing | October 27, 2004, 3:36 pm |
| AuthCookieNTLM - No Post Data | December 6, 2004, 9:25 am |
| AuthCookieNTLM - No Post Data | December 6, 2004, 10:01 am |
| Extracting value from data within an element. | November 30, 2005, 5:26 am |
| MakeMaker and data files | March 22, 2006, 8:02 am |
|
XML Sitemap