Why does DBI implicitly change integers to strings?

Question

I have a MySQL table with following structure.

alid       bigint(20),
ndip       varchar(20),
ndregion   varchar(20),
occ_num    int(3),
Delta_Flag int(1)

After selecting data from the table, I am getting all the data quoted and as a string value.

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper;
use FindBin;
use lib $FindBin::Bin;
use Database;

my $pwd = $FindBin::Bin;

my $db  = Database->new( 'mysql', "$pwd/config.ini" );
my $db1 = Database->new( 'mysql', "$pwd/config2.ini" );

my @tables = qw( AutoTT_AlarmStatus_Major1 );

for my $table ( @tables ) {

    my $query_select = "SELECT alid, ndip, ndregion, occ_num, Delta_Flag FROM $table LIMIT 1";
    my $result = $db->db_get_results( $query_select );

    print Dumper( $result );

    for my $item ( @{$result} ) {

        # Here I want to prepare, bind and insert this data
        # into other table with same structure
    }
}

Database.pm

sub db_get_results {
    my $self = shift;
    my $qry  = shift;

    my $sth  = $self->{dbh}->prepare( $qry );
    $sth->execute();

    my @return = ();
    while ( my @line = $sth->fetchrow_array ) {
        push @return, \@line;
    }

    return \@return;
}

Output:

$VAR1 = [
          [
            '1788353',
            '10.34.38.12',
            'North Central',
            '1',
            '1'
          ]
        ];

Why is DBI implicitly converting all integers to strings?

Answer 1

As @choroba notes in his answer, it's not the DBI that's doing anything with the data. It's just passing through what the driver module (DBD::mysql in your case) returned.

In the General Interface Rules & Caveats section of the DBI docs it says:

Most data is returned to the Perl script as strings. (Null values are returned as undef.) This allows arbitrary precision numeric data to be handled without loss of accuracy. Beware that Perl may not preserve the same accuracy when the string is used as a number.

I wrote that back in the days before it was common to configure perl to support 64-bit integers, and long-double floating point types were unusual. These days I recommend that drivers return values in the most 'natural' Perl type that doesn't risk data loss.

For some drivers that can be tricky to implement, especially those that support returning multiple result sets, with different numbers of columns, from a single handle, as DBD::mysql does.

I skimmed the DBD::mysql docs but didn't see any mention of this topic, so I looked at the relevant code where I can see that the current DBD::mysql is returning numbers as numbers. There's also lots of references to recent changes in this area in the Change log.

Perhaps you're using an old version of DBD::mysql and should upgrade.

Answer 2

That's how the DBD driver for MySQL works. Other databases might behave differently. For example, in SQLite, numbers remain numeric:

#!/usr/bin/perl
use warnings;
use strict;

use DBI;
use Data::Dumper; 

my $dbh = 'DBI'->connect('dbi:SQLite:dbname=:memory:', q(), q());
$dbh->do('CREATE TABLE t (id INT, val VARCHAR(10))');

my $insert = $dbh->prepare('INSERT INTO t VALUES (?, ?)');
$insert->execute(@$_) for [ 1, 'foo' ], [ 2, 'bar' ];

my $query = $dbh->prepare('SELECT id, val FROM t');
$query->execute;
while (my $row = $query->fetchrow_arrayref) {
    print Dumper($row);
}

__END__
$VAR1 = [
          1,
          'foo'
        ];
$VAR1 = [
          2,
          'bar'
        ];

Answer 3

This is nothing DBI does in general. As it already was pointed out many database-drivers (DBD::xy) of the DBI system convert numbers to strings. AFAIK its not possible to avoid that.

What you can do is ask the statement handle for corresponding native type or (easier in your case) wether the column of your resultset is numeric in the mysql-DB or not. Here is an example

Given this basic database:

mysql> create table test (id INT,text VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test VALUES (1,'lalala');
Query OK, 1 row affected (0.00 sec)

you can lookup wether the column is numeric or not by using the driver-specific field 'mysql_is_num':

Reference to an array of boolean values; TRUE indicates, that the respective column contains numeric values. (from DBD::mysql)

#!/usr/bin/env perl 

use strict;
use warnings;
use utf8;

use DBI;
use DBD::mysql;
use Data::Dumper;

my $dsn = "DBI:mysql:database=test;host=localhost";
my $dbh = DBI->connect($dsn,'user','pass') or die "$!";
my $sql = "SELECT * FROM test WHERE id = ?";
my $sth = $dbh->prepare($sql);

$sth->execute(1);

my $num_fields = $sth->{'NUM_OF_FIELDS'};
my $num_mask = $sth->{'mysql_is_num'};

my $result;
my $cnt = 0;
while (my $line = $sth->fetchrow_arrayref){
    for (my $i = 0; $i < $num_fields; $i++){
        if ($num_mask->[$i]){
            $line->[$i] + 0;
        }
        $result->[$cnt] = $line;
    }
    $cnt++;    
}

print Dumper($result);

I hope this helps. As it was written in a hurry, please excuse the style. Of course i'm open to any suggestions.