How to map the coloumn type integers to names in DBI

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

Update:

I have used bobr's solution, but I found another problem, the returned name is a user defined type name instead of a regular data type. For example using "sp_columns" I can see a column's type is float, but the type_info() return is ABC_TY(which is a user defined type). I am using Sybase, anyone encountered similar problem?

I am using DBI and the following code to get the table column type:

    my $sql = "SELECT * FROM $tablePath WHERE 0 = 1";

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->finish();
    
    my $all_types = $sth->{TYPE};
[download]

The returned value is a reference to an array which contains many integers indicating the data type of each column, these integers are not useful unless I turn them into names, anyone knows how can I turn these data type integers into more descriptive names?

arturo (Vicar) had mentioned this in http://www.perlmonks.org/?node_id=184120, but I still don't find any detail document.

Thanks a lot!

Comment on How to map the coloumn type integers to names in DBI Download Code

Replies are listed 'Best First'.
Re: How to map the coloumn type integers to names in DBI by gmargo (Hermit) on Dec 29, 2009 at 12:47 UTC
I had to get the TYPE info from the statment handle before calling `$sth->finish();`. Also from the DBI documentation: how to get the SQL names for those data type integers: `{ no strict 'refs'; foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) { printf "%s=%d\n", $_, &{"DBI::$_"}; } }` [download]	[reply] [d/l] [select]
Re^2: How to map the coloumn type integers to names in DBI by littlehorse (Sexton) on Dec 30, 2009 at 02:27 UTC
Thanks	[reply]
Re: How to map the coloumn type integers to names in DBI by bobr (Monk) on Dec 29, 2009 at 12:16 UTC
Hello, I found following in DBI docs: `@names = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYP +E} }` [download] Did not try it myself, though. -- Roman	[reply] [d/l]
Re^2: How to map the coloumn type integers to names in DBI by littlehorse (Sexton) on Dec 30, 2009 at 02:01 UTC
It works, thanks, I think it's time for me to read the DBI document more carefully:)	[reply]