Tim Bunce wrote:
> I think the latest DBD::mysql contains my column info code and
> thus does return set & enum values.
>
> Tim.
>
> On Tue, Oct 14, 2003 at 09:01:14AM +1000, Ron Savage wrote:
>> On Mon, 13 Oct 2003 13:54:46 -0400, Scott R. Godin wrote:
>>
>> Hi Scott
>>
>> >?A while back in February, Tim and Ron were discussing DBD::mysql
>> >?and column_info..
>>
>> I'm still interested in info in this area.
>>
>> >?This is the only thing I could find that *may* relate to my
>> >?question, so I'm asking here for further clarification.
>>
>> This is the place.
>>
>> >?It's not clear in the docs (at least not to me) whether I can have
>> >?column_info return the possible values of a SET or ENUM column,
>> >?without hard-coding them into my scripts.
>>
>> I too would like to know that.
>>
Well I've tried several iterations of this, and the DBD::mysql and DBI
current releases don't accept a column value on
my $sth = $dbh->column_info( undef, $schema, $table, $column);
but instead require me to use '%'.
However this results in:
$ perl col_info.pl
Undefined subroutine &DBD::mysql::db::SQL_VARCHAR called at
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/mysql.pm line
337.
which I don't quite understand. I tried adding :sql_types to the DBI
declaration, but no go.
here's my test program: maybe you can point out what I'm doing wrong.
$ cat col_info.pl
#!/usr/bin/perl
use warnings;
use strict;
use DBI qw/:sql_types/;
my $dbh = DBI->connect('DBI:mysql:allclassics', 'webdragon', 'PASSWORD',
{RaiseError => 1})
or die $DBI::errstr;
my $sth = $dbh->column_info( undef, 'allclassics', 'products', '%') or die;
__END__
now I have managed to otherwise glean this info using the following program,
but I'd prefer to have a preset DBI method to do this with.
$ cat allclassics.pl
#!/usr/bin/perl
use warnings;
use strict;
use DBI qw/:sql_types/;
my $dbh = DBI->connect('DBI:mysql:allclassics', 'webdragon', 'PASSWORD',
{RaiseError => 1}) or die $DBI::errstr;
my $sth = $dbh->prepare('SHOW COLUMNS FROM products LIKE ?');
$sth->execute('keywords');
my $ref = $sth->fetchrow_hashref('NAME_lc');
my $value = $ref->{type};
$value =~ m#\((.*)\)#;
my @values = map { s/'//g; $_ } split /,/, $1;
$sth->finish();
use CGI qw/:standard/;
use CGI::Pretty qw/:html3/;
print start_html(),
scrolling_list(
-name=> "keywords",
-values=> [ sort @values ],
-size=>10,
-multiple=>'true',
),
end_html();
I'd be interested to know what I'm doing wrong in the earlier program, and
how I would go about asking for the values otherwise, as it's still not
very clear in the DBI docs.
just so it's easier to test, the SET declaration in the products table looks
like this:
keywords SET('', 'carving', 'fountain', 'children', 'child',
'animal', 'statue', 'statuette', 'tiki', 'mermaid', 'furniture',
'bookends', 'planter', 'coatrack', 'unbrella stand', 'totem pole', 'toy',
'rocker', 'plane', 'mounted head', 'buddha', 'standing', 'sitting',
'lying', 'trophy', 'desktop', 'horse', 'dolphin', 'indian', 'cigar',
'butler', 'waiter', 'cat', 'rooster', 'pig', 'dog', 'chicken', 'cow',
'small', 'medium', 'large', 'huge', 'man', 'woman', 'male', 'female',
'girl', 'boy') DEFAULT '' NOT NULL,
--scott
