Re: DBD::mysql - column_info() ?

[Ron Savage]

On Thu, 20 Feb 2003 18:57:37 +0000, Tim Bunce wrote:

Hi TIm

>Does enyone have a reasonably working implementation of
>column_info() for mysql?

Do you mean Have I implemented it? or do you mean Can I get it to
work? The answers are No and No.

DBI V 1.32.
DBD:mysql V 2.1021.

template.pl:
-----><8-----
#!/usr/bin/perl
#
# Name:
#       template.pl.
#
# Purpose:
#       Test MySQL & $dbh -> table_info().
#
# Note:
#       tab = 4 spaces || die.
#
# Author:
#       Ron Savage <[EMAIL PROTECTED]>
#       http://savage.net.au/index.html

use strict;
use warnings;

use DBI;
use Error qw/:try/;

# -----------------------------------------------

sub get_column_names
{
        my($dbh, $table_name) = @_;

        my($sql) = "select * from $table_name where 1=2";
        my($sth) = $dbh -> prepare($sql);

        $sth -> execute();

        #$field_count   = $$sth{'NUM_OF_FIELDS'};

        my(@column_name) = @{$$sth{'NAME'} };

        $sth -> finish();

        \@column_name;

}       # End of get_column_names.

# -----------------------------------------------

sub tables_1
{
        my($dbh)                = @_;
        my(@table_name) = $dbh -> tables();

        print "Tables and their columns as returned by \$dbh -> tables().
\n";

        my($column_name, $first_column);

        for my $table_name (@table_name)
        {
                print "Table: $table_name. \n";

                $column_name    = get_column_names($dbh, $table_name);
                $first_column   = $$column_name[0] if (! $first_column);

                print "Column: $_. \n" for @$column_name;
                print "\n";
        }

        print "For the 1st table, $table_name[0], call \$dbh ->
column_info('', '', $table_name[0], ''). \n";

        my($sth) = $dbh -> column_info('', '', $table_name[0], '');

        print "Column info: \n";
        print map{"$_ => $$sth{$_}. \n"} sort keys %$sth;
        print "\n";

        print "For the 1st table, $table_name[0], and the 1st column,
$first_column, call \$dbh -> column_info('', '', $table_name[0],
$first_column). \n";

        $sth = $dbh -> column_info('', '', $table_name[0], $first_column);

        print "Column info: \n";
        print map{"$_ => $$sth{$_}. \n"} sort keys %$sth;
        print "\n";

}       # End of tables_1.

# -----------------------------------------------

sub tables_2
{
        my($dbh, $catalog, $schema, $table, $type) = @_;
        my($sth) = $dbh -> table_info($catalog, $schema, $table, $type);

        print "Table info as returned by \$dbh -> table_info($catalog,
$schema, $table, $type). \n";

        if ($catalog eq '%')
        {
                print "Catalog names: \n";
                print map{"$_ => $$sth{$_}. \n"} sort keys %$sth;
        }
        elsif ($schema eq '%')
        {
                print "Schema names: \n";
                print map{"$_ => $$sth{$_}. \n"} sort keys %$sth;
        }
        elsif ($type eq '%')
        {
                print "Table types: \n";
                print map{"$_ => $$sth{$_}. \n"} sort keys %$sth;
        }

        print "\n";

}       # End of tables_2.

# -----------------------------------------------

sub test
{
        my($dbh) = @_;

        tables_1($dbh);
        tables_2($dbh, '%', '', '', '');
        tables_2($dbh, '', '%', '', '');
        tables_2($dbh, '', '', '', '%');

}       # End of test.

# -----------------------------------------------

try
{
        my($dbh) = DBI -> connect
        (
                "DBI:mysql:test:127.0.0.1", 'root', 'toor',
                {
                        AutoCommit                      => 1,
                        HandleError                     => sub {Error::Simple -> 
record($_[0]); 0},
                        LongReadLen                     => 150_000,
                        LongTruncOk                     => 0,
                        PrintError                      => 0,
                        RaiseError                      => 1,
                        ShowErrorStatement      => 1,
                }
        );

        test($dbh);
}
catch Error::Simple with
{
        my($error) = 'Error::Simple: ' . $_[0] -> text();
        chomp($error);
        print $error;
};
-----><8-----
Output on my system:
-----><8-----
Tables and their columns as returned by $dbh -> tables().
Table: book.
Column: book_id.
Column: title.
Column: author.
Column: publisher_id.
Column: date_published.
Column: isbn.
Column: rating.
Column: comment.

Table: dog.
Column: dog_id.
Column: breed.
Column: official.

Table: hobbit.
Column: id.
Column: parent_id.
Column: name.
Column: code.
Column: _url.
Column: _node_id.

Table: industry.
Column: industry_id.
Column: industry_code.
Column: industry_name.

Table: publisher.
Column: publisher_id.
Column: publisher_name.

Table: sqle.
Column: sqle_id.
Column: sqle_name.

Table: wine.
Column: wine_id.
Column: wine_name.
Column: winemaker_id.
Column: vineyard.
Column: year.
Column: rating.
Column: reviewed.

Table: winemaker.
Column: winemaker_id.
Column: winemaker_name.

For the 1st table, book, call $dbh -> column_info('', '', book, '').
Column info:

For the 1st table, book, and the 1st column, book_id, call $dbh ->
column_info('', '', book, book_id).
Column info:

Table info as returned by $dbh -> table_info(%, , , ).
Catalog names:

Table info as returned by $dbh -> table_info(, %, , ).
Schema names:

Table info as returned by $dbh -> table_info(, , , %).
Table types:

-----><8-----

:-((.
--
Cheers
Ron Savage, [EMAIL PROTECTED] on 21/02/2003
http://savage.net.au/index.html