Arunesh Singh Arunesh Singh - 1 year ago 75
Perl Question

Perl: Why DBI is implicitly changing integers into String?

I have a

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.

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

sub db_get_results {
my $self = shift;
my $qry = shift;
my $sth = $self->{dbh}->prepare($qry);
my @return = ();
while ( my @line = $sth->fetchrow_array ) {
push @return, \@line;
return \@return;


$VAR1 = [
'North Central',

is implictly converting all the int's into String?

Answer Source

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 configuring perl to support 64bit integers and long-double floating point types was common. 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download