Re: [INTERFACES] Perl Interface - Mailing list pgsql-interfaces
From | James Olin Oden |
---|---|
Subject | Re: [INTERFACES] Perl Interface |
Date | |
Msg-id | 376FD4CE.489201E9@lee.k12.nc.us Whole thread Raw |
In response to | Perl Interface (robert_hiltibidal_at_cms08405@ccmailgw.state.il.us) |
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
pgsql-interfaces by date: