Arunesh Singh Arunesh Singh - 10 days ago 7
MySQL Question

Why does DBI implicitly change integers to strings?



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

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.

Comments