I don't think DBI will ever return anything but Perl native/atomic datatypes from a query.

If you want automatic conversion ("inflation") of column values to custom objects, you will have to do that yourself, either by using some kind of ORM or by writing your own fetch/inflation routine. You could inspect the catalog to automate this inflation.

Personally, I suspect that the transfer from/to the database is done as strings anyway, as the Math::Bigint likely gets converted to its string representation before it gets transferred to the database driver.

Have you considered using Math::Int128 and binary(16)?

Good catch :)

This really is history repeating. Last time Math::int64 was recommended. So I'll definitely give this a go.

May all your objects be blessed.

Math:Int128 didn't work out for me under Strawberry PERL.

The build tests failed during compilation.

Anyway I came up with the following.

use strict;
use Benchmark::Timer;
use Math::BigInt;


our $twoto32=Math::BigInt->bone();
$twoto32 <<= 32;

our $twoto64=Math::BigInt->bone();
  $twoto64 <<= 64;
  
my $t=Benchmark::Timer->new();
$t->start('overall');
 
 for (my $j=10000;$j<100000;$j++) {
my $test_address='0x'.'ABCD' x 6 . '123'.$j;
#my $test_address='0x'.'4142' x 6 . '30313233'; # check for bit and by
+te order => ascii "ABABABABABAB0123"
$t->start('new_bigint');
my $bigint=Math::BigInt->new($test_address);
$t->stop('new_bigint');

$t->start('convert_to');
my $binary16=bigint_to_varbinary16( $bigint);
#print "binary16".$binary16." length ".length($binary16)."\n";
$t->stop('convert_to');
$t->start('convert_from');
my $bigint2= varbinary16_to_bigint($binary16);
$t->stop('convert_from');

$t->start('convert_to_64bit');
my $binary16=bigint_to_varbinary16_64bit( $bigint);
#print "binary16".$binary16." length ".length($binary16)."\n";
$t->stop('convert_to_64bit');
$t->start('convert_from_64bit');
my $bigint2= varbinary16_to_bigint_64bit($binary16);
$t->stop('convert_from_64bit');

#print $bigint." ".$bigint2."\n";
}
 $t->stop('overall');
 
 print $t->reports;
 
 exit 0;
 
 
 sub bigint_to_varbinary16 {
  my $bigint = shift;
  my $my_copy=$bigint->copy(); # create a new bigint that can get mung
+ed
  my @oa; #output array
  my $i=4;

  while($i>1) {
      $i--;
    #my ($q,$r) = $my_copy->brsft(32); # shift off 4 octets on the rig
+ht. In my install $r is NOT set!
    my ($q,$r) = $my_copy->bdiv($twoto32); # shift off 4 octets on the
+ right 
    $oa[$i]= pack 'N',$r; # 32 bits in network bit order (MSB first)
    if ($i==1) {
      $oa[0]= pack 'N',$q; # saves one bdiv
      last;
    }
  }
  return join('',@oa);
}

sub varbinary16_to_bigint {
    my $binary16=shift;
    my $result = Math::BigInt->new(0);
    my $i=0;
    my $p=0;
    while ($i<4) {
        $result <<= 32 if $i>0; #!4294967296;
        $result += unpack ('N', substr $binary16, $p , 4);
        $p+=4;
        $i++;
    } ;
    return $result;
}


sub bigint_to_varbinary16_64bit {
  my $bigint = shift;
  my $my_copy=$bigint->copy(); # create a new bigint that can get mung
+ed

  my ($q,$r) = $my_copy->bdiv($twoto64); # shift off 8 octets on the r
+ight. brsft does not set remainder on my test machine, hence bdiv

  return reverse join('', pack('Q',$r), pack('Q',$q)); # Q is little e
+ndian on my machine. I want DB SELECT to also be able to perform comp
+arison operators.
}

sub varbinary16_to_bigint_64bit {
    my $binary16=shift;
    my $string = reverse $binary16;
    my $result = Math::BigInt->bzero();
    $result += unpack ('Q', substr $string, 8 , 8);
    $result <<= 64; 
    $result += unpack ('Q', substr $string, 0 , 8);
    return $result;
}
[download]

Unsurprisingly, 64 bit pack and unpack is more efficient. I just wish there was an equivalent built in BigInt function to do this, and this was also built into DBI. But I ain't got time/skills to do that so I'll have to roll my own.

results:

new_bigint90000 trials of new_bigint (4.193s total), 46us/trial
convert_to90000 trials of convert_to (17.848s total), 198us/trial
convert_from90000 trials of convert_from (31.125s total), 345us/trial
convert_to_64bit90000 trials of convert_to_64bit (7.648s total), 84us/
+trial
convert_from_64bit90000 trials of convert_from_64bit (13.012s total),1
+44us/trial
[download]

Math:Int128 didn't work out for me under Strawberry PERL.

Math::Int128 requires a compiler supporting 128bit integers. In practice that means it should work under the 64bit version of Strawberry Perl but not under the 32bit one.

Math:Int128 didn't work out for me under Strawberry PERL

It's incompatible with 32-bit Strawberry Perl but should be trivial to build and install on 64-bit Strawberry Perl.

Cheers,
Rob

As already identified, get yourself a 64-bit perl.

I cannot conceive of any reason to use a 32-bit perl (or most anything else) under a 64-bit OS; it's like buying a V12 Ferrari and disconnecting the plugs to one bank of the V.

---

With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

"Science is about questioning the status quo. Questioning authority". I knew I was on the right track :)

In the absence of evidence, opinion is indistinguishable from prejudice. Not understood.

Maybe you need an "integer". This link can help you out 12.1.1 Numeric Type Overview

I'm afraid that won't work for 128 bit numbers.

The largest integer currently available in MySQL is 'bigint' (nothing to do with Math::Bigint) which can store 64 bits. PERL's internal integer type is also 64 bit. Until recently that wasn't even 64 bit clean and suffered overflows (thankfully now fixed). That's why I had to resort to Math::Bigint. My previous code combined two SQL bigint columns to produce one PERL Math::Bigint but that's slow both for the DB and PERL.

Then this will help binary(16).