 | |
[Info-Ingres] dbi and float format
comp.databases.ingres comp.databases.ingres
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
[Info-Ingres] dbi and float format -
12-21-2010
, 05:01 AM
#2
| |||
| |||
|
#3
| |||
| |||
|
#4
| |||
| |||
|
#5
| |||
| |||
|
|
Another quick update on DBI and formatting floats values. The -f[48]Nx.y options are recognised by DBI:Ingres, but any formatting specified seems to be lost when a float value is returned to a perl script - a solution (or at least a workaround) is to cast it to varchar and return a string instead. eg "select varchar(float8(10.1))" gives the formatted value whereas "select float8(10.1)" doesn't. To get both -f8 and -f4 accepted in a connection string in ESQL,the trick is to use: options='-f4Nx.y','-f8Nx,y' So both options are enclosed by quotes, and separated by a comma. (Thanks to Ingres support for pointing us in the right direction there). All that's left is to figure out how to specify both -f options in a connect string in a perl script... |
controls float -> string formatting in the DBMS as described above.
I.e.varchar cast on floats, e.g.:
select varchar(float4(0.5)) from iidbconstants
If you need control over formatting of floats in Perl, that is your
responsibility as the Perl programmer. Here is a web page that has a
concise table/example for printf: http://www.tutorialspoint.com/perl/perl_printf.htm
Here is a quick demo:
#!perl
use strict;
use warnings;
my $myf = 0.5;
print "$myf\n";
printf "%e\n", $myf;
printf "%E\n", $myf;
printf "%f\n", $myf;
printf "%79.38f\n", $myf;
##### End
And here is a quick demo of varchar type cast with the f4 param using
Ingres DBI (NOTE I personally recommend you consider using the ODBC
DBI driver if you are using Perl with Ingres, I'm not sure what
session connection flags are for -fx though).
use strict;
use warnings;
use DBI;
my $dbname = "iidbdb"; # database name, may include vnode
# Create a database handle.
#my $dbh = DBI->connect( "DBI:Ingres:$dbname")
my $dbh = DBI->connect( "DBI:Ingres:$dbname;-f4F79.38")
or die $DBI::errstr;
# Prepare a statment
my $sth = $dbh->prepare('select varchar(float4(0.5)) from
iidbconstants');
$sth -> execute();
# Get one line of the result as an array.
print join "|", $sth -> fetchrow_array();
print "\n";
# "finishing" the statement is not strictly necessary, but in this
# case we will get a warning if we don't.
$sth -> finish();
$dbh -> disconnect();
There are some perl pages in the wiki, please feel free to add to them
see http://community.ingres.com/wiki/Perl_DBI
Chris
#6
| |||
| |||
|
|
On Dec 21, 6:58*am, Ingres Forums <info- ing... (AT) kettleriverconsulting (DOT) com> wrote: Another quick update on DBI and formatting floats values. The -f[48]Nx.y options are recognised by DBI:Ingres, but any formatting specified seems to be lost when a float value is returned to a perl script - a solution (or at least a workaround) is to cast it to varchar and return a string instead. eg "select varchar(float8(10.1))" gives the formatted value whereas "select float8(10.1)" doesn't. To get both -f8 and -f4 accepted in a connection string in ESQL,the trick is to use: options='-f4Nx.y','-f8Nx,y' So both options are enclosed by quotes, and separated by a comma. (Thanks to Ingres support for pointing us in the right direction there). All that's left is to figure out how to specify both -f options in a connect string in a perl script... The -f flags are Ingres specific flags, in this situation this controls float -> string formatting in the DBMS as described above. I.e.varchar cast on floats, e.g.: * * select varchar(float4(0.5)) from iidbconstants If you need control over formatting of floats in Perl, that is your responsibility as the Perl programmer. Here is a web page that has a concise table/example for printf:http://www.tutorialspoint.com/perl/perl_printf.htm Here is a quick demo: #!perl use strict; use warnings; my $myf = 0.5; print "$myf\n"; printf "%e\n", $myf; printf "%E\n", $myf; printf "%f\n", $myf; printf "%79.38f\n", $myf; ##### End And here is a quick demo of varchar type cast with the f4 param using Ingres DBI (NOTE I personally recommend you consider using the ODBC DBI driver if you are using Perl with Ingres, I'm not sure what session connection flags are for -fx though). use strict; use warnings; use DBI; my $dbname = "iidbdb"; * # database name, may include vnode # Create a database handle. #my $dbh = DBI->connect( "DBI:Ingres:$dbname") my $dbh = DBI->connect( "DBI:Ingres:$dbname;-f4F79.38") or die $DBI::errstr; # Prepare a statment my $sth = $dbh->prepare('select varchar(float4(0.5)) from iidbconstants'); $sth -> execute(); # Get one line of the result as an array. print join "|", $sth -> fetchrow_array(); print "\n"; # "finishing" the statement is not strictly necessary, but in this # case we will get a warning if we don't. $sth -> finish(); $dbh -> disconnect(); There are some perl pages in the wiki, please feel free to add to them seehttp://community.ingres.com/wiki/Perl_DBI Chris |
string since Perl uses doubles internally hence any float4 or float8
would be coerced to float8 or double anyway, with of course no loss
in precision.
DBD::Ingres states that :
"Floats and doubles are returned as numeric values (NVs in perl-
speak)."
and an NV scalar is a double;more accuratelly it can hold a double.
So since you are going to consume the data from within a perl program
you will have to deal with doubles anyway and the formatting would be
up to you, as Chris points out. Can use pritnf for presentation or
sprintf for storing the value
as a string.
Always use Devel::Peek when in doubt :
use Devel::Peek;
$a=0.5;
Dump($a);
##output##
SV = NV(0x1835a7c) at 0x182a6cc
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 0.5
NV is the internal representation of a double
#7
| |||
| |||
|
#8
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.