PERL Programming - Perl DBI and MySQL Fieldname Problem

Author

Perl DBI and MySQL Fieldname Problem

Alex

2004-08-03, 3:57 pm

Hi,

I write:

# Get the field headers
# $sql = qq(select * from cartb;);
my $sth = &dbh -> prepare ($sql);
my @fields;
for my $field (@{$sth->{'NAME'}}) {
push @fields, $field;
}
$sth -> execute;

but i get a failure and the following error log:

[Tue Aug 03 20:20:59 2004] [error] 2928: ModPerl::Registry: Can't set
DBI::st=HASH(0x186b520)->{NAME}: unrecognised attribute or invalid value at
/projects/perl/importcaruk.com/modules/Car.pm line 86.

Surely this is textbook stuff... How come it won't let me grab the
fieldnames? Moving the $sth->execute doesn't help, and I know the database
connection is fine.

Any help appreciated.

Thanks,

Alex
http://www.alexite.com

Andrew Palmer

2004-08-04, 3:55 am

"Alex" <alex@alexbanks.com> wrote in message
news:410fe634$0$303$cc9e4d1f@news.dial.pipex.com...
> Hi,
>
> I write:
>
> # Get the field headers
> # $sql = qq(select * from cartb;);

For starters, a key line of code seems to be commented out.

> my $sth = &dbh -> prepare ($sql);
> my @fields;
> for my $field (@{$sth->{'NAME'}}) {
> push @fields, $field;
> }
> $sth -> execute;
>
> but i get a failure and the following error log:
>
> [Tue Aug 03 20:20:59 2004] [error] 2928: ModPerl::Registry: Can't set
> DBI::st=HASH(0x186b520)->{NAME}: unrecognised attribute or invalid value
at
> /projects/perl/importcaruk.com/modules/Car.pm line 86.
>
> Surely this is textbook stuff...

It is, literally. Read the documentation to DBI.pm. You have several
problems. You didn't just "miss something"

> How come it won't let me grab the
> fieldnames? Moving the $sth->execute doesn't help, and I know the database
> connection is fine.
>
> Any help appreciated.
>
> Thanks,
>
>
> Alex
> http://www.alexite.com
>
>

Matt Garrish

2004-08-04, 3:56 pm

"Alex" <alex@alexbanks.com> wrote in message
news:410fe634$0$303$cc9e4d1f@news.dial.pipex.com...
> Hi,
>
> I write:
>
> # Get the field headers
> # $sql = qq(select * from cartb;);

Helps to not comment out important pieces of code.

> my $sth = &dbh -> prepare ($sql);

&dbh? The above code is not doing whatever you think it is doing...

> my @fields;
> for my $field (@{$sth->{'NAME'}}) {
> push @fields, $field;
> }
> $sth -> execute;

You're now trying to access a result set *before* you execute the sql
statement?

>
> but i get a failure and the following error log:
>

I'm sure you'll get many other error messages as well. Personally, I would
write the above as follows: (untested)

my $sel_sth = $dbh->prepare("SELECT * FROM cartb");

$sel_sth->execute();

my $href = $sel_sth->fetchrow_hashref();

$sel_sth->finish();

foreach my $key (keys %$href) {
# each $key will be a column header
}

Matt

Andrew Palmer

2004-08-05, 3:56 pm

"Matt Garrish" <matthew.garrish@sympatico.ca> wrote in message
news:Pt5Qc.33103$Vm1.667649@news20.bellglobal.com...
>
> "Alex" <alex@alexbanks.com> wrote in message
> news:410fe634$0$303$cc9e4d1f@news.dial.pipex.com...
>
> Helps to not comment out important pieces of code.
>
>
> &dbh? The above code is not doing whatever you think it is doing...
>
>
> You're now trying to access a result set *before* you execute the sql
> statement?
>
>
> I'm sure you'll get many other error messages as well. Personally, I would
> write the above as follows: (untested)
>
> my $sel_sth = $dbh->prepare("SELECT * FROM cartb");
>
> $sel_sth->execute();
>
> my $href = $sel_sth->fetchrow_hashref();
>
> $sel_sth->finish();
>
> foreach my $key (keys %$href) {
> # each $key will be a column header
> }

This code stops after fetching one row, no matter how many are returned by
the db query. The "fetchrow" part should go in a loop.

Second, there's (probably) no reason to fetch a hashref over an arrayref
(normally you know which fields you need, and don't ever "SELECT *"). A hash
is less efficient than an array, and the fields will come up out of order if
you loop over it. fetchrow_arrayref() is probably preferred.

Matt Garrish

2004-08-05, 8:55 pm

"Andrew Palmer" <andrewpalmer@email.com> wrote in message
news:NHsQc.2290$zc1.1213@fe40.usenetserver.com...
>
> "Matt Garrish" <matthew.garrish@sympatico.ca> wrote in message
> news:Pt5Qc.33103$Vm1.667649@news20.bellglobal.com...
would[color=darkred]
>
> This code stops after fetching one row, no matter how many are returned by
> the db query. The "fetchrow" part should go in a loop.
>

No kidding? He asked how to get the column names. Why would you loop through
an entire result set when only one row is needed?

>
> Second, there's (probably) no reason to fetch a hashref over an arrayref
> (normally you know which fields you need, and don't ever "SELECT *"). A
hash
> is less efficient than an array, and the fields will come up out of order
if
> you loop over it. fetchrow_arrayref() is probably preferred.
>

I take it you haven't done much database work in your life. There are many
cases where you have no idea what table will be accessed or why. Try writing
a gui wrapper over a MySQL database. You won't have very meaningful data
sets without knowing what columns the data belong to.

Matt

Andrew Palmer

2004-08-06, 3:56 pm

"Matt Garrish" <matthew.garrish@sympatico.ca> wrote in message
news:8_yQc.42367$Vm1.953434@news20.bellglobal.com...
>
> "Andrew Palmer" <andrewpalmer@email.com> wrote in message
> news:NHsQc.2290$zc1.1213@fe40.usenetserver.com...
> would
by[color=darkred]
>
> No kidding? He asked how to get the column names.

Sorry, I lost track of what was going on. (Is "Alex" a he or a she?)

> Why would you loop through
> an entire result set when only one row is needed?
>

No rows are needed!!! You do not have to retrieve a row of data to get
column names (besides, there is no way to know the column order that way).
OP was trying to do it the correct way, with the "$sth->{'NAME'}" array
reference.

Also, I think your way could be truncated to something like:

my $href = $dbh->selectrow_hashref("SELECT * FROM cartb");

foreach my $key (keys %$href) {
# each $key will be a column header
}

> hash
order[color=darkred]
> if
>
> I take it you haven't done much database work in your life. There are many
> cases where you have no idea what table will be accessed or why. Try
writing
> a gui wrapper over a MySQL database. You won't have very meaningful data
> sets without knowing what columns the data belong to.
>

Sheesh. I think we've hit everything. Try this:

# Get the field headers
$sql = qq(select * from cartb;);
my $sth = $dbh -> prepare ($sql);
$sth -> execute;
my @fields;
for my $field (@{$sth->{'NAME'}}) {
push @fields, $field;
}
$sth->finish();

Andrew Palmer

2004-08-06, 8:55 pm

Or just (duh!)

my $sql = qq(select * from cartb;);
my $sth = $dbh -> prepare ($sql);
$sth -> execute;
my @fields=@{$sth->{'NAME'}};
$sth->finish();

If there really is a reason to copy that array, that is.

Sponsored Links