MS SQL Server & PERL :: Data Getting Rounded

bynagari has asked for the wisdom of the Perl Monks concerning the following question:

Hi,
Thanks a lot in advance for your help.

Here is the problem I have:

We have two patients in MS SQL Server database.

Doug --> 1425648232001530
Margie --> 1425648232001532

The problem is PERL is returning the IDs from DB after rounding the ID. For example: for Margie, the ID is 1425648232001532. But PERL pulls it as 1425648232001530, which is Doug's ID.

Here is the code:

sub db_query_ {
my ($query,$dbh)=(@_);
Math::BigInt->accuracy(undef);
Math::BigInt->round_mode(undef);
Math::BigInt->precision(undef);
$dbh->ping() or $dbh=Mconnect();
#$dbh->{LongReadLen} = 400000;
#$dbh->LongReadLen(4000);
$dbh->{LongTruncOk} = 0;
my $sth = $dbh->prepare($query);
$sth->execute() or die "on DB query -$query- error sql:".$sth->errstr;
my $res = $sth->fetchall_arrayref();
#print Dumper ($res);
return $res;
}

sub sql2_1{
my ($self,$con)=(@_);
my $sql="exec PATIENT_DETAILS_SSP '$PatientPhone', '$CareLocationDID', '$SSN4', '$BirthYear' ";
to_log($self,"SQL2.1: $sql", 5);
my $res = db_query_($sql, $con);
to_log($self,"SQL2.1: Results:: $res", 3);
#print Dumper($res);
return $res;
}

The data from DB is coming as "1.42564823200153e+15" which is missing the 2 at the end.
Can you please suggest a solution?

Comment on MS SQL Server & PERL :: Data Getting Rounded

Replies are listed 'Best First'.
Re: MS SQL Server & PERL :: Data Getting Rounded by Old_Gray_Bear (Bishop) on Oct 27, 2009 at 23:21 UTC
If these are really, truly ID numbers (randomly assigned and having to other purpose in life that to be used as keys into the Data Base), then they should be defined as a character-string, not as numeric data. Think about it, when was the last time you added together your Master-Card number and your Visa number? The fact that a piece of data consists entirely of numbers does not automatically mean that it is a meaningful number subject to the whims of Arithmetic and Algebra. ---- I Go Back to Sleep, Now. OGB	[reply]
Re^2: MS SQL Server & PERL :: Data Getting Rounded by glasswalk3r (Friar) on Oct 27, 2009 at 23:41 UTC
Or, if you can't modify how data is stored in the table(s), just modify the stored procedure to convert the numbers to characters. Alceu Rodrigues de Freitas Junior --------------------------------- "You have enemies? Good. That means you've stood up for something, sometime in your life." - Sir Winston Churchill	[reply]
Re: MS SQL Server & PERL :: Data Getting Rounded by mje (Curate) on Oct 28, 2009 at 09:33 UTC
What DBD driver are you using? Most DBDs obtain data as strings in which case this would not happen unless you attempted to do some arithmetic with them afterwards. Unless the thing looking like a number really is a number you need to do some arithmetic on then you should treat it as a string. I would be very interested to know what DBD and driver you are using that produced this result.	[reply]