From: James Olin Oden <joden@lee.k12.nc.us>
To: robert_hiltibidal_at_cms08405@ccmailgw.state.il.us
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Perl Interface
Date: Tue, 22 Jun 1999 14:24:14 -0400
Message-ID: <376FD4CE.489201E9@lee.k12.nc.us> (raw)
References: <9906229300.AA930073628@ccmailgw.state.il.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: joden@lee.k12.nc.us, robert_hiltibidal_at_cms08405@ccmailgw.state.il.us
Subject: Re: [INTERFACES] Perl Interface
In-Reply-To: <376FD4CE.489201E9@lee.k12.nc.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox