DBI and fetchhasref

Discussion in 'Perl Misc' started by Peter.Kramer, Jul 12, 2005.

  1. Peter.Kramer

    Peter.Kramer Guest

    hi there
    I'm wondering why I cant get all resulst in my hasref in this statement:


    --
    select r.resourcesize, a.samaccountname

    from aduser a
    (
    select * from resourcesize
    where ts = (select max(ts) from resourcesize where server = 'xyz')
    ) r

    where a.samaccountname = r.samaccountname(+)
    ----

    What I get is an hasref and all data from table "a" and all fields of table
    "r" but no data of table "r".
    I assume, that the sub select is the problem.

    result:
    $hash->{SAMACCOUNTNAME} = "tiger"
    $hash->{RESOURCESIZE} = ""

    When I execute the script in SQL Plus all Data is availible!

    $hash->{SAMACCOUNTNAME} = "tiger"
    $hash->{RESOURCESIZE} = "1234"

    I'm using Active State Perl 5.8.4 with DBD-Oracle and Oracle 9i
     
    Peter.Kramer, Jul 12, 2005
    #1
    1. Advertising

  2. Peter.Kramer

    Paul Lalli Guest

    Peter.Kramer wrote:
    > I'm wondering why I cant get all resulst in my hasref in this statement:


    "Hashref". Not "hasref".

    > --
    > select r.resourcesize, a.samaccountname
    >
    > from aduser a
    > (
    > select * from resourcesize
    > where ts = (select max(ts) from resourcesize where server = 'xyz')
    > ) r
    >
    > where a.samaccountname = r.samaccountname(+)
    > ----
    >
    > What I get is an hasref and all data from table "a" and all fields of table
    > "r" but no data of table "r".
    > I assume, that the sub select is the problem.
    >
    > result:
    > $hash->{SAMACCOUNTNAME} = "tiger"
    > $hash->{RESOURCESIZE} = ""
    >
    > When I execute the script in SQL Plus all Data is availible!
    >
    > $hash->{SAMACCOUNTNAME} = "tiger"
    > $hash->{RESOURCESIZE} = "1234"
    >
    > I'm using Active State Perl 5.8.4 with DBD-Oracle and Oracle 9i


    >From the lack of code posted, I gather you believe there is something

    wrong with Perl and/or DBD-Oracle, rather than something wrong with
    your code? That's mighty arrogant of you. You'll forgive me if I tend
    not to believe you. Please post a short-but-complete script that
    demonstrates the problem you're having. Have you seen the posting
    guidelines for this newsgroup? Please read them, and follow at least 2
    particulars:
    Speak Perl, not English. (Show us, in Perl, what your variable
    contains, don't try to tell us what you think it contains).
    Post the aforementioned short-but-complete script.

    Paul Lalli
     
    Paul Lalli, Jul 12, 2005
    #2
    1. Advertising

  3. Peter.Kramer

    Peter.Kramer Guest

    "Paul Lalli" <> schrieb im Newsbeitrag
    news:...
    > Peter.Kramer wrote:
    >> I'm wondering why I cant get all resulst in my hasref in this statement:

    >
    > "Hashref". Not "hasref".
    >
    >> --
    >> select r.resourcesize, a.samaccountname
    >>
    >> from aduser a
    >> (
    >> select * from resourcesize
    >> where ts = (select max(ts) from resourcesize where server = 'xyz')
    >> ) r
    >>
    >> where a.samaccountname = r.samaccountname(+)
    >> ----
    >>
    >> What I get is an hasref and all data from table "a" and all fields of
    >> table
    >> "r" but no data of table "r".
    >> I assume, that the sub select is the problem.
    >>
    >> result:
    >> $hash->{SAMACCOUNTNAME} = "tiger"
    >> $hash->{RESOURCESIZE} = ""
    >>
    >> When I execute the script in SQL Plus all Data is availible!
    >>
    >> $hash->{SAMACCOUNTNAME} = "tiger"
    >> $hash->{RESOURCESIZE} = "1234"
    >>
    >> I'm using Active State Perl 5.8.4 with DBD-Oracle and Oracle 9i

    >
    >>From the lack of code posted, I gather you believe there is something

    > wrong with Perl and/or DBD-Oracle, rather than something wrong with
    > your code? That's mighty arrogant of you. You'll forgive me if I tend
    > not to believe you. Please post a short-but-complete script that
    > demonstrates the problem you're having. Have you seen the posting
    > guidelines for this newsgroup? Please read them, and follow at least 2
    > particulars:
    > Speak Perl, not English. (Show us, in Perl, what your variable
    > contains, don't try to tell us what you think it contains).
    > Post the aforementioned short-but-complete script.
    >
    > Paul Lalli
    >


    Here the complete Code.
    But as I said, when I copy the sql statement to sql+ i see all expected
    data. Its not the first time that I use DBI, but the first time I use a sub
    select.
    I assume a problem with perl because of the sql+ experience

    schnipp:
    ---
    use DBI;
    my $sizelimit =0;
    my $sizelimitmail =0;


    my $dbh=DBI->connect("DBI:Oracle:$database","user","pass") or die "no
    connect\n";

    my $sql ="
    select
    resourcesize R,a.samaccountname USR,displayname,
    department,m.sizemb Mailboxsize, floor((resourcesize)/(1024)) rsize,
    a.EXTENSIONATTRIBUTE9 Kostenstelle

    from btrsammdba.aduser a, btrsammdba.mailboxsize m,
    (
    select * from btrsammdba.resourcesize
    where ts = (select max(ts) from btrsammdba.resourcesize where server =
    'BMAIZ-FILEN02')
    ) r

    where a.samaccountname = r.samaccountname(+)
    and a.samaccountname = m.samaccountname(+)

    and a.EXTENSIONATTRIBUTE9 is not null
    and a.EXTENSIONATTRIBUTE3 is null
    and m.sizemb > '$sizelimitmail'

    order by a.EXTENSIONATTRIBUTE9,resourcesize desc


    ";


    print "\n$query\n";


    print "creating query.... ";
    my $sth = $dbh->prepare($sql, $attr) or return undef;
    $sth->execute;
    print "...created\n";

    print $DBI::errstr."\n";
    while( my $href= $sth->fetchrow_hashref)
    {

    foreach my $key (keys %$href)
    {
    print "$key -> $href->{$key}\n"; # here is no value in rsize
    }
    }
     
    Peter.Kramer, Jul 13, 2005
    #3
  4. Peter.Kramer

    Bob Smith Guest

    Peter.Kramer wrote:

    >
    > "Paul Lalli" <> schrieb im Newsbeitrag
    > news:...
    >> Peter.Kramer wrote:
    >>> I'm wondering why I cant get all resulst in my hasref in this statement:

    >>
    >> "Hashref". Not "hasref".
    >>
    >>> --
    >>> select r.resourcesize, a.samaccountname
    >>>
    >>> from aduser a
    >>> (
    >>> select * from resourcesize
    >>> where ts = (select max(ts) from resourcesize where server = 'xyz')
    >>> ) r
    >>>
    >>> where a.samaccountname = r.samaccountname(+)
    >>> ----
    >>>
    >>> What I get is an hasref and all data from table "a" and all fields of
    >>> table
    >>> "r" but no data of table "r".
    >>> I assume, that the sub select is the problem.
    >>>
    >>> result:
    >>> $hash->{SAMACCOUNTNAME} = "tiger"
    >>> $hash->{RESOURCESIZE} = ""
    >>>
    >>> When I execute the script in SQL Plus all Data is availible!
    >>>
    >>> $hash->{SAMACCOUNTNAME} = "tiger"
    >>> $hash->{RESOURCESIZE} = "1234"
    >>>
    >>> I'm using Active State Perl 5.8.4 with DBD-Oracle and Oracle 9i

    >>
    >>>From the lack of code posted, I gather you believe there is something

    >> wrong with Perl and/or DBD-Oracle, rather than something wrong with
    >> your code? That's mighty arrogant of you. You'll forgive me if I tend
    >> not to believe you. Please post a short-but-complete script that
    >> demonstrates the problem you're having. Have you seen the posting
    >> guidelines for this newsgroup? Please read them, and follow at least 2
    >> particulars:
    >> Speak Perl, not English. (Show us, in Perl, what your variable
    >> contains, don't try to tell us what you think it contains).
    >> Post the aforementioned short-but-complete script.
    >>
    >> Paul Lalli
    >>

    >
    > Here the complete Code.
    > But as I said, when I copy the sql statement to sql+ i see all expected
    > data. Its not the first time that I use DBI, but the first time I use a
    > sub select.
    > I assume a problem with perl because of the sql+ experience
    >
    > schnipp:
    > ---
    > use DBI;
    > my $sizelimit =0;
    > my $sizelimitmail =0;
    >
    >
    > my $dbh=DBI->connect("DBI:Oracle:$database","user","pass") or die "no
    > connect\n";
    >
    > my $sql ="
    > select
    > resourcesize R,a.samaccountname USR,displayname,
    > department,m.sizemb Mailboxsize, floor((resourcesize)/(1024)) rsize,
    > a.EXTENSIONATTRIBUTE9 Kostenstelle
    >
    > from btrsammdba.aduser a, btrsammdba.mailboxsize m,
    > (
    > select * from btrsammdba.resourcesize
    > where ts = (select max(ts) from btrsammdba.resourcesize where server =
    > 'BMAIZ-FILEN02')
    > ) r
    >
    > where a.samaccountname = r.samaccountname(+)
    > and a.samaccountname = m.samaccountname(+)
    >
    > and a.EXTENSIONATTRIBUTE9 is not null
    > and a.EXTENSIONATTRIBUTE3 is null
    > and m.sizemb > '$sizelimitmail'
    >
    > order by a.EXTENSIONATTRIBUTE9,resourcesize desc
    >
    >
    > ";
    >
    >
    > print "\n$query\n";
    >
    >
    > print "creating query.... ";
    > my $sth = $dbh->prepare($sql, $attr) or return undef;
    > $sth->execute;
    > print "...created\n";
    >
    > print $DBI::errstr."\n";
    > while( my $href= $sth->fetchrow_hashref)
    > {
    >
    > foreach my $key (keys %$href)
    > {
    > print "$key -> $href->{$key}\n"; # here is no value in rsize
    > }
    > }

    hmm, I don't know, are you sure that the field you call resourcesize is
    populated?

    what about the floor function, what fieldtype is resourcesize, ?
    --
    http://www.kolumbus.fi/bob.smith
     
    Bob Smith, Jul 14, 2005
    #4
  5. Peter.Kramer

    Peter.Kramer Guest

    "Bob Smith" <> schrieb im Newsbeitrag
    news:db5b0h$aol$...
    > Peter.Kramer wrote:
    >
    >>
    >> "Paul Lalli" <> schrieb im Newsbeitrag
    >> news:...
    >>> Peter.Kramer wrote:
    >>>> I'm wondering why I cant get all resulst in my hasref in this
    >>>> statement:
    >>>
    >>> "Hashref". Not "hasref".
    >>>
    >>>> --
    >>>> select r.resourcesize, a.samaccountname
    >>>>
    >>>> from aduser a
    >>>> (
    >>>> select * from resourcesize
    >>>> where ts = (select max(ts) from resourcesize where server = 'xyz')
    >>>> ) r
    >>>>
    >>>> where a.samaccountname = r.samaccountname(+)
    >>>> ----
    >>>>
    >>>> What I get is an hasref and all data from table "a" and all fields of
    >>>> table
    >>>> "r" but no data of table "r".
    >>>> I assume, that the sub select is the problem.
    >>>>
    >>>> result:
    >>>> $hash->{SAMACCOUNTNAME} = "tiger"
    >>>> $hash->{RESOURCESIZE} = ""
    >>>>
    >>>> When I execute the script in SQL Plus all Data is availible!
    >>>>
    >>>> $hash->{SAMACCOUNTNAME} = "tiger"
    >>>> $hash->{RESOURCESIZE} = "1234"
    >>>>
    >>>> I'm using Active State Perl 5.8.4 with DBD-Oracle and Oracle 9i
    >>>
    >>>>From the lack of code posted, I gather you believe there is something
    >>> wrong with Perl and/or DBD-Oracle, rather than something wrong with
    >>> your code? That's mighty arrogant of you. You'll forgive me if I tend
    >>> not to believe you. Please post a short-but-complete script that
    >>> demonstrates the problem you're having. Have you seen the posting
    >>> guidelines for this newsgroup? Please read them, and follow at least 2
    >>> particulars:
    >>> Speak Perl, not English. (Show us, in Perl, what your variable
    >>> contains, don't try to tell us what you think it contains).
    >>> Post the aforementioned short-but-complete script.
    >>>
    >>> Paul Lalli
    >>>

    >>
    >> Here the complete Code.
    >> But as I said, when I copy the sql statement to sql+ i see all expected
    >> data. Its not the first time that I use DBI, but the first time I use a
    >> sub select.
    >> I assume a problem with perl because of the sql+ experience
    >>
    >> schnipp:
    >> ---
    >> use DBI;
    >> my $sizelimit =0;
    >> my $sizelimitmail =0;
    >>
    >>
    >> my $dbh=DBI->connect("DBI:Oracle:$database","user","pass") or die "no
    >> connect\n";
    >>
    >> my $sql ="
    >> select
    >> resourcesize R,a.samaccountname USR,displayname,
    >> department,m.sizemb Mailboxsize, floor((resourcesize)/(1024)) rsize,
    >> a.EXTENSIONATTRIBUTE9 Kostenstelle
    >>
    >> from btrsammdba.aduser a, btrsammdba.mailboxsize m,
    >> (
    >> select * from btrsammdba.resourcesize
    >> where ts = (select max(ts) from btrsammdba.resourcesize where server
    >> =
    >> 'BMAIZ-FILEN02')
    >> ) r
    >>
    >> where a.samaccountname = r.samaccountname(+)
    >> and a.samaccountname = m.samaccountname(+)
    >>
    >> and a.EXTENSIONATTRIBUTE9 is not null
    >> and a.EXTENSIONATTRIBUTE3 is null
    >> and m.sizemb > '$sizelimitmail'
    >>
    >> order by a.EXTENSIONATTRIBUTE9,resourcesize desc
    >>
    >>
    >> ";
    >>
    >>
    >> print "\n$query\n";
    >>
    >>
    >> print "creating query.... ";
    >> my $sth = $dbh->prepare($sql, $attr) or return undef;
    >> $sth->execute;
    >> print "...created\n";
    >>
    >> print $DBI::errstr."\n";
    >> while( my $href= $sth->fetchrow_hashref)
    >> {
    >>
    >> foreach my $key (keys %$href)
    >> {
    >> print "$key -> $href->{$key}\n"; # here is no value in rsize
    >> }
    >> }

    > hmm, I don't know, are you sure that the field you call resourcesize is
    > populated?
    >
    > what about the floor function, what fieldtype is resourcesize, ?
    > --
    > http://www.kolumbus.fi/bob.smith


    Yes I'm sure, when I use a sql statemnet that has no sub select all adta is
    presented well.
    the floor function works proper as well, the data type is NUMBER(38,0)
     
    Peter.Kramer, Jul 15, 2005
    #5
  6. Peter.Kramer <> wrote:


    [snip 120 lines of full-quote including .sig]

    [snip 3 lines of new text]


    Please learn how to compose a proper followup.


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Jul 15, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    531
    Juha Laiho
    Jul 22, 2004
  2. Aredridel
    Replies:
    9
    Views:
    174
    Larry Felton Johnson
    Feb 27, 2004
  3. Jerome Hauss
    Replies:
    0
    Views:
    175
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    178
  5. Tim Haynes
    Replies:
    3
    Views:
    146
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page