Re: column_info, mysql, and SET/ENUM values

[Scott R. Godin]

Tim Bunce wrote:

>> $ 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.
> 
> It's a minor bug in DBD::mysql. Try adding "use DBI qw(:sql_types);" just
> after "package DBD::mysql::db;" in DBD/mysql.pm
> 
> Looks like it's not fixed in the DBD-mysql-2.9003_1 release either.
> 

After doing the above.. => 

I haven't abstracted these away into object methods yet, as I'm not yet
fully comfortable with the whole object-oriented thing, and am not quite
sure of the best way to do this, however as a subroutine in my scripts this
should work fine. 

Took a bit of doing, and fooling around with it to come up with these, and I
hope the documentation for these is updated soon. It wasn't immediately
obvious to me that column_info returns a DBI::st object that I had to
further handle with other additional DBI methods, for example. My first
attempt was just to read the values returned, thinking that $sth would be a
simple hashref. I was rapidly disabused of this notion, and came up with
the following. :)

(If anyone is willing to show me how I would take these subroutines and
convert them to subclassed DBI methods, I'd be interested in seeing how it
should be done properly.) :-)

#!/usr/bin/perl
# col_info.plx 
# methods for obtaining values from SET or ENUM columns. 
# 
use warnings;
use strict;
use DBI;

my $dbh = DBI->connect(
                'DBI:mysql:allclassics', 
                'webdragon', 
                'PASSWORD', 
                {RaiseError => 1}
        )
        or die $DBI::errstr;

END {undef $dbh if $dbh }

sub get_column_values ($$$) {# dbhandle, table name, and column name
        my ($dbh1, $table, $column, @vals) = @_;
        
        my $sth = $dbh1->column_info( undef, undef, $table, '%'); 

        $sth->execute();

        while ( my $ref = $sth->fetchrow_hashref() )
        {
                next unless $ref->{COLUMN_NAME} eq $column;
                @vals = @{$ref->{mysql_values}};
        }

        $sth->finish();

        return wantarray ? @vals : scalar(@vals);
}

sub get_value_hashref ($$) {# dbhandle, table name
        my ($dbh1, $table, %hash) = @_;
        my $sth = $dbh1->column_info(undef, undef, $table, '%');
        $sth->execute();
        while ( my $ref = $sth->fetchrow_hashref() )
        {
                next unless $ref->{TYPE_NAME} =~ /SET|ENUM/i;
                $hash{ $ref->{COLUMN_NAME} }{type} = $ref->{TYPE_NAME};
                $hash{ $ref->{COLUMN_NAME} }{'values'} 
                        = [ @{$ref->{mysql_values}} ];
        }
        return \%hash;
}

## -=- Example one

my @default_keywords = sort( 
                get_column_values($dbh, 'products', 'keywords') 
        );

print join ", ", map { "'$_'" } @default_keywords;
print "\n\n";

## -=- Example two

my $ref = get_value_hashref($dbh, 'products');

foreach my $key (keys %$ref)
{
        print "Column Name: $key - Column Type: $ref->{$key}{type}\n";
        print join ", ", @{ $ref->{$key}{'values'} }, "\n\n";
}

## -=-

# other stuff that was just for testing purposes
# and to help me figure out how this column_info thing worked. 
#
#       next unless $ref->{COLUMN_NAME} eq $column;
# print all info returned by column_info() for a particular column
#       foreach (sort keys %$ref)
#       {
#               print "$_ => ";
#               print  defined $ref->{$_} ? "$ref->{$_}\n" : "\n";
#       }