Interpreting Perl DBI MySQL column_info()

Question

I'm trying to write Perl code that accepts bindings for a SQL INSERT statement, and identifies problems that might cause the INSERT to be rejected for bad data, and fixes them. To do this, I need to get and interpret column metadata.

The $dbh->column_info method returns the info in a coded form. I've pored through the official CPAN documentation but am still confused.

my $sth_column_info
        = $dbh->column_info( $catalog, $schema, $table, undef );
    my $columns_aoh_ref = $sth_column_info->fetchall_arrayref(
        {   COLUMN_NAME      => 1,
            DATA_TYPE        => 1,
            NULLABLE         => 1,
            ORDINAL_POSITION => 1,
            COLUMN_DEF       => 1,
        }
    );
    say $table;
    for my $href (@$columns_aoh_ref) {
        my @list;
        while ( my ( $k, $v ) = each %$href ) {
            push @list, "$k=" . ( $v // 'undef' );
        }
        say join '|', @list;
    }

Output is:

dw_phone
NULLABLE=0|COLUMN_DEF=undef|DATA_TYPE=4|ORDINAL_POSITION=1|COLUMN_NAME=phone_id
NULLABLE=0|COLUMN_DEF=undef|DATA_TYPE=4|ORDINAL_POSITION=2|COLUMN_NAME=phone_no
NULLABLE=1|COLUMN_DEF=undef|DATA_TYPE=4|ORDINAL_POSITION=3|COLUMN_NAME=phone_ext
NULLABLE=0|COLUMN_DEF=undef|DATA_TYPE=1|ORDINAL_POSITION=4|COLUMN_NAME=phone_type
NULLABLE=0|COLUMN_DEF=undef|DATA_TYPE=1|ORDINAL_POSITION=5|COLUMN_NAME=phone_location
NULLABLE=1|COLUMN_DEF=undef|DATA_TYPE=1|ORDINAL_POSITION=6|COLUMN_NAME=phone_status
NULLABLE=0|COLUMN_DEF=undef|DATA_TYPE=11|ORDINAL_POSITION=7|COLUMN_NAME=insert_date
NULLABLE=0|COLUMN_DEF=undef|DATA_TYPE=11|ORDINAL_POSITION=8|COLUMN_NAME=update_date

Where - for example - does one find a mapping of data type codes to strings? Should I be using DATA_TYPE, TYPE_NAME, or SQL_DATA_TYPE? Should I be using NULLABLE or IS_NULLABLE, and why the two flavors?

I can appreciate the difficulty of documenting (let alone implementing) a universal interface for databases. But I wonder if anyone knows of a reference manual for using the DBI that's specific to MySQL?

UPDATE 1:

Tried to shed more light by retrieving all info using an array rather than a hash:

    my $sth_column_info
        = $dbh->column_info( $catalog, $schema, $table, undef );

    my $aoa_ref = $sth_column_info->fetchall_arrayref; # <- chg. to arrayref, no parms
    say $table;
    for my $aref (@$aoa_ref) {
        my @list = map $_ // 'undef', @$aref;
        say join '|', @list;
    }

Now I can see lots of potentially useful information mixed in there.

dw_contact_source
undef|dwcust1|dw_contact_source|contact_id|4|BIGINT|20|undef|undef|10|0|undef|undef|4|undef|undef|1|NO|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|1|bigint(20)|undef|0
undef|dwcust1|dw_contact_source|company_id|4|SMALLINT|6|undef|undef|10|0|undef|undef|4|undef|undef|2|NO|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|1|smallint(6)|undef|0
undef|dwcust1|dw_contact_source|contact_type_id|4|TINYINT|4|undef|undef|10|0|undef|undef|4|undef|undef|3|NO|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef||tinyint(4)|undef|0
undef|dwcust1|dw_contact_source|insert_date|11|DATETIME|19|undef|0|undef|0|undef|undef|9|-79|undef|4|NO|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef||datetime|undef|0
undef|dwcust1|dw_contact_source|update_date|11|DATETIME|19|undef|0|undef|0|undef|undef|9|-79|undef|5|NO|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef||datetime|undef|0

So my question would be:

How do I get the corresponding names/descriptions of these metadata?
How do I fetchall_arrayref just what I need, using symbols rather than integers? (I tried fetchall_arrayref([qw/COLUMN_NAME DATA_TYPE/]) and got back all undefs; now I'm just flailing about guessing.)

UPDATE 2:

Now I'm digging around in DBD::mysql.pm and I found a very interesting array:

my @names = qw(                                                                                                                                             
    TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME                                                                                                            
    DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS                                                                                            
    NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF                                                                                                              
    SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH                                                                                                        
    ORDINAL_POSITION IS_NULLABLE CHAR_SET_CAT                                                                                                               
    CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM COLLATION_NAME                                                                               
    UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME                                                                                          
    SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY                                                                                                        
    DTD_IDENTIFIER IS_SELF_REF                                                                                                                              
    mysql_is_pri_key mysql_type_name mysql_values                                                                                                           
    mysql_is_auto_increment                                                                                                                                 
);

These correspond precisely to what is returned by fetchall_arrayref. Now I can see I have four choices for learning data type, so let's see if any of the codes are documented.

UPDATE 3:

DBI Recipes is a very nice adjunct to CPAN DBI documentation about retrieving info back into Perl (Especially the {select|fetch}{row|all}_{hash|array} methods.)

metacpan.org/module/DBD::mysql might be a good place to start. — Perleone
Thanks -- if I'm reading it right, the section STATEMENT HANDLES would be the most relevant to me. But the technique seems to require that you first execute a SELECT * on the table. (Haven't checked to see if it even works on an empty table.) Using the column_info method against the table as a whole seems like the better approach, which is why I'd prefer learning how to use that. — Chap

JParadiso JParadiso · Accepted Answer · 2013-02-18T05:46:32

This will help you determine the values for the data_types. I normally use the data_type to determine how to handle a column based on its type.

You then need to look at the MySQL data type key below and get the hash value. Then look at the DBI table below and match up the data name to get the data type value. Example: a BIGINT is an INTEGER type which matches SQL_INTEGER so the DATA_TYPE value is 4,

DBD::MySQL
### ANSI datatype mapping to mSQL datatypes
%DBD::mysql::db::ANSI2db = ("CHAR"          => "CHAR",
            "VARCHAR"       => "CHAR",
            "LONGVARCHAR"   => "CHAR",
            "NUMERIC"       => "INTEGER",
            "DECIMAL"       => "INTEGER",
            "BIT"           => "INTEGER",
            "TINYINT"       => "INTEGER",
            "SMALLINT"      => "INTEGER",
            "INTEGER"       => "INTEGER",
            "BIGINT"        => "INTEGER",
            "REAL"          => "REAL",
            "FLOAT"         => "REAL",
            "DOUBLE"        => "REAL",
            "BINARY"        => "CHAR",
            "VARBINARY"     => "CHAR",
            "LONGVARBINARY" => "CHAR",
            "DATE"          => "CHAR",
            "TIME"          => "CHAR",
            "TIMESTAMP"     => "CHAR"
           );

DBI.pm TYPE The TYPE attribute contains a reference to an array of integer values representing the international standard values for the respective datatypes. The array of integers has a length equal to the number of columns selected within the original statement, and can be referenced in a similar way to the NAME attribute example shown earlier.

The standard values for common types are:

SQL_CHAR             1
SQL_NUMERIC          2
SQL_DECIMAL          3
SQL_INTEGER          4
SQL_SMALLINT         5
SQL_FLOAT            6
SQL_REAL             7
SQL_DOUBLE           8
SQL_DATE             9
SQL_TIME            10
SQL_TIMESTAMP       11
SQL_VARCHAR         12
SQL_LONGVARCHAR     -1
SQL_BINARY          -2
SQL_VARBINARY       -3
SQL_LONGVARBINARY   -4
SQL_BIGINT          -5
SQL_TINYINT         -6
SQL_BIT             -7
SQL_WCHAR           -8
SQL_WVARCHAR        -9
SQL_WLONGVARCHAR   -10

While these numbers are fairly standard,[61] the way drivers map their native types to these standard types varies greatly. Native types that don't correspond well to one of these types may be mapped into the range officially reserved for use by the Perl DBI: -9999 to -9000.

Interpreting Perl DBI MySQL column_info()

1 Answers