On Wed, 31 Dec 2014 18:49:24 +0100, "H.Merijn Brand" <h.m.br...@xs4all.nl> wrote:
> On Wed, 31 Dec 2014 16:58:02 +0100, "H.Merijn Brand"
> <h.m.br...@xs4all.nl> 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/
pgpMMancqGIYR.pgp
Description: OpenPGP digital signature
