Re: DBD::Oracle sometimes return wrong PRECISION

"H.Merijn Brand" <[email protected]>
Newsgroups gmane.comp.lang.perl.modules.dbi.sybase.devel
Message-ID <[email protected]> 
On Wed, 31 Dec 2014 18:49:24 +0100, "H.Merijn Brand"
<[email protected]> wrote:

> On Wed, 31 Dec 2014 16:58:02 +0100, "H.Merijn Brand"
> <[email protected]> wrote:
> 
> > I have something weird in DBD::Oracle (1.44 and 1.74)
> > Client = 10.2.0.1.0 / AIX-5.3 - Server = 11.2.0.3.0 - 64bit / Linux
> > 
> > consider a field that looks like this:
> > 
> > {   LINK             => undef,
> >     NAME             => 'D_GELD',
> >     NAME_lc          => 'd_geld',
> >     NAME_uc          => 'D_GELD',
> >     NULLABLE         => '',
> >     PRECISION        => 8,
> >     SCALE            => 0,
> >     TYPE             => 3,
> >     TYPE_NAME        => 'DECIMAL',
> >     dbd_type         => undef,
> >     ora_est_row_width => undef,
> >     ora_fail_over    => undef,
> >     ora_lengths      => 172,
> >     ora_rowid        => undef,
> >     ora_type         => undef,
> >     ora_types        => 2
> >     }
> > 
> > *sometimes* I get 126 in the PRECISION entry instead of 8
> > does that ring any bells?
> > 
> > I get 126 only if I use a where clause that addresses a field that is
> > first in an index on that table otherwise I get 8
> 
> I can reproduce it with this:
> 
> --8<--- test.pl
> use 5.16.2;
> use warnings;
> 
> use DBI;
> 
> my $dbh = DBI->connect ("dbi:Oracle:", $ENV{DBI_USER}, $ENV{DBI_PASS}, {
>     RaiseError         => 1,
>     PrintError         => 1,
>     AutoCommit         => 1,
>     ChopBlanks         => 1,
>     ShowErrorStatement => 1,
>     FetchHashKeyName   => "NAME_lc",
>     });
> 
> say "DBI: $DBI::VERSION";
> say "DBD: $DBD::Oracle::VERSION";
> say "OCI: ", ORA_OCI;
> say "Svr: @{$dbh->func ('ora_server_version')}";
> 
> $dbh->do (qq;
>     create table foo (
> 	a1       number (3) default -1,
> 	a2       number (4) default -1,
> 	a3       number (3) default -1,
> 	d_foo    number (8) default -1,
> 	v_foo    number (2) default  0,
> 	hist     char   (1) default 'A'
> 	););
> 
> sub prec
> {
>     my $sth = $dbh->prepare (shift);
>     $sth->execute;
>     printf "PRECISION: %3d, St: %s\n", $sth->{PRECISION}[3], $sth->{Statement};
>     } # prec
> 
> sub test
> {
>     prec ("select a1, a2, a3, d_foo, v_foo from foo");
>     prec ("select a1, a2, a3, d_foo, v_foo from foo where a1 = 111");
>     prec ("select a1, a2, a3, d_foo, v_foo from foo where a2 = 111");
>     prec ("select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111");
>     } # test
> 
> test ();
> 
> $dbh->do ("insert into foo values (101, 101, 25, 19841213, 0, 'A')");
> 
> test ();
> 
> $dbh->do ("create index idx_foo on foo (a1, a2, a3, d_foo desc, v_foo desc, hist)");
> 
> test ();
> 
> END { $dbh->do ("drop table foo"); }
> -->8---
> 
> $ perl test.pl
> DBI: 1.632
> DBD: 1.74
> OCI: 11.2.0.3
> Svr: 11 2 0 3 0
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
> PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo
> PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
> PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
> PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
> 
> 
> SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 31 18:42:36 2014
> This is perl 5, version 20, subversion 0 (v5.20.0) built for x86_64-linux-thread-multi-ld

SQLite (all undef):
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   -, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111

MySQL:
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111

Unify:
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111

CSV: (use integer instead of numeric)
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
PRECISION:   8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
Can't find column definitions! at /pro/lib/perl5/site_perl/5.20.0/SQL/Statement.pm line 88.


-- 
H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
using perl5.00307 .. 5.21   porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/        http://www.test-smoke.org/
http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/
signature.asc (application/pgp-signature, 490 B) 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (GNU/Linux)

iQEcBAEBAgAGBQJUpWHFAAoJEAOhR6E+XcCYdeoH/RTA9eBqj0ehWotT1l0KfpJt
F7xZlK0D6isvq1DGn/FrLYTI4Rc3ftiBSSDDfXOLhZkbwcQhATuMHyed08K19sge
+X7yxMPFmVtgpphVAfGe71lG6p8w4jgCQFePSg5nlm0YV6n6Vp/LyOOCTfzCDDun
C79emoipEtvi9KS7BNaKG8ZPQ49roGRik00O1o/hZd1yrzdnoh6/RVWWGknxBq1O
1f3C6tbgeUjGXGmpAMEbJaWvAZCCNe2no68+tcvsnGk/10m32vSp4rWi/T2f/Jrm
mlTxpZtnslNN2Pdxe2arEtB28m+ZXfGXSLl199busn0dn5DKEt+DJGqgbtvFrxI=
=zG8o
-----END PGP SIGNATURE-----
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.