DBI scanning of column definitions

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I need to scan the column definitions in a MySQL table. Then I need to separate them into 2 groups: those that should be '0' (INTs, for example) and those that should be "" (CHAR, ENUMS, VARCHAR, for example). And associate the column names with these values. Let me explain...

So if field_a is an INT, I would set up a hash, such as:

$hash{field_a} = 0;
[download]

Or if field_b is determined to be a CHAR, the hash would be:

$hash{field_b} = "";
[download]

How can I efficiently do this? Thanks, Monks.

Comment on DBI scanning of column definitions Select or Download Code

Replies are listed 'Best First'.
Re: DBI scanning of column definitions by rnahi (Curate) on Jul 16, 2005 at 09:00 UTC
If you are just using MySQL, then you can benefit from some special attributes in DBD::mysql, such as mysql_is_num. For example: $dbh->do(qq{DROP TABLE IF EXISTS test.test1}); $dbh->do(qq{CREATE TABLE test.test1( f_int int, f_char char(10), f_double double, f_bigint BIGINT, f_varchar VARCHAR(10), f_date DATE, f_timestamp timestamp )}); my $sth= $dbh->prepare(qq{SELECT * from test.test1}); $sth->execute() or die "execution error ($DBI::errstr)\n"; my @columns = @{$sth->{NAME}}; for (0 .. $#columns) { printf "%s %s numeric\n", $columns[$_], $sth->{mysql_is_num}[$_] ? "is" : "isn't"; } __END__ output: f_int is numeric f_char isn't numeric f_double is numeric f_bigint is numeric f_varchar isn't numeric f_date isn't numeric f_timestamp isn't numeric [download] HTH	[reply] [d/l]
Re: DBI scanning of column definitions by gam3 (Curate) on Jul 16, 2005 at 03:58 UTC
The Statemnt handle TYPE key can be used to do what I think you want to do. `my $dbh = DBI->connect(...); my $st = $dbh->prepare('select * from ...'); $hash_ref = $st->fetchrow_hashref; my $types = $st->{TYPE}; for my $type (@$types) { print "$type -- "; print [$dbh->type_info($type)]->[0]->{LOCAL_TYPE_NAME}, "\n"; print "\n"; } $st->finish(); $dbh->disconnect();` [download] -- gam3 A picture is worth a thousand words, but takes 200K.	[reply] [d/l]