Thread: perl Pg module and result status
Dear all: i am somewhat new to both perl and PostgreSQL. I am writing
some perl scripts to store and retrieve information in a pgsql database,
using the Pg perl module. However i am having trouble doing checks after
issuing queries or commands.
According to the module documentation:
"
$result_status = $result->resultStatus
Returns the status of the result. For comparing the status you may use
one of the following constants depending upon the command executed:
- PGRES_EMPTY_QUERY
- PGRES_COMMAND_OK
- PGRES_TUPLES_OK
- PGRES_COPY_OUT
- PGRES_COPY_IN
- PGRES_BAD_RESPONSE
- PGRES_NONFATAL_ERROR
- PGRES_FATAL_ERROR
"
When I check the contents of $result_status it is not any of the
mentioned constants, but a number (i am now getting 7 for a
$result->getvalue command after a failed select statement). If i issue
the same select statement in the pgsql terminal everything works. Thus i
don't understand what could be happening.
However, comparisons of the kind of:
if ($result_status != PGRES_COMMAND_OK)
appear to work most of the times (am i comparing against a number?)
Any help is appreciated.
Thanks in advance,
Fernan
i can't seem to locate the reference to the documentation
you mention in the perldoc for DBD::Pg, can you reference
it?
as a general note, you should be using the DBI interface
with DBD::Pg and not calling the methods in DBD::Pg
directly. they're "private" and likely to change.
in other words your perl script should "use DBI;" and
not mention DBD::Pg at all.
try this:
use DBI;
$user = '';
$pass = '';
$dburl = ''; # should be: dbi:Pg:dbname=[your database name]
$db = DBI->connect($dburl, $user, $pass, {AutoCommit=>0} )
or die "Can't connect to db";
$sth = $db->prepare( q{ select current_timestamp } )
or die "Can't prepare statement: " . $db->errstr;
$sth->execute()
or die "Can't execute statement: " . $db->errstr;
$result = $sth->fetchrow_array();
($result) ? print "$result\n" : print "error fetching: " . $db->errstr;
__END__
hope this helps
--e--
On Wed, 20 Sep 2000 23:59:41 GMT, Fern�n Ag�ero wrote:
> Dear all: i am somewhat new to both perl and PostgreSQL. I am writing
> some perl scripts to store and retrieve information in a pgsql database,
> using the Pg perl module. However i am having trouble doing checks after
> issuing queries or commands.
>
> According to the module documentation:
> "
> $result_status = $result->resultStatus
> Returns the status of the result. For comparing the status you may use
> one of the following constants depending upon the command executed:
> - PGRES_EMPTY_QUERY
> - PGRES_COMMAND_OK
> - PGRES_TUPLES_OK
> - PGRES_COPY_OUT
> - PGRES_COPY_IN
> - PGRES_BAD_RESPONSE
> - PGRES_NONFATAL_ERROR
> - PGRES_FATAL_ERROR
> "
>
> When I check the contents of $result_status it is not any of the
> mentioned constants, but a number (i am now getting 7 for a
> $result->getvalue command after a failed select statement). If i issue
> the same select statement in the pgsql terminal everything works. Thus i
> don't understand what could be happening.
>
> However, comparisons of the kind of:
> if ($result_status != PGRES_COMMAND_OK)
> appear to work most of the times (am i comparing against a number?)
>
> Any help is appreciated.
>
> Thanks in advance,
>
>
> Fernan
>
>
>
On Wed, Sep 20, 2000 at 07:27:40PM -0400, Edward Q. Bridges wrote:
> i can't seem to locate the reference to the documentation
> you mention in the perldoc for DBD::Pg, can you reference
> it?
I believe he is using straight Pg - the perl interface to Postgres.
AFAIK, that's independant of DBI::Pg, which is the Postgres
driver for DBI.
I agree with you, however: I've found DBD::Pg quite a bit easier
to work with than Pg. It also gives you the possibility of
moving your code to another database with relatively small
changes.
If you don't mind me commenting on your code:
> use DBI;
> $user = '';
> $pass = '';
> $dburl = ''; # should be: dbi:Pg:dbname=[your database name]
> $db = DBI->connect($dburl, $user, $pass, {AutoCommit=>0} )
> or die "Can't connect to db";
Is there a reason you're not using constants? e.g.
use constant DB_USER => 'foo';
use constant DB_PASSWD => 'qwerty';
You might also want to add the "$DBI::errstr" to the error message
on a connection failure.
(Sorry, I just couldn't resist nit-picking :D)
HTH,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Is uniformity [of religion] attainable? Millions of innocent men, women, and
children, since the introduction of Christianity, have been burnt, tortured,
fined, imprisoned; yet we have not advanced one inch towards uniformity.
-- Thomas Jefferson, "Notes on Virginia"
Attachment
On Wed, 20 Sep 2000 21:24:17 -0400, Neil Conway wrote: > I believe he is using straight Pg - the perl interface to Postgres. > AFAIK, that's independant of DBI::Pg, which is the Postgres > driver for DBI. > ahhh! "i see" said the blind man :) > I agree with you, however: I've found DBD::Pg quite a bit easier > to work with than Pg. It also gives you the possibility of > moving your code to another database with relatively small > changes. > yes, i agree totally. it also makes using a new database a lot easier, since you're just calling the same functions. > If you don't mind me commenting on your code: not at all! > > $user = ''; > > $pass = ''; > > $dburl = ''; # should be: dbi:Pg:dbname=[your database name] > Is there a reason you're not using constants? e.g. > > use constant DB_USER => 'foo'; > use constant DB_PASSWD => 'qwerty'; > nope. didn't occur to me for this sort of example. also seems to bring in extra overhead. is there an advantage? > You might also want to add the "$DBI::errstr" to the error message > on a connection failure. > yeah, i used it on the other method calls. should have put it there as well. i think, tho, that the driver will by default print out that error string when it dies. regards --e--
Hi folks!
Thanks for the advice. Excuse my delay in replying: I've been filled with
work.
And yes, I was using the Pg module that comes with pgsql, and not the DBI:Pg
one. Now I switched to DBI and is surprising how much cleaner code you get,
particularly since the checks for errors are done by DBI (PrintErrors => 1,
RaiseErrors => 1).
I have yet to get into more depth with DBI but, as you said, I find it
easier to get things done. Not to mention the possibility of moving from
one RDMS to another and just changing a couple of lines in the scripts.
Thanks again,
Fernan
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 9/20/00, 7:27:40 PM, "Edward Q. Bridges" <ed.bridges@buzznik.com> wrote
regarding Re: [GENERAL] perl Pg module and result status:
> i can't seem to locate the reference to the documentation
> you mention in the perldoc for DBD::Pg, can you reference
> it?
> as a general note, you should be using the DBI interface
> with DBD::Pg and not calling the methods in DBD::Pg
> directly. they're "private" and likely to change.
> in other words your perl script should "use DBI;" and
> not mention DBD::Pg at all.
> try this:
> use DBI;
> $user = '';
> $pass = '';
> $dburl = ''; # should be: dbi:Pg:dbname=[your database name]
> $db = DBI->connect($dburl, $user, $pass, {AutoCommit=>0} )
> or die "Can't connect to db";
> $sth = $db->prepare( q{ select current_timestamp } )
> or die "Can't prepare statement: " . $db->errstr;
> $sth->execute()
> or die "Can't execute statement: " . $db->errstr;
> $result = $sth->fetchrow_array();
> ($result) ? print "$result\n" : print "error fetching: " . $db->errstr;
> __END__
> hope this helps
> --e--
> On Wed, 20 Sep 2000 23:59:41 GMT, Fern n Agero wrote:
> > Dear all: i am somewhat new to both perl and PostgreSQL. I am writing
> > some perl scripts to store and retrieve information in a pgsql database,
> > using the Pg perl module. However i am having trouble doing checks after
> > issuing queries or commands.
> >
> > According to the module documentation:
> > "
> > $result_status = $result->resultStatus
> > Returns the status of the result. For comparing the status you may use
> > one of the following constants depending upon the command executed:
> > - PGRES_EMPTY_QUERY
> > - PGRES_COMMAND_OK
> > - PGRES_TUPLES_OK
> > - PGRES_COPY_OUT
> > - PGRES_COPY_IN
> > - PGRES_BAD_RESPONSE
> > - PGRES_NONFATAL_ERROR
> > - PGRES_FATAL_ERROR
> > "
> >
> > When I check the contents of $result_status it is not any of the
> > mentioned constants, but a number (i am now getting 7 for a
> > $result->getvalue command after a failed select statement). If i issue
> > the same select statement in the pgsql terminal everything works. Thus i
> > don't understand what could be happening.
> >
> > However, comparisons of the kind of:
> > if ($result_status != PGRES_COMMAND_OK)
> > appear to work most of the times (am i comparing against a number?)
> >
> > Any help is appreciated.
> >
> > Thanks in advance,
> >
> >
> > Fernan
> >
> >
> >
On Fri, 22 Sep 2000, [ISO-8859-1] Fern�n Ag�ero wrote:
> Hi folks!
>
> Thanks for the advice. Excuse my delay in replying: I've been filled with
> work.
>
> And yes, I was using the Pg module that comes with pgsql, and not the DBI:Pg
> one. Now I switched to DBI and is surprising how much cleaner code you get,
> particularly since the checks for errors are done by DBI (PrintErrors => 1,
> RaiseErrors => 1).
You may wish to turn off RaiseErrors...you can still get the dbi
errors by doing: $dbi = $DBI::errstr; Embed this in your query -
if (!defined $rv) ... $dbi = $DBI::errstr; and so on.
The reason to do this is that you can then write your own stderr and
include the dbi errstr...this is very handy if you want to beef up the
debugging output that gets dumped to the apache error_log (I use DBI
alongside CGI in perl scripts).
The problem with RaiseErrors is that it overwrites any stderr the
script is looking to dump to the error_log...just an FYI.
> I have yet to get into more depth with DBI but, as you said, I find it
> easier to get things done. Not to mention the possibility of moving from
> one RDMS to another and just changing a couple of lines in the scripts.
The author of DBI (Tim Bunce) has an excellent book published by Tim
O'Reilly called Programming The Perl DBI Interface.
Cheers,
Tom
--------------------------------------------------------------------
SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
IS Coordinator / DBA Phone: 718-354-5528
Fax: 718-354-5056
--------------------------------------------------------------------
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |---------- linux The Power To Serve
--------------------------------------------------------------------