On Fri, 21 Feb 2003 17:03:51 +0000, Tim Bunce wrote:
>On Thu, Feb 20, 2003 at 06:57:37PM +0000, Tim Bunce wrote:
>>Does anyone have a reasonably working implementation of
>>column_info() for mysql?
>
>I guess not, so I've written my own basic one and appended it below
>for feedback.
Hi Tim
Many thanx. I had to make a few patches to get it to work :-)
This is for mysql.pm V 2.1021.
1) I inserted your code (minus the 1; __END__) into mysql.pm at line
271, between sub _ListTables and package DBD::mysql::st;
2) I added
use DBI qw/:sql_types/;
at line 161 of mysql.pm because you used SQL_VARCHAR() etc
3) At line 272, I simplified the name of the sub:
sub column_info {
4) Starting at what is now line 295, we see:
my $desc = $dbh->selectall_arrayref("DESCRIBE $table", {
Columns=>{} });
my $ordinal_pos = 0;
foreach my $row (@$desc) {
Inside this for loop, I changed all references from $row->{field} to
$row->{Field}. This applies to all, repeat all, keys of %$row, not
just Field. I can't image why you assume lower case keys here.
5) At the end of this sub, I added code to return something :-):
\%col_info;
6) In my call to column_info(), I made sure the parameters survived
the sanity checks at the start of your sub.
Hey, presto! It works.
Demo:
-----><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 metadata
{
my($dbh) = @_;
my(@table_name) = sort $dbh -> tables();
print "Tables: \n";
print map{"$_. \n"} @table_name;
print "\n";
my($sth) = $dbh -> column_info(undef, undef, $table_name[1], '%');
print "Table: $table_name[1]. Column metadata as returned by \$dbh
-> column_info(undef, undef, '$table_name[1]', '%'). \n";
for my $column (sort keys %$sth)
{
print "Column: $column. \n";
print map{"Attribute: $_ => " . ($$sth{$column}{$_} ?
$$sth{$column}{$_} : '') . "\n"} sort keys %{$$sth{$column} };
print "\n";
}
} # End of metadata.
# -----------------------------------------------
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,
}
);
metadata($dbh);
}
catch Error::Simple with
{
my($error) = 'Error::Simple: ' . $_[0] -> text();
chomp($error);
print $error;
};
-----><8-----
Output:
-----><8-----
Tables:
book.
dog.
hobbit.
industry.
publisher.
sqle.
wine.
winemaker.
Table: dog. Column metadata as returned by $dbh -> column_info(undef,
undef, 'dog', '%').
Column: breed.
Attribute: COLUMN_DEF =>
Attribute: COLUMN_NAME => breed
Attribute: COLUMN_SIZE => 50
Attribute: DATA_TYPE => 12
Attribute: IS_NULLABLE => NO
Attribute: NULLABLE =>
Attribute: ORDINAL_POSITION => 2
Attribute: SQL_DATA_TYPE => 12
Attribute: TABLE_NAME => dog
Attribute: TYPE_NAME => varchar(50)
Attribute: mysql_is_pri_key =>
Column: dog_id.
Attribute: COLUMN_DEF =>
Attribute: COLUMN_NAME => dog_id
Attribute: COLUMN_SIZE => 11
Attribute: DATA_TYPE => 4
Attribute: IS_NULLABLE => NO
Attribute: NULLABLE =>
Attribute: NUM_PREC_RADIX => 10
Attribute: ORDINAL_POSITION => 1
Attribute: SQL_DATA_TYPE => 4
Attribute: TABLE_NAME => dog
Attribute: TYPE_NAME => int(11)
Attribute: mysql_is_pri_key => 1
Column: official.
Attribute: COLUMN_DEF =>
Attribute: COLUMN_NAME => official
Attribute: COLUMN_SIZE => 1
Attribute: DATA_TYPE => 1
Attribute: IS_NULLABLE => YES
Attribute: NULLABLE => 1
Attribute: ORDINAL_POSITION => 3
Attribute: SQL_DATA_TYPE => 1
Attribute: TABLE_NAME => dog
Attribute: TYPE_NAME => char(1)
Attribute: mysql_is_pri_key =>
-----><8-----
--
Cheers
Ron Savage, [EMAIL PROTECTED] on 23/02/2003
http://savage.net.au/index.html
