Perl DBI interpretation MySQL column_info ()

Question

Perl DBI interpretation MySQL column_info ()

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

The $ dbh-> column_info method returns information in encoded form. I have looked 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:

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, can you find the mapping of data type codes to strings? Should I use DATA_TYPE, TYPE_NAME or SQL_DATA_TYPE? Should I use NULLABLE or IS_NULLABLE, and why are these two flavors?

I can appreciate the difficulty of documenting (let alone implementing) a generic interface for databases. But I am wondering if anyone knows a reference manual for using MySQL specific DBI?

UPDATE 1:

Tried to shed more light by fetching all the information with 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 see a lot 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 is:

How do I get the corresponding names / descriptions of this metadata?
How do I fetchall_arrayref

just do what I need using characters rather than integers? (I tried fetchall_arrayref([qw/COLUMN_NAME DATA_TYPE/])

and got all the undef

s back, now I just wave my guess.)

UPDATE 2:

Now I dig into 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                                                                                                                                 
);

They match exactly what fetchall_arrayref returns. Now I see that I have four options for exploring the datatype, so let's see if any of the codes are registered.

UPDATE 3:

DBI Recipes are a very nice addition to the CPAN DBI documentation about returning information back to Perl (especially {select | fetch} {row | all} _ {hash | array}.)

+3

perl dbi metadata

Chap 16 Feb 13 at 19:58

source to share

1 answer

JParadiso · Answer 1 · 2013-02-18T05:46:32+0000

This will help you define values for data types. I usually use data_type to determine how to handle a column based on its type.

Then you need to look at the MySQL datatype key below and get the hash value. Then look at the DBI table below and match the data name to get the data type value. Example: BIGINT is an INTEGER type that corresponds to 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 refers to an array of integer values representing the international standard values for the respective data types. An array of integers has a length equal to the number of columns selected in the original expression, and can be referenced in a similar way to the example of the NAME attribute shown earlier.

The default 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

Although these numbers are fairly standardized, [61] the way drivers display their native types for these standard types varies greatly. Native types that do not match one of these types can be mapped to the range officially reserved for Perl DBI use: -9999 to -9000.

Perl DBI interpretation MySQL column_info ()

More articles: