On Fri, Sep 12, 2008 at 9:36 AM, Raja Vadlamudi <[EMAIL PROTECTED]> wrote:
>
>
> 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.
>
This one is working.
Thanks
Regards,
Amit Saxena
