Thread: Perl Interface
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,firstnameetc. 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
adminmstrationbox does see a fair amount of load. We use sql extensively and consequently we ahve a large number of
cronjobs that fire up psql shells. I had thought using the methods found in pg would help. I am planning to
portour 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
notclear yet as how to navigate multiple rows on the recordset return using the pg method. Ideally I want to put
allthe 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. I have created a lttle
subroutineusing 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|TueJun 22 00:10:18 1999 CDT
> 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. See the Free Pascal page. A great cross-platform compiler, both Borland Pascal & Delphi compatible. http://tfdec1.fys.kuleuven.ac.be/~michael/fpc/ On the contributed units page, you will find a postgres unit, which is a pascal wrapper for libpq (postgres 'c' library). I have been using this for about a year now on Linux. Works wonderfully and allows me to stay with the language of my choice ;-). GPC is another cross-platform compiler more focused on portability than FPC. It can also wrap 'c' libraries, but I have no experience with it yet. Cheers! Ken > > 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. > > 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 > > >
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
Robert,
have you seen DBI/DBD perl interface to PostgreSQL ?
I'd recommend you to try it.
Your script looks like (not tested):
------------------------------------
#!/usr/bin/perluse DBI; my $dbname = 'test';my $query = "Select username,password,knickname,emailaddy,forward,ip,date
fromtemp";$dbh = DBI->connect("dbi:Pg:dbname=$dbname") or die $DBI::errstr;my $sth = $dbh->prepare ( $query ) or die
"Can'tprepare statement: $DBI::errstr\n\t$query"; $sth->execute() or die "Can't execute statement:
$DBI::errstr\n\t$query";while( my @ary = $sth->fetchrow() ) { print join("\|",@ary),"\n"; }$sth->finish or die
$DBI::errstr;
Regards,
Oleg
On Tue, 22 Jun 1999 robert_hiltibidal_at_cms08405@ccmailgw.state.il.us wrote:
> Date: Tue, 22 Jun 99 12:46:33 -0600
> From: robert_hiltibidal_at_cms08405@ccmailgw.state.il.us
> To: pgsql-interfaces@postgreSQL.org
> Subject: [INTERFACES] Perl Interface
>
>
> 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.
>
> 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 #
> ############################
>
>
>
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83