David N Murray wrote:
If you don't know the columns that will be returned, I presume you're
doing something akin to 'select * from'. At any rate, if you know the
table name, why not ask the database for the table info?
#!/usr/bin/perl
use DBI;
my $dbh = DBI->connect("dbi:mysql:test",'','');
die "Unable to connect:\n" . $DBI::errstr . "\n" if (! defined($dbh));
# 3rd param is table name, 4th means all columns
my $sth = $dbh->column_info(undef, undef, 'test', '%');
while (my @ar = $sth->fetchrow_array()) {
print join(":", @ar), "\n";
}
$sth->finish;
$dbh->disconnect;
which is akin to
mysql> desc test;
HTH,
Dave
On Aug 24, Dan scribed:
Hi all.
I'm working on an object to handle synchronizing fields on a
Gtk2::GladeXML - generated form with a database ( 2nd release is just
around the corner ).
I need to get a field list when my object is created, however in some
cases I'll be running a query which returns NO records on the first run.
I have bene using:
foreach my $field ( keys %{$self->{records}[$self->{position}]} ) {
# do stuff
}
but this won't work if I don't have any records.
I only see 2 options:
1) Run a separate query with NO where clause and a LIMIT clause ( MySQL
) and get the field names from there, and then run the real query. This
will work but I don't like it.
2) Keep a flag to indicate if I've done all the stuff in my loop ( which
must happen only once ) and run it at the first opportunity ( the first
time I run a query that gets results ). This is also a little messy, but
better than option 1.
Is there any other way to get the field list without having any records?
Dan
Not certain I understand your issue, but, from the DBD::mySQL
POD (as posted on CPAN):
my $sth = $dbh->prepare("SELECT * FROM $table");
if (!$sth) {
die "Error:" . $dbh->errstr . "\n";
}
if (!$sth->execute) {
die "Error:" . $sth->errstr . "\n";
}
my $names = $sth->{'NAME'};
my $numFields = $sth->{'NUM_OF_FIELDS'};
for (my $i = 0; $i < $numFields; $i++) {
printf("%s%s", $i ? "," : "", $$names[$i]);
}
print "\n";
Is there something you're trying to do that this doesn't ?
NOTE: this is the std. way of fetching field info for a resultset,
tho some drivers/dbms's don't populate that info until after execute(),
but a properly conformant driver should populate it (and
TYPE, PRECISION, SCALE, and NULLABLE) even if the resultset is empty.
Dean Arnold
Presicient Corp.
