http://qs321.pair.com?node_id=449501

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

I'm sure this is a pretty easy one, but I've spent a long time searching on the internet and still haven't resolved the problem. I am trying to extract the lengths of the fields from a mysql table using DBI. Based on the documentation I read I wrote this code:
$sth=$dbh->prepare('SELECT * FROM userReports WHERE 1=0'); $sth->execute(); my $flh=$sth->{SCALE}; my @lengths=@{$flh}; print "@lengths";
If I substitute NAME for SCALE then I get an array of column names as expected. However, using SCALE I just get an array full of zeros. I wonder what the problem with this is? Also, is there a better way of doing this?

Replies are listed 'Best First'.
Re: field lengths of database tables
by dbwiz (Curate) on Apr 20, 2005 at 07:33 UTC

    Instead of SCALE, use PRECISION.

    Check the DBI docs for some caveats about its applicability with numerical types.

    To get reliable results, you should use $sth->{PRECISION} in coordination with the values of $sth->{TYPE}

Thanks! - Re to field lengths of database tables
by polettix (Vicar) on Apr 20, 2005 at 14:51 UTC
    I want to thank you for this post. Just yesterday I was wondering how I could get column names, and a first search in DBI docs did not give me a positive answer - go figure where I was looking at :)

    Update: retitled as per site administrators' suggestion (reason in How do I compose an effective node title?).

    Flavio (perl -e "print(scalar(reverse('ti.xittelop@oivalf')))")

    Don't fool yourself.
Re: field lengths of database tables
by kgraff (Monk) on Apr 20, 2005 at 19:40 UTC

    A quick and dirty way get table information is send the describe MySQL command. It doesn't port well to other databases but gives you most of the information in one query.

Re: field lengths of database tables
by Anonymous Monk on Apr 20, 2005 at 07:50 UTC
    SCALE isn't documented in DBD::mysql, try LENGTH

      Thanks for the info dbwiz... You're right the array does fill up properly when I use PRECISION, though it does look like a bit of added work is necessary to get the data in the right format. I take it theres no way around this then?

      BTW Anonymous Monk, I couldn't get LENGTH to work at all, the program just falls over saying 'unrecognised attribute'.