On Fri, Sep 12, 2008 at 12:01 AM, Dr.Ruud
<[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> wrote:
> "Amit Saxena" schreef:
> > Dr.Ruud:
> >> jm:
>
> >>> there is a LIMIT option for the SELECT statement that will return
> >>> the number of records you desire.
> >>>
> >>> $sth = $dbh->prepare("select <whatever> from <table> <optional WHERE
> >>> arguments> LIMIT <desired number of records>");
> >>> $sth->execute();
> >>>
> >>> while ($vars ...) = $sth->fetchrow_array())
> >>> {
> >>> }
> >>> # or whatever syntax best suits your preferences
> >>
> >> That won't always work as you expect it. You need to give values for
> >> the ORDER BY and OFFSET and LIMIT, but between queries there can be
> >> new rows inserted or deleted, so you might get the same row again,
> >> or miss new ones.
> >> [snipped signature, one should never quote signatures]
> >
> > What about the scenario when the table is accessed in a read-only
> > mode ?
>
>
> Things get mixed up now. One technique is the one documented in the DBI
> documentation (as I quoted), the other technique is doing repeated
> queries with changing offset. Both have their uses.
>
> > Also I want to know whether the subsequent calls to fetchrow_array
> > will actually fetch the next LIMIT records or the current LIMIT one.
>
> The query that you prepared for a "fetchrow_arrayref() with a
> $max_records parameter" can have a SQL-LIMIT value, but that value is of
> course normally greater than the $max_records value.
> The $max_records value is the number of rows (or chunk size) that you
> want to work on at the same time.
> The SQL-LIMIT value is the maximum number of rows that the query returns
> (in total).
>
> --
> Affijn, Ruud
>
> "Gewoon is een tijger."
>
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> http://learn.perl.org/
>
>
>
I have made a sample program to test this using my old program :-
Old Program :-
# cat test1.pl
#!/usr/bin/perl
##!/u01/app/oracle/product/10.1.0/db_1/perl/bin/perl
# Example PERL DBI/DBD Oracle Example on Oracle 10g
use DBI;
my $dbname = ""; ## DB Name from tnsnames.ora
my $user = "";
my $passwd = "";
#### Connect to the database and return a database handle
$dbh = DBI->connect("dbi:Oracle:${dbname}", $user, $passwd);
if($dbh){
print("Connected as user $user\n");
} else {
print("Failed to connect!\n");
exit;
}
#### Prepare and Execute a SQL Statement Handle
my $sth = $dbh->prepare("SELECT * FROM employee");
$sth->execute();
# [
# [ 1, 2, 3 ],
# [ 4, 5, 6 ],
# [ 7, 8, 9 ]
# ]
$recordref = $sth->fetchall_arrayref();
foreach $recordrow (@{$recordref})
{
$col1 = ${$recordrow}[0];
$col2 = ${$recordrow}[1];
$col3 = ${$recordrow}[2];
$col4 = ${$recordrow}[3];
$col5 = ${$recordrow}[4];
$col6 = ${$recordrow}[5];
print "\n[$col1][$col2][$col3][$col4][$col5][$col6]";
}
print("Select Done!...");
#### Disconnect
if($dbh->disconnect){
print("Disconnected\n");
} else {
print("Failed to disconnect\n");
}
#
Output of Old Program :-
# perl test1.pl
Connected as user
[100][Wilson][Clrk][1700][][10]
[101][Smith][Slsm][2500][1300][40]
[103][Reed][Anlt][3500][][30]
[105][Watson][Mngr][4500][0][30]
[109][Allen][Mngr][3800][8000][40]
[110][Turner][Clrk][1800][][50]
[200][Chen][Mngr][2900][][10]
[210][Ramirez][Mngr][3650][][50]
[213][McDonnel][Clrk][1625][][60]
[214][Simpson][Drvr][825][][60]
[215][Di Salvo][Spvr][2700][][60]
[220][Schwartz][Slsm][4250][5300][40]Select Done!...Disconnected
New Program :-
# cat test3.pl
#!/usr/bin/perl
##!/u01/app/oracle/product/10.1.0/db_1/perl/bin/perl
# Example PERL DBI/DBD Oracle Example on Oracle 10g
use DBI;
my $dbname = ""; ## DB Name from tnsnames.ora
my $user = "";
my $passwd = "";
#### Connect to the database and return a database handle
$dbh = DBI->connect("dbi:Oracle:${dbname}", $user, $passwd);
if($dbh){
print("Connected as user $user\n");
} else {
print("Failed to connect!\n");
exit;
}
#### Prepare and Execute a SQL Statement Handle
my $sth = $dbh->prepare("SELECT * FROM employee");
$sth->execute();
# [
# [ 1, 2, 3 ],
# [ 4, 5, 6 ],
# [ 7, 8, 9 ]
# ]
my $max_rows = 4;
# $recordref = $sth->fetchall_arrayref();
# while ($recordref = $sth->fetchall_arrayref(undef, $max_rows))
#while($row = shift(@$recordref) ||
shift(@{$recordref=$sth->fetchall_arrayref(undef, $max_rows)})) { }
while (my $recordref = shift(@$rowcache) || shift ( @{$rowcache =
$sth->fetchall_arrayref(undef, $max_rows)} ))
{
foreach $recordrow (@{$recordref})
{
$col1 = ${$recordrow}[0];
$col2 = ${$recordrow}[1];
$col3 = ${$recordrow}[2];
$col4 = ${$recordrow}[3];
$col5 = ${$recordrow}[4];
$col6 = ${$recordrow}[5];
print "\n[$col1][$col2][$col3][$col4][$col5][$col6]";
}
print "\n----------------\n";
};
print("\nSelect Done!...\n");
#### Disconnect
if($dbh->disconnect){
print("Disconnected\n");
} else {
print("Failed to disconnect\n");
}
#
Output of New program :-
# perl test3.pl
Connected as
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
[][][][][][]
----------------
Select Done!...
Disconnected
[EMAIL PROTECTED] ~]#
I am still not clear about what's mentioned in the documentation and some of
the websites stated for this maximum row feature in Perl-DBI.
if it's possible, please someone state a small working example using this
feature using hashref or arrayref.
Thanks & Regards,
Amit Saxena
