CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Fetching one row at a time

 



sha37
Novice

Mar 3, 2010, 9:30 AM

Post #1 of 5 (4817 views)
Fetching one row at a time Can't Post

Hi,

I have a below subroutine called getqueryresults()


Code
   

The below function just takes the data from ABC table and returns one row.

This function exists in db.pl file

sub getqueryresults()
{
my $followerName = $_[0];
my $sql = "SELECT * ABC WHERE (delay is null or delay < sysdate) AND id = ? ORDER BY qcounter, priority";

my $sth = $dbh->prepare($sql);
$sth->bind_param(1, $followerName);
eval
{
$sth->execute;
};

if ($@)
{
$log->error("dbh->errstr());
}

if ($sth->rows == -1)
{
$log->error("No rows.");
}
my @data= $sth->fetchrow_array();
return @data;
}



this below file XX.pl file calls getqueryresults();


Code
  

#!/usr/bin/perl

require 'db.pl';

$signal = 1;

while ($signal)

{

$signal = &processCommand();

}

sub processCommand()
{
my ($Id, $sId, $rd, $cmd) = getqueryresults("XX");

print $Id . "sd==>" . $sId . "rd===>" . $rd ."\n";
return $signal = 1;
}



This above print always prints the same row everytime. is there something i am doing wrong. I want to print a next row everytime.

It will be great if someone can take a loot at it.


FishMonger
Veteran / Moderator

Mar 3, 2010, 10:01 AM

Post #2 of 5 (4813 views)
Re: [sha37] Fetching one row at a time [In reply to] Can't Post

Sorry to say, but that's a very convoluted and inefficient approach.

Start by adding this to both scripts.

Code
use strict; 
use warnings;


Those pragmas should be included in every Perl script you write, and will point out lots of coding mistakes.

Remove the empty prototype definitions. Prototypes should not be used unless you know what they're used for and when they should be used.

Don't use '&' when calling a sub unless you know, understand and want its side effects.

The getqueryresults() sub should either return the complete dataset to the caller in a single call so that the caller can process it, or it should loop over and process the data itself.

If you really want to continue doing it this way, then you need to use the limit clause and keep track of and pass back and forth the record offset numbers.


(This post was edited by FishMonger on Mar 3, 2010, 10:01 AM)


sha37
Novice

Mar 3, 2010, 10:24 AM

Post #3 of 5 (4805 views)
Re: [FishMonger] Fetching one row at a time [In reply to] Can't Post

I do use strict and warnings. since i am mainly concerned about the question i posted.

You answer helps a lot.

Thanks

Sha


sha37
Novice

Mar 3, 2010, 10:47 AM

Post #4 of 5 (4800 views)
Re: [FishMonger] Fetching one row at a time [In reply to] Can't Post

Hi FishMonger,

Can you give me some sample code on using limit in my situation of the code?

this will really help me a lot.

Thanks

Sha


FishMonger
Veteran / Moderator

Mar 3, 2010, 11:43 AM

Post #5 of 5 (4795 views)
Re: [sha37] Fetching one row at a time [In reply to] Can't Post

Not all databases support the limit clause, but you can read up on its usage here:

http://dev.mysql.com/doc/refman/5.5/en/select.html

However, I'd highly recommend that you alter your approach. What you've got going here is really bad.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives