Re: anyway to determine # rows before fetch loop ends and withoutseperate count(*)

> listmail@triad.rr.com wrote:
>
> > #Here's an example which shows what I am trying to accomplish.
> If I
> > can determine the number of rows before pushing the data, this
> can
> > simply things for #me when processing the data throught my
> scripts.
> > #
> > use warnings;
> > use strict;
>
> Good good :)
>
> > use DBI;
> > use DBD::Oracle;
> >
> > my $sql=q{ select name, location
> > from mytable
> > };
> >
> > my $dbh;
> >
> > eval {
> > $dbh = DBI->connect("dbi:Oracle:MYDB",
> > 'dbuser', 'dbpass',
> > {
> > RaiseError => 1,
> > AutoCommit => 0,
> > ora_session_mode => 0
> > }
> > );
> > };
> >
> > if ( $@ ) {
> > outprint('end',"$DBI::errstr\n");
> > }
>
> Hmm, perhaps the oracle specific stuff needs it but why are you
> evaling
> that?
>
> my $dbh = DBI->connect(@DBI_CONNECT_ARGS) or outprint('end',
> $DBI::errstr); # assumign its die()ing or exit()ing
>
This was carried over from a larger script by accident, but in case you
are curious, I eval in order to capture the connect failure and output
that error info to a Tk text window. Its not neccessary or wanted to
die in my Tk app because it connects to multiple databases through
Radio button selection and doesn't run anything automatically on its
own. So if you dig get an error, go troubleshoot and reconnect...
Anyway nuff said about that....

>
> > my $sth=$dbh->prepare($sql) or die "Couldn't prepare statement:
> " . DBI-
> >
> >>errstr;
> >
> >
> > $sth->execute or die "Couldn't execute statement: " . DBI->errstr;
> >
> > my $ary;
> >
> > while ($ary = $sth->fetchrow_array()) {
> > #I need to determine number of rows as this will
> affect
> > whether a matrix is used or not
>
> Also very convoluted, all of that can be done with:
>
> my $results = $dbh->selectall_arrayref($sql); # if you only want
> to
> process a certain amount just LIMIT in your $sql...
>
I appreciate the response. I tested selectall_arrayref and as I
expected, irregardless of the number of rows returned, $results will
always point to a matrix. So from what I am seeing, $record->[0] as
you have written below would have to be written as $record[0]->[0]. At
this point I've come to conclusion that my requirements are causing
uneccessary complications. If it wasn't clear, previously I was
wanting the data from a sql statement with one row returned to be
stored into an array of columns, otherwise make it an array of columns
and rows.....

I'll simply go with a matrix always and be done with it.

> my $count = @{ $results };
>
> $dbh->disconnect;
>
> if($count < 1000) { # or whatever you wanted teh count for...
> for my $record(@{ $results }) {
> # now use the data:
> # $record->[0]
> # $record->[1]
> }
> }
>

Re: anyway to determine # rows before fetch loop ends and withoutseperate count(*)

>>Also very convoluted, all of that can be done with:
>>
>>my $results = $dbh->selectall_arrayref($sql); # if you only want
>>to
>>process a certain amount just LIMIT in your $sql...
>>
>
> I appreciate the response. I tested selectall_arrayref and as I
> expected, irregardless of the number of rows returned, $results will
> always point to a matrix. So from what I am seeing, $record->[0] as

Actualyy its an array reference and each element of the array is an
array refernce that is the dat areturned by the select.

> you have written below would have to be written as $record[0]->[0]. At

nope. $record is one element of the $results array in the for loop,look
again:

> this point I've come to conclusion that my requirements are causing
> uneccessary complications. If it wasn't clear, previously I was
> wanting the data from a sql statement with one row returned to be
> stored into an array of columns, otherwise make it an array of columns
> and rows.....

Sounds like you want selectall_arrayref() still... did you read it
documentation?


my $results = $dbh->selectall_arrayref("SELECT id, foo, bar FROM baz
WHERE $where");

my $count = @{ $results }; # the number of elements in $results (IE the
number of rows returned)

for my $record (@{ $results }) { # go through each $record in your $results
print "Id $record->[0] has a foo of $record->[1]\n";
print "Id $record->[0] has a bar of $record->[2]\n";
}

> I'll simply go with a matrix always and be done with it.

There's no "matrix" :) you're making it too complex on yourself :)

You have an array ref that you can get the number of rows from *and*
each record from as an array ref itself, its not nearly as complicated
or obscure as a "matrix".

>>my $count = @{ $results };
>>
>>$dbh->disconnect;
>>
>>if($count < 1000) { # or whatever you wanted teh count for...
>> for my $record(@{ $results }) {
>> # now use the data:
>> # $record->[0]
>> # $record->[1]
>> }
>>}
>>
>
>
>
>
>

Re: anyway to determine # rows before fetch loop ends and withoutseperate count(*)

Well I'm not seeing why a number of arrays that each point to arrays
could not be consider a matrix of arrays when considering one definition
of the word matrix "Something resembling such an array, as in the
regular formation of elements into columns and rows". I dunno, i'm not
trying to argue with you of course. It is apparent that I truly am
confused with Perl References again. I beleive my main mistake could be
using "foreach my $record (@{ $results })" instead of what you've shown
"for my $record (@{ $results })". I'll test this later when I get a
chance and also see how I can include the use of bind variables while
using this method as well.

I appreciate your help an explanations.

JupiterHost.Net wrote:

>>> Also very convoluted, all of that can be done with:
>>>
>>> my $results = $dbh->selectall_arrayref($sql); # if you only want to
>>> process a certain amount just LIMIT in your $sql...
>>>
>>
>> I appreciate the response. I tested selectall_arrayref and as I
>> expected, irregardless of the number of rows returned, $results will
>> always point to a matrix. So from what I am seeing, $record->[0] as
>
>
> Actualyy its an array reference and each element of the array is an
> array refernce that is the dat areturned by the select.
>
>> you have written below would have to be written as $record[0]->[0]. At
>
>
> nope. $record is one element of the $results array in the for
> loop,look again:
>
>> this point I've come to conclusion that my requirements are causing
>> uneccessary complications. If it wasn't clear, previously I was
>> wanting the data from a sql statement with one row returned to be
>> stored into an array of columns, otherwise make it an array of
>> columns and rows.....
>
>
> Sounds like you want selectall_arrayref() still... did you read it
> documentation?
>
>
> my $results = $dbh->selectall_arrayref("SELECT id, foo, bar FROM baz
> WHERE $where");
>
> my $count = @{ $results }; # the number of elements in $results (IE
> the number of rows returned)
>
> for my $record (@{ $results }) { # go through each $record in your
> $results
> print "Id $record->[0] has a foo of $record->[1]\n";
> print "Id $record->[0] has a bar of $record->[2]\n";
> }
>
>> I'll simply go with a matrix always and be done with it.
>
>
> There's no "matrix" :) you're making it too complex on yourself :)
>
> You have an array ref that you can get the number of rows from *and*
> each record from as an array ref itself, its not nearly as complicated
> or obscure as a "matrix".
>
>>> my $count = @{ $results };
>>>
>>> $dbh->disconnect;
>>>
>>> if($count < 1000) { # or whatever you wanted teh count for...
>>> for my $record(@{ $results }) {
>>> # now use the data:
>>> # $record->[0]
>>> # $record->[1]
>>> }
>>> }
>>>
>>
>>
>>
>>
>>
>
>
>

RE: anyway to determine # rows before fetch loop ends and without seperate count(*)

listmail [mailto:listmail@triad.rr.com] wrote:
>
> Well I'm not seeing why a number of arrays that each point to arrays
> could not be consider a matrix of arrays when considering one definition
> of the word matrix "Something resembling such an array, as in the
> regular formation of elements into columns and rows". I dunno, i'm not
> trying to argue with you of course. It is apparent that I truly am
> confused with Perl References again. I beleive my main mistake could be
> using "foreach my $record (@{ $results })" instead of what you've shown
> "for my $record (@{ $results })". I'll test this later when I get a
> chance and also see how I can include the use of bind variables while
> using this method as well.
>

You can certainly call it a matrix if you want to, but other people may not
know what you mean. (I didn't, when I read your original post.) The
standard Perl terminology is to call it an array of arrays.

foreach and for in Perl are completely interchangeable.

These two are the same:
foreach my $x (@list)
for my $x (@list)

These two are also the same:
for (my $i = 0; $i < 10; ++$i) {
foreach (my $i = 0; $i < 10; ++$i) {

Which you use is merely style/personal preference.


Ronald

Re: anyway to determine # rows before fetch loop ends and withoutseperate count(*)

listmail wrote:
> Well I'm not seeing why a number of arrays that each point to arrays
> could not be consider a matrix of arrays when considering one definition
> of the word matrix "Something resembling such an array, as in the
> regular formation of elements into columns and rows". I dunno, i'm not


Because the data type of "$results" is an array reference.

Each item in it is also an array refenerence, so instead of confusing
and overwhelming your self with complex and ominous sounding "matrix"

Just think:

Ok, $results contains all of my $records, each $record has all of the
columns I SELECTed.

very simple and intuitive and doesn't sounds like you have to be
einstein or neo to understand and manipulate it.

> trying to argue with you of course. It is apparent that I truly am
> confused with Perl References again. I beleive my main mistake could be

because you're making it too hard on yourself thinking in such abstract
apocolyptic terms such as "matrix" which relate to computing theory in
general instead of a well defined paradigm and implimentation of a
specific component of the given language.

Sure *technically* and array ref that contains other array refs can be
considered a matrix but "an array ref of array refs" not only tells you
what it is but *exactly* what each part of it is which in turn instantly
tells you how it needs to be accessed which ultimately makes the 50 or
60 line script you sent originally about 10 lines or less. (IE by using
the array of array setup instead of some convoluted matrix that is
structured god know how.

That means in six months when you (or heaven forbid me) have to maintain
your code we don't have to figure what you were smoking to get what you
were after and what you personally define as a matrix and how you'd
structure and access the data in said matrix, etc etc

> using "foreach my $record (@{ $results })" instead of what you've shown
> "for my $record (@{ $results })". I'll test this later when I get a

for and foreach are the same thing, foreach just takes up 4 more
characters so I always use for(), its cleaner IMHO but do what you like :)