Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

Getting column size/type From DBI

by BigGuy (Friar)
on Jul 22, 2002 at 16:40 UTC ( #184120=perlquestion: print w/replies, xml ) Need Help??

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

<DISCLAIMER> this is more of a database question than a straight perl question </DISCLAIMER>
I have been doing a lot of database coding in perl lately and have a DBD::Pg question. Is there an easy way to get column size and/or column type from the DBI? Currently I am using a very poor system where i keep track of the largest length in each column as i work the data over, which leads to slower runtimes and more memory usage and in general just looks bad if anyone else were ever to look at my code. I use pgaccess and ipgsql and both of these have features that will tell you the column type and size which leads me to think that the DBI must have a similar feature.

"One World, one Web, one Program" - Microsoft promotional ad
"Ein Volk, ein Reich, ein Fuhrer" - Adolf Hitler

Replies are listed 'Best First'.
Re: Getting column size/type From DBI
by LTjake (Prior) on Jul 22, 2002 at 16:59 UTC
    According to the DBI docs:

    column_info Warning: This method is experimental and may change. $sth = $dbh->column_info( $catalog, $schema, $table, $column ); Returns an active statement handle that can be used to fetch informati +on about columns in specified tables
    Give that a shot.

    earthboundmisfit points out a, perhaps better, DBD::Pg specific answer with pg_size. note: use pg_type to get column data types.
      column_info is the generic answer.

      Unfortunately I've found that only a few DBD drivers actually implement it (for example DBD::Sybase will only implement this in the next release...)


Re: Getting column size/type From DBI
by arturo (Vicar) on Jul 22, 2002 at 17:20 UTC

    The DBI defines methods for getting metadata. If you want to get info on a given table, though, you have to go through a statement handle. It's a little bit involved after that. You execute a "select * from table_name" (or, if you know the field names you're interested in, just get those) and then the driver makes other info available to you via the {NAME} and {TYPE} attributes of the statement handle.

    The *statement handle* attribute {NAME} is a reference to an array of the names of the fields returned by the current statement, and there's also the Probably the most straightforward way of doing this looks something like this:
    # you've already got your DB handle: my @tables = $db->tables(); foreach my $table (@tables) { my $sql = "select * from $table"; my $sth = $db->prepare( $sql ); $sth->execute(); # check for errors, either set RaiseError or have + a "die" clause here print "Structure of $table \n\n"; my $num_fields = $sth->{NUM_OF_FIELDS}; for ( my $i=0; $i< $num_fields; $i++ ) { my $field = $sth->{NAME}->[$i]; my $type = $sth->{TYPE}->[$i]; my $precision = $sth->{PRECISION}->[$i]; # e.g. VARCHAR(50) ha +s a precision of 50 print "Field $field is of type $type, with precision $ precisi +on\n"; } $sth->finish(); }

    You can turn those numeric types into more descriptive names, I don't know anything specifically about DBI::Pg, and my knowledge is derived from the very useful Programming the Perl DBI, which has been surpassed on the metadata issue via newer versions of the DBI, but I see that those newer versions of the DBI define type_info methods that can help you figure out which types you're dealing with.


    I mistrust all systematizers and avoid them. The will to a system shows a lack of integrity -- F. Nietzsche

Re: Getting column size/type From DBI
by earthboundmisfit (Chaplain) on Jul 22, 2002 at 17:03 UTC
    I'm not a pg user, but the Cheetah book lists, under driver specific attributes and methods on page 312, pg_size, which sounds like what you need.

    Returns a reference to an array of integer values for each column. The integer shows the storage (not display) size of the column in bytes. Variable length columns are indicated by -1.

    - Programming the Perl DBI

Re: Getting column size/type From DBI
by ehdonhon (Curate) on Jul 22, 2002 at 17:05 UTC
Re: Getting column size/type From DBI
by BigD (Scribe) on Jul 22, 2002 at 17:01 UTC
    New DBI version 1.30 incorporates a new functionality, still experimental, called, $dbh->column_info (check perldoc DBI documentation for the correct syntax), where you can know the size of the column as well as the type. However, I have not used it yet, so I do not know exactly how it works. Just note that this is still experimental and it might not work under pgaccess.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://184120]
Approved by VSarkiss
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (1)
As of 2023-10-01 23:03 GMT
Find Nodes?
    Voting Booth?

    No recent polls found