help with big numbers and DBI
in [Perl]
|
Prev: $$$$$ READ EMAILS & EARN CREDITS TOWARDS A LAPTOP !!!
Next: Reading binary file created a C++ program inside a Perl prog
From: hymie! on 9 Feb 2010 08:47 Greetings. I'm not sure exactly where my problem is, and I'm looking for a little help. I hope my need to anonymize the info will not impact your ability to guide me. I have an MSSQL database. I connect to it with SQL Server, run a query "select content_id from log" with a "where" clause I'm not allowed to broadcast, and I get these results: 1037479785592177191 1037222160396202204 1036993281177442875 1037489555080390716 1037253823299245752 I probably need to note that "content_id" is defined in the database as a "bigint not null". I run a perl script with the same query: my $query = "select content_id from log "; my $st = $ardbh->prepare($query); $st->execute(@ARGV); while (my @row = $st->fetchrow_array) { print "$row[0]\n"; } I get this result: 1.03747978559218e+18 1.0372221603962e+18 1.03699328117744e+18 1.03748955508039e+18 1.03725382329925e+18 So I made a slight change to my script: while (my @row = $st->fetchrow_array) { $row[0] = sprintf "%ld",$row[0]; print "$row[0]\n"; } and I get this result: 1037479785592177152 1037222160396202240 1036993281177442816 1037489555080390656 1037253823299245696 Note that the last two or three digits do not match. Then I found the "bignum" module and altered my script thusly: use DBI; use bignum; my $query = "select content_id from log "; my $st = $ardbh->prepare($query); $st->execute(@ARGV); while (my @row = $st->fetchrow_array) { $row[0] = $row[0] + 0; print "$row[0]\n"; } and got these results: 1037479785592180000 1037222160396200000 1036993281177440000 1037489555080390000 1037253823299250000 So perhaps I need a module other than "bignum"? Or is there something else I need to do so that I end up with the correct numbers printed out? Or am I just doomed by the lack of precision in my machines? Thanks. --hymie! http://lactose.homelinux.net/~hymie hymie(a)lactose.homelinux.net -------------------------------------------------------------------------------
From: Steve C on 9 Feb 2010 09:26 hymie! wrote: > Greetings. I'm not sure exactly where my problem is, and I'm looking > for a little help. I hope my need to anonymize the info will not > impact your ability to guide me. > > I have an MSSQL database. I connect to it with SQL Server, run a > query "select content_id from log" with a "where" clause I'm not > allowed to broadcast, and I get these results: > > 1037479785592177191 > 1037222160396202204 > 1036993281177442875 > 1037489555080390716 > 1037253823299245752 > > I probably need to note that "content_id" is defined in the database > as a "bigint not null". > > I run a perl script with the same query: > > my $query = "select content_id from log "; > my $st = $ardbh->prepare($query); > $st->execute(@ARGV); > while (my @row = $st->fetchrow_array) > { > print "$row[0]\n"; > } > > I get this result: > 1.03747978559218e+18 > 1.0372221603962e+18 > 1.03699328117744e+18 > 1.03748955508039e+18 > 1.03725382329925e+18 > > So I made a slight change to my script: > > while (my @row = $st->fetchrow_array) > { > $row[0] = sprintf "%ld",$row[0]; > print "$row[0]\n"; > } > > and I get this result: > 1037479785592177152 > 1037222160396202240 > 1036993281177442816 > 1037489555080390656 > 1037253823299245696 > > Note that the last two or three digits do not match. > > Then I found the "bignum" module and altered my script thusly: > > use DBI; > use bignum; > my $query = "select content_id from log "; > my $st = $ardbh->prepare($query); > $st->execute(@ARGV); > while (my @row = $st->fetchrow_array) > { > $row[0] = $row[0] + 0; > print "$row[0]\n"; > } > > and got these results: > 1037479785592180000 > 1037222160396200000 > 1036993281177440000 > 1037489555080390000 > 1037253823299250000 > > So perhaps I need a module other than "bignum"? Or is there something else > I need to do so that I end up with the correct numbers printed out? > Or am I just doomed by the lack of precision in my machines? > > Thanks. > This is perl on my 32-bit desktop system: perl -V |grep use64bitint use64bitint=undef use64bitall=undef uselongdouble=undef This is from an Opteron server running CentOS 64-bit: perl -V |grep use64bitint use64bitint=define use64bitall=define uselongdouble=undef So your results may change depending on what perl build you are using.
From: ccc31807 on 9 Feb 2010 09:58 On Feb 9, 8:47 am, hy...(a)lactose.homelinux.net (hymie!) wrote: > I have an MSSQL database. I connect to it with SQL Server, run a > query "select content_id from log" with a "where" clause I'm not > allowed to broadcast, and I get these results: > > 1037479785592177191 > 1037222160396202204 > 1036993281177442875 > 1037489555080390716 > 1037253823299245752 If these are keys, then you probably want to manipulate them as strings rather than integers. Perl is perfectly happy to accept a string of any size, as long as the hardware doesn't complain, and you probably won't be performing arithmetic on your keys anyway. > my $query = "select content_id from log "; > my $st = $ardbh->prepare($query); > $st->execute(@ARGV); > while (my @row = $st->fetchrow_array) > { > print "$row[0]\n"; > > } My general approach is to save the results of the fetch operation in a hash reference ($row = $sth->fetchrow_hashref();) and then iterate through the hash. If you are doing anything even remotely sophisticated with the data, it's a lot easier to use the hash syntax rather than the array syntax. CC
From: RedGrittyBrick on 9 Feb 2010 10:25 On 09/02/2010 13:47, hymie! wrote: > Greetings. I'm not sure exactly where my problem is, and I'm looking > for a little help. I hope my need to anonymize the info will not > impact your ability to guide me. > > I have an MSSQL database. I connect to it with SQL Server, run a > query "select content_id from log" with a "where" clause I'm not > allowed to broadcast, and I get these results: > > 1037479785592177191 > 1037222160396202204 > 1036993281177442875 > 1037489555080390716 > 1037253823299245752 > > I probably need to note that "content_id" is defined in the database > as a "bigint not null". > > I run a perl script with the same query: > > my $query = "select content_id from log "; > my $st = $ardbh->prepare($query); > $st->execute(@ARGV); > while (my @row = $st->fetchrow_array) > { > print "$row[0]\n"; > } > > I get this result: > 1.03747978559218e+18 > 1.0372221603962e+18 > 1.03699328117744e+18 > 1.03748955508039e+18 > 1.03725382329925e+18 > > So I made a slight change to my script: > > while (my @row = $st->fetchrow_array) > { > $row[0] = sprintf "%ld",$row[0]; > print "$row[0]\n"; > } > > and I get this result: > 1037479785592177152 > 1037222160396202240 > 1036993281177442816 > 1037489555080390656 > 1037253823299245696 > > Note that the last two or three digits do not match. > > Then I found the "bignum" module and altered my script thusly: > > use DBI; > use bignum; > my $query = "select content_id from log "; > my $st = $ardbh->prepare($query); > $st->execute(@ARGV); > while (my @row = $st->fetchrow_array) > { > $row[0] = $row[0] + 0; > print "$row[0]\n"; > } > > and got these results: > 1037479785592180000 > 1037222160396200000 > 1036993281177440000 > 1037489555080390000 > 1037253823299250000 > > So perhaps I need a module other than "bignum"? Or is there something else > I need to do so that I end up with the correct numbers printed out? > Or am I just doomed by the lack of precision in my machines? Transforming your Perl quesion into an MS-SQL question, can you do something like select cast(content_id as varchar(19)) as content_id from ...
From: Ben Morrow on 9 Feb 2010 10:26
Quoth hymie(a)lactose.homelinux.net (hymie!): > Greetings. I'm not sure exactly where my problem is, and I'm looking > for a little help. I hope my need to anonymize the info will not > impact your ability to guide me. > > I have an MSSQL database. I connect to it with SQL Server, run a > query "select content_id from log" with a "where" clause I'm not > allowed to broadcast, and I get these results: > > 1037479785592177191 > 1037222160396202204 > 1036993281177442875 > 1037489555080390716 > 1037253823299245752 > > I probably need to note that "content_id" is defined in the database > as a "bigint not null". > > I run a perl script with the same query: > > my $query = "select content_id from log "; > my $st = $ardbh->prepare($query); > $st->execute(@ARGV); > while (my @row = $st->fetchrow_array) > { > print "$row[0]\n"; > } > > I get this result: > 1.03747978559218e+18 You need to read the DBI docs (and possibly the docs of whichever DBD you are using: Sybase?) to find out how to get DBI to return the data in a different format. As cartercc said, you probably want to retrieve the data as strings rather than numbers. Ben |