Re: anyway to determine # rows before fetch loop ends and without seperate count(*)
Let's close this thread now. Thanks. Tim.
Re: anyway to determine # rows before fetch loop ends and without seperate count(*)
> 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 Whatever is fueling the need for you to blow things out of such proportion? I suggest that you refrain from trying to insult my or any other person's intelligence who come to this mailing list seeking help. You know as well as I do that the original script that I sent was executing a push inside a while fetch loop, which is just another way to do it ---> big deal. Is that so complicated??? No. If things run efficiently and correctly either way does it really matter? Did you include the number of lines in selectall_arrayref() sub too? -- Who cares? My post came about because of a fundamental misunderstanding of what is actually going on. The communication of what I believed to be true does not change what is actually happening, although it seems to do that for you. Or is it that you spend to much time wanting to negate someone's statements? I communicated things incorrectly and you sit here and speculate these things and make these comments and honestly I am only left to wonder who really finds you as beneficial as you think that you are. I know ~I'm~ still learning. I go back and rewrite my subs all the time as my knowledge progresses. I'm not really sure who'd want you to maintain their code, but rest assured you wont ever have to worry about maintaining mine.
Re: anyway to determine # rows before fetch loop ends and without seperate 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 :)
RE: anyway to determine # rows before fetch loop ends and without seperate count(*)
Read standard books such as _Programming Perl_ by Wall etc. for information
on array references.
Generally references are the most efficient way to pass arrays around or
return them from a function. Returning an array instead of a reference to
an array means the whole array has to be copied. Array references avoid
that. An array reference is not a two-dimensional array.
selectall_arrayref() returns an array reference.
Maybe you still need to work on this issue, but it's off topic for this
list.
-Will
-Original Message-
From: listmail [mailto:[EMAIL PROTECTED]
Sent: Friday 18 November 2005 09:59
To: dbi-users@perl.org
Subject: Re: anyway to determine # rows before fetch loop ends and without
seperate 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.
- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is
solely intended for the named addressee (or a person responsible for delivering
it to the addressee). If you are not the intended recipient of this message,
you are not authorized to read, print, retain, copy or disseminate this message
or any part of it. If you have received this e-mail in error, please notify the
sender immediately by return e-mail and delete it from your computer.
RE: anyway to determine # rows before fetch loop ends and without seperate count(*)
listmail [mailto:[EMAIL PROTECTED] 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 without seperate 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(*)
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(*)
> [EMAIL PROTECTED] 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 without seperate count(*)
It doesn't work according to the DBI documentation. >From _Programming the Perl DBI_, p. 223 (for example): >For SELECT statements, execute simply "starts" the query within the database engine. Use one of >the fetch methods to retrieve the data after calling execute. The execute method does _not_ return >the number of rows that will be returned by the query (because most databases can't tell in advance), >it simply returns a true value. If you are relying on that 'feature' you are asking for trouble. There have been discussions over this issue on the list before. -Will -Original Message- From: Rob Craig [mailto:[EMAIL PROTECTED] Sent: Wednesday 16 November 2005 17:19 To: [EMAIL PROTECTED] Cc: dbi-users@perl.org Subject: Re: anyway to determine # rows before fetch loop ends and without seperate count(*) I am using DBD::mysql and I can get the number of rows by assigning the result of the execute to a scalar: my $rows = $sth->execute or die "Couldn't execute statement: " . DBI->errstr; maybe this doesn't work with DBD::Oracle? Rob - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer.
Re: anyway to determine # rows before fetch loop ends and without seperate count(*)
On 11/16/05, Rob Craig <[EMAIL PROTECTED]> wrote:
> I am using DBD::mysql and I can get the number of rows by assigning the
> result of the execute to a scalar:
>
> my $rows = $sth->execute or die "Couldn't execute statement: " .
> DBI->errstr;
>
> maybe this doesn't work with DBD::Oracle?
>
> Rob
When you execute a SELECT statement in MySQL, it (by default)
immediately returns all of the rows back to you under the covers,
which is why it is able to give you a row count. Woe betide you if
your SELECT statement returns a million rows.
>
> [EMAIL PROTECTED] 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;
> 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");
> }
>
> 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
> #a boolean variable $matrix could be returned or a ref
> check done so that the data
> #processing code can act accordingly
> #$sth->rows only shows total rows after the while loop
> is processed
> #Can I accomplish this without a seperate count(*)
> statement?
> #
> #push @newary,[ @{$ary} ]; # if more than one row
> #or
> #push @newary, @{$ary} ; # single row
> }
>
> $sth->finish;
>
> $dbh->disconnect;
> #
> #ActivePerl 5.8.7 813
> #ppm
> #-DBD-Oracle 1.16
> #-DBI 1.48
>
>
>
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005
>
>
>
Re: anyway to determine # rows before fetch loop ends and without seperate count(*)
[EMAIL PROTECTED] 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
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...
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(*)
I am using DBD::mysql and I can get the number of rows by assigning the
result of the execute to a scalar:
my $rows = $sth->execute or die "Couldn't execute statement: " .
DBI->errstr;
maybe this doesn't work with DBD::Oracle?
Rob
[EMAIL PROTECTED] 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;
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");
}
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
#a boolean variable $matrix could be returned or a ref
check done so that the data
#processing code can act accordingly
#$sth->rows only shows total rows after the while loop
is processed
#Can I accomplish this without a seperate count(*)
statement?
#
#push @newary,[ @{$ary} ]; # if more than one row
#or
#push @newary, @{$ary} ; # single row
}
$sth->finish;
$dbh->disconnect;
#
#ActivePerl 5.8.7 813
#ppm
#-DBD-Oracle 1.16
#-DBI 1.48
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005
RE: anyway to determine # rows before fetch loop ends and without seperate count(*)
The short answer is ... No.
The long answer is ...
How could the DBI (or the SQL and/or PL/SQL engine for that matter) know
in advance of getting the data, how much would be retrieved? According
to the concepts manual, part II, chapter 13 for 10gR2:
In a single-user database, the user can modify data in the database
without concern for
other users modifying the same data at the same time. However, in a
multiuser
database, the statements within multiple simultaneous transactions can
update the
same data. Transactions executing at the same time need to produce
meaningful and
consistent results.
Given this, there is no way for the DBI to know how much data will be
retrieved by a SELECT statement.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 16, 2005 2:27 PM
To: dbi-users@perl.org
Subject: anyway to determine # rows before fetch loop ends and without
seperate count(*)
#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;
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");
}
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
#a boolean variable $matrix could be returned or a ref
check done so that the data
#processing code can act accordingly
#$sth->rows only shows total rows after the while loop
is processed
#Can I accomplish this without a seperate count(*)
statement?
#
#push @newary,[ @{$ary} ]; # if more than one row
#or
#push @newary, @{$ary} ; # single row
}
$sth->finish;
$dbh->disconnect;
#
#ActivePerl 5.8.7 813
#ppm
#-DBD-Oracle 1.16
#-DBI 1.48
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
