Re: [INTERFACES] Perl Interface - Mailing list pgsql-interfaces

robert_hiltibidal_at_cms08405@ccmailgw.state.il.us wrote:
> 
>      Afternoon,
> 
>      Something that's been driving me a little buggy maybe someone Out
>      There can shed some light...
> 
>      We use postgres for user management on linux systems. Nothing fancy
>      about the fields, just username,password,lastname,firstname etc.
> 
>      Traditionally I've used the shell psql function:
>      @exec = `psql -A -d users -t -q -c "Select * from temp"`;
> 
>      Weeellll now I'm running into server timeouts on the box. The
>      adminmstration box does see a fair amount of load. We use sql
>      extensively and consequently we ahve a large number of cron jobs that
>      fire up psql shells. I had thought using the methods found in pg would
>      help. I am planning to port our applications over to C but... that's a
>      several month job. I'm looking for a quick fix here to buy me some
>      time. The other side of that is C is not my preferred language of
>      choice... I do pascal and perl.
> 
>      I am not clear yet as how to navigate multiple rows on the recordset
>      return using the pg method. Ideally I want to put all the results into
>      an array like this:
>      username|password|lastname
>      marty|next|Robinson
>      mel|foreman|Johnson
> 
>      The <$ret = $result->fetchrow> function only returns a portion of the
>      row.
Just change that $ret to @ret, and it will return the entire row.
After that your code becomes something like:
  $query = "Select
username,password,knickname,emailaddy,forward,ip,date from temp";  $conn = Pg::connectdb("dbname=$dbname");  $result =
$conn->exec($query); while (@row = $result->fetchrow) {    @row = join("|", @row);  }  for $member (@set) {      print
"$member\n"; }
 


or:
  $query = "Select
username,password,knickname,emailaddy,forward,ip,date from temp";  $conn = Pg::connectdb("dbname=$dbname");  $result =
$conn->exec($query); while (@row = $result->fetchrow) {    print join("|", @row) . "\n";  }
 


You might want to add an error check on the Connection.  (i.e. check the
value of $conn before using it).

Hope this helps...james
> 
>      I have created a lttle subroutine using the getvalue method that
>      iterates thru each row and pushes it into an array. This will work for
>      now. However I am curious, is there another, more simpler method to
>      accomplish this?
> 
>      Thanks,
> 
>      -Rob
> 
>      ############################
>      #       Subroutine         #
>      ############################
> 
>      $query = "Select username,password,knickname,emailaddy,forward,ip,date
>      from temp";
>      $conn = Pg::connectdb("dbname=$dbname");
>      $result = $conn->exec($query);
>      $tuples = $result->ntuples;
>      $fields = $result->nfields;
>      $count = 0;
>      while ($count < $tuples ) {
>            $fieldcount = 0;
>            for ($fieldcount = 0;;$fieldcount++) {
>                $entry .= $result->getvalue($count,$fieldcount);
>                if ($fieldcount != $fields) {
>                   $entry .= "\|";
>                }
>                 else {
>                   last;
>                }
>            }
>            push(@set,$entry);
>            $entry="";
>            $count++;
>      }
> 
>      for $member (@set) {
>           print "$member\n";
>      }
> 
>      ###########################
>      When run it produces this output. We need to keep this format for
>      existing scripts.
> 
>      marty|marty|fghu|sdfg@fghj|t|208.130.70.9|Tue Jun 22 00:09:34 1999 CDT
>      marty1|marty|fghu|sdfg@fghj|f|208.130.70.9|Tue Jun 22 00:10:18 1999
>      CDT