On Thu, Sep 11, 2008 at 11:12 PM, Amit Saxena <[EMAIL PROTECTED]>wrote:
> On Fri, Sep 12, 2008 at 12:01 AM, Dr.Ruud
> <[EMAIL PROTECTED] <[EMAIL PROTECTED]><
> [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
>
Here is one simple example. It just works fine.
$do_src = $dbh_src->prepare($sql_retrieve);
$do_src->execute();
while (my $data = $do_src->fetchall_arrayref(undef, 4)) {
for (@$data) {
print $_->[0] . "\n";
}
print "Reached end of while loop\n";
}
Make sure you use 'use strict' to catch unknown problems.
