RE: retrieving column names from a table using DBI module
If you're using mysql try a 'DESCRIBE TABLE' query first. If not mysql find a similar command in your database and use that first, Once you have the name and position of the column names you can do what you want with them While associating them to the data in the subsequent select statement quite easily. I usually accomplish this by putting my describe statement results in has that has an index number and Column name and using the index nuumber of the select results to associate them however I need. Dan > > > > Thanks Dave > But i made the mistake of caling it a table but actually it > is a view and table_info ethod does not work for that. Also i > tried to run table_info for a table but it does not return > the column names. > There is a column info method for tables to do this. But it > does not work for views. Please help manisha > > Dave K <[EMAIL PROTECTED]> wrote: > Manisha > Hello > Hi > I am working with Perl CGi. There is a query where i have to > select all columns in a table and display the data on the web > page. the query is something like "select * from > But the problem is I am not able to get the column names. > I am using the DBI module. Is there a way to get column > names? I read the responses from Dan, Timothy and Brian, and > they all have sound advice. If you would like to see another > approach you can visit: http://www.geocities.com/k2001evad/pindex.html > and cut and paste scripts for Oracle, MySql and Postgres DBMS > that demonstrate another (not nessecarily better) way to get > meta data from those RDBMs. If your driver supports the > table_info method that may be the most direct way to get to > the column names. Check the DBI docs for more information. HTH > > > > > - > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, and more >
Re: retrieving column names from a table using DBI module
Thanks Dave But i made the mistake of caling it a table but actually it is a view and table_info ethod does not work for that. Also i tried to run table_info for a table but it does not return the column names. There is a column info method for tables to do this. But it does not work for views. Please help manisha Dave K <[EMAIL PROTECTED]> wrote: Manisha Hello Hi I am working with Perl CGi. There is a query where i have to select all columns in a table and display the data on the web page. the query is something like "select * from But the problem is I am not able to get the column names. I am using the DBI module. Is there a way to get column names? I read the responses from Dan, Timothy and Brian, and they all have sound advice. If you would like to see another approach you can visit: http://www.geocities.com/k2001evad/pindex.html and cut and paste scripts for Oracle, MySql and Postgres DBMS that demonstrate another (not nessecarily better) way to get meta data from those RDBMs. If your driver supports the table_info method that may be the most direct way to get to the column names. Check the DBI docs for more information. HTH - Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, and more
Re: retrieving column names from a table using DBI module
On Wed, 19 Feb 2003 08:15:27 -0500, Dave K wrote: Hi Dave >advice. If you would like to see another approach you can visit: >http://www.geocities.com/k2001evad/pindex.html Thanx for the demo. -- Cheers Ron Savage, [EMAIL PROTECTED] on 20/02/2003 http://savage.net.au/index.html
Re: retrieving column names from a table using DBI module
On Wed, 19 Feb 2003 09:13:48 +, [EMAIL PROTECTED]
wrote:
>
>Hi,
Hi Philip
>fetchrow_hashref returns a reference to a hash, with each hash
>having the
>column name as it's keys.
>
>$row = $sth->fetchrow_hashref;
>
>print "$row->{NAME},$row->{ADDRESS}"
Sure but there are other things to consider:
1) table_info() may be experimental, but it does work
2) table_info() is aimed at solving the problem - there is no need to
use other code which accidently solves the problem
3) Advice given was careless if not wrong. The suggesion to use
something like 'select * from table' selects all rows, although it
does not retrieve them. Whoever made that suggestion should have read
the docs which recommended 'select * from table where 1=0', which is
a vendor-independent trick to limit the output. And,yes, I'm aware
that suggesting this trick appears to contradict the comment above
about not using code with works accidently. If you think those 2
pieces of advice are really in conflict, I suggest you need to think
things though.
4)TMTOWTDI
--
Cheers
Ron Savage, [EMAIL PROTECTED] on 20/02/2003
http://savage.net.au/index.html
Re: retrieving column names from a table using DBI module
Manisha Hello Hi I am working with Perl CGi. There is a query where i have to select all columns in a table and display the data on the web page. the query is something like "select * from But the problem is I am not able to get the column names. I am using the DBI module. Is there a way to get column names? I read the responses from Dan, Timothy and Brian, and they all have sound advice. If you would like to see another approach you can visit: http://www.geocities.com/k2001evad/pindex.html and cut and paste scripts for Oracle, MySql and Postgres DBMS that demonstrate another (not nessecarily better) way to get meta data from those RDBMs. If your driver supports the table_info method that may be the most direct way to get to the column names. Check the DBI docs for more information. HTH
Re: retrieving column names from a table using DBI module
Hi,
fetchrow_hashref returns a reference to a hash, with each hash having the
column name as it's keys.
$row = $sth->fetchrow_hashref;
print "$row->{NAME},$row->{ADDRESS}"
or whatever.
Similarly, you could use fetchall_hashref or selectall_hashref. I
understand that the hashref functions are not as efficient as the array ref
functions, so, if performance is an issue, it may be worth performing a
select from the database system tables to determine column names (and
types?) before using the arrayref functions.
There is a table_info function that will give you the table definitions,
but the docs say that this feature is experimental, so use it with caution!
Regards
Phil
---
Direct: +44 (0) 20 7325 1653GDP:325-1653
Mobile: +44 (0) 77 4876 0299Home: +44 (0) 1444 891365
RTM:800 625 1275 or + 1(1) 334 420 2916 Code: 435994
"Brian McCain"
, <[EMAIL PROTECTED]>
sters.com>cc:
Subject: Re: retrieving column names
from a table using DBI
18/02/2003 module
23:31
It seems to me that there should be a more direct way to do it, but you
could do:
my $sql = "SELECT * FROM FOO";
my $arrayref = $dbh->selectall_arrayref($sql, { Columns => {} });
my @names;
foreach my $key (keys %{$arrayref[0]}){
push @names, $key;
}
Which would leave you with a reference to an array containing one hashref
per result row and, again, an array containing the names of all your
columns.
Brian McCain
- Original Message -
From: "Manisha Gupta" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 18, 2003 2:57 PM
Subject: retrieving column names from a table using DBI module
Hello
I am working with Perl CGi. There is a query where i have to select all
columns in a table and display the data on the web page.
the query is something like
"select * from
But the problem is I am not able to get the column names.
I am using the DBI module. Is there a way to get column names?
Thanks
Re: retrieving column names from a table using DBI module
On Tue, 18 Feb 2003 17:57:12 -0500, Manisha Gupta wrote: >Hello Hi Manisha I see you've been given 3 answers. I suggest reading DBI.html. The data you want is clearly documented in DBI.html (generate this from DBI.pm), under the heading 'Statement Handle Attributes' -- Cheers Ron Savage, [EMAIL PROTECTED] on 19/02/2003 http://savage.net.au/index.html
Re: retrieving column names from a table using DBI module
It seems to me that there should be a more direct way to do it, but you
could do:
my $sql = "SELECT * FROM FOO";
my $arrayref = $dbh->selectall_arrayref($sql, { Columns => {} });
my @names;
foreach my $key (keys %{$arrayref[0]}){
push @names, $key;
}
Which would leave you with a reference to an array containing one hashref
per result row and, again, an array containing the names of all your
columns.
Brian McCain
- Original Message -
From: "Manisha Gupta" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 18, 2003 2:57 PM
Subject: retrieving column names from a table using DBI module
Hello
I am working with Perl CGi. There is a query where i have to select all
columns in a table and display the data on the web page.
the query is something like
"select * from
But the problem is I am not able to get the column names.
I am using the DBI module. Is there a way to get column names?
Thanks
RE: retrieving column names from a table using DBI module
Manisha,
I think this is in the archives, or it was discussed recently.
Anyway, here's a snippet that shows how to do it:
for ($i=0;$i<$csr->{NUM_OF_FIELDS};$i++) {
print "Field $i: $csr->{NAME}->[$i]\n";
}
if you get your results back in an arrayref:
while ( $arrayRef = $csr->fetchrow_arrayref) {
you can easily match the names to the column data.
Regards,
Tim
PS this info is also in "Programming the Perl DBI". There are other staement
handle attributes as well: NAME_uc, NAME_lc, TYPE, PRECISION etc..
-Original Message-
From: Manisha Gupta [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 18, 2003 5:57 PM
To: [EMAIL PROTECTED]
Subject: retrieving column names from a table using DBI module
Hello
I am working with Perl CGi. There is a query where i have to select all
columns in a table and display the data on the web page.
the query is something like
"select * from
But the problem is I am not able to get the column names.
I am using the DBI module. Is there a way to get column names?
Thanks
RE: retrieving column names from a table using DBI module
See the DBI documentation it's something like fetch_rowhash.
Or what I've done before is a describe query that assigned the column name to a number
( for reference later )
Then when You do select * ...
@row = fetch_rowarray ...
my $i;
foreach $item(@row) {
print "Column Name - $columnhash[$n] : Column Value = $item \n";
$i++;
}
Soemthing like that anyway
Dan
> -Original Message-
> From: Manisha Gupta [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 18, 2003 4:57 PM
> To: [EMAIL PROTECTED]
> Subject: retrieving column names from a table using DBI module
>
>
> Hello
> I am working with Perl CGi. There is a query where i have to
> select all columns in a table and display the data on the
> web page. the query is something like
> "select * from
> But the problem is I am not able to get the column names.
> I am using the DBI module. Is there a way to get column names?
>
> Thanks
>
