DBI and fetchhasref

P

Peter.Kramer

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
 
P

Paul Lalli

Peter.Kramer said:
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
 
P

Peter.Kramer

Paul Lalli said:
"Hashref". Not "hasref".


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
}
}
 
B

Bob Smith

Peter.Kramer said:
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, ?
 
P

Peter.Kramer

Bob Smith said:
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, ?

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)
 
T

Tad McClellan

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

[snip 3 lines of new text]


Please learn how to compose a proper followup.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top