Perl-Postgres connection: "Commit" method not found in DBI. Advice?

--0-1997764129-1172677834=:62704
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I am working with a perl file intended to enter a postgresql database (as user postgres), select a list of key values based on one of those values and delete any records which match the returned results. The purpose of the program is to delete inactive records from the database that are over two years in age.

(For the record, the system in question is running SuSE 9.3 os, Apache2, Postgres 8.1.3, and appears to be running Perl 5.8.6)

When I first ran the perl script, it did not appear to be working; entering the database afterwards would show that the data was still present. After finding and uncommenting a 'commit' statement at the end, I am now ending up with the error message

Can't locate object method "commit" via package "DBI::st" at ./purge_acnt line
242 (#1)
(F) You called a method correctly, and it correctly indicated a package
functioning as a class, but that package doesn't define that particular
method, nor does any of its base classes. See perlobj.
Uncaught exception from user code:
Can't locate object method "commit" via package "DBI::st" at ./purge_acnt line 242.
at ./purge_acnt line 242
Database handle destroyed without explicit disconnect at ./purge_acnt line 242.
Database handle destroyed without explicit disconnect at ./purge_acnt line 242.

I am asuming that the last two lines are because the program crashed, artificially severing the connections to the DB. (The last couple of lines of the program are normal disconnect statements, but occur after the 'commit' line that triggered the error.) It's the rest of the error that I am confused about. If I am reading it correctly, it seems to be saying that there is no 'commit' function in the DBI. Do I need to update my DBI files somehow, or is there another way to prevent this error from occuring? And if I have to update, would someone please be kind enough to link me to a good set of instructions on doing so?

Thank you for your consideration.


---------------------------------
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.
--0-1997764129-1172677834=:62704--

RE: Perl-Postgres connection: "Commit" method not found in DBI. Advice?

It looks like you're calling commit() on the $sth instead of the $dbh.

Philip

Andrew Edson wrote:
> I am working with a perl file intended to enter a postgresql database
> (as user postgres), select a list of key values based on one of those
> values and delete any records which match the returned results. The
> purpose of the program is to delete inactive records from the
> database that are over two years in age. =20
>=20
> (For the record, the system in question is running SuSE 9.3 os,
> Apache2, Postgres 8.1.3, and appears to be running Perl 5.8.6)=20
>=20
> When I first ran the perl script, it did not appear to be working;
> entering the database afterwards would show that the data was still
> present. After finding and uncommenting a 'commit' statement at the
> end, I am now ending up with the error message =20
>=20
> Can't locate object method "commit" via package "DBI::st" at
> ./purge_acnt line 242 (#1)
> (F) You called a method correctly, and it correctly indicated a
> package functioning as a class, but that package doesn't define
> that particular method, nor does any of its base classes. See
> perlobj. Uncaught exception from user code:
> Can't locate object method "commit" via package "DBI::st" at
> ./purge_acnt line 242. at ./purge_acnt line 242
> Database handle destroyed without explicit disconnect at ./purge_acnt
> line 242.=20
> Database handle destroyed without explicit disconnect at ./purge_acnt
> line 242.=20
>=20
> I am asuming that the last two lines are because the program
> crashed, artificially severing the connections to the DB. (The last
> couple of lines of the program are normal disconnect statements, but
> occur after the 'commit' line that triggered the error.) It's the
> rest of the error that I am confused about. If I am reading it
> correctly, it seems to be saying that there is no 'commit' function
> in the DBI. Do I need to update my DBI files somehow, or is there
> another way to prevent this error from occuring? And if I have to
> update, would someone please be kind enough to link me to a good set
> of instructions on doing so? =20
>=20
> Thank you for your consideration.
>=20
>=20
> ---------------------------------
> Sucker-punch spam with award-winning protection.
> Try the free Yahoo! Mail Beta.

Re: Perl-Postgres connection: "Commit" method not found in DBI. Advice?

Please post some code, it seems like there is something wrong in
purge_acnt. Line 242 and a few lines before and after should be
interesting, and the lines around DBI->connect.

Alexander

On 28.02.2007 16:50, Andrew Edson wrote:
> I am working with a perl file intended to enter a postgresql database (as user postgres), select a list of key values based on one of those values and delete any records which match the returned results. The purpose of the program is to delete inactive records from the database that are over two years in age.
>
> (For the record, the system in question is running SuSE 9.3 os, Apache2, Postgres 8.1.3, and appears to be running Perl 5.8.6)
>
> When I first ran the perl script, it did not appear to be working; entering the database afterwards would show that the data was still present. After finding and uncommenting a 'commit' statement at the end, I am now ending up with the error message
>
> Can't locate object method "commit" via package "DBI::st" at ./purge_acnt line
> 242 (#1)
> (F) You called a method correctly, and it correctly indicated a package
> functioning as a class, but that package doesn't define that particular
> method, nor does any of its base classes. See perlobj.
> Uncaught exception from user code:
> Can't locate object method "commit" via package "DBI::st" at ./purge_acnt line 242.
> at ./purge_acnt line 242
> Database handle destroyed without explicit disconnect at ./purge_acnt line 242.
> Database handle destroyed without explicit disconnect at ./purge_acnt line 242.
>
> I am asuming that the last two lines are because the program crashed, artificially severing the connections to the DB. (The last couple of lines of the program are normal disconnect statements, but occur after the 'commit' line that triggered the error.) It's the rest of the error that I am confused about. If I am reading it correctly, it seems to be saying that there is no 'commit' function in the DBI. Do I need to update my DBI files somehow, or is there another way to prevent this error from occuring? And if I have to update, would someone please be kind enough to link me to a good set of instructions on doing so?
>
> Thank you for your consideration.
>
>
> ---------------------------------
> Sucker-punch spam with award-winning protection.
> Try the free Yahoo! Mail Beta.
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

RE: Perl-Postgres connection: "Commit" method not found in DBI. Advice?

Isn't AutoCommit turned off at the database handle level?

DBI->connect('dbi:xxx:', uid, pwd, {AutoCommit =3D> 0})

Sorry, I have not been following this thread.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Martin Gainty [mailto:mgainty@hotmail.com]=20
Sent: Wednesday, February 28, 2007 6:12 PM
To: Alexander Foken; Andrew Edson
Cc: dbi-users@perl.org
Subject: Re: Perl-Postgres connection: 'Commit' method not found in DBI. =
Advice?

yep--
Aparently postgres thinks its smarter than anyone that wants to use it =
and has Auto-commit ALWAYS turned on
If you find a way to turn this *feature* off let me know because it is =
massively counter intuitive to normal operation of any other db on the =
planet
Thanks,
Martin--
------------------------------------------------------------ -------------=
--=20
This e-mail message (including attachments, if any) is intended for the =
use of the individual or entity to which it is addressed and may contain =
information that is privileged, proprietary , confidential and exempt =
from disclosure. If you are not the intended recipient, you are notified =
that any dissemination, distribution or copying of this communication is =
strictly prohibited.
------------------------------------------------------------ -------------=
--=20
Le pr=E9sent message =E9lectronique (y compris les pi=E8ces qui y sont =
annex=E9es, le cas =E9ch=E9ant) s'adresse au destinataire indiqu=E9 et =
peut contenir des renseignements de caract=E8re priv=E9 ou confidentiel. =
Si vous n'=EAtes pas le destinataire de ce document, nous vous signalons =
qu'il est strictement interdit de le diffuser, de le distribuer ou de le =
reproduire.
----- Original Message -----=20
From: "Alexander Foken"
To: "Andrew Edson"
Cc:
Sent: Wednesday, February 28, 2007 11:55 AM
Subject: Re: Perl-Postgres connection: 'Commit' method not found in DBI. =
Advice?


> Please post some code, it seems like there is something wrong in=20
> purge_acnt. Line 242 and a few lines before and after should be=20
> interesting, and the lines around DBI->connect.
>=20
> Alexander
>=20
> On 28.02.2007 16:50, Andrew Edson wrote:
>> I am working with a perl file intended to enter a postgresql database =
(as user postgres), select a list of key values based on one of those =
values and delete any records which match the returned results. The =
purpose of the program is to delete inactive records from the database =
that are over two years in age.
>> =20
>> (For the record, the system in question is running SuSE 9.3 os, =
Apache2, Postgres 8.1.3, and appears to be running Perl 5.8.6)
>> =20
>> When I first ran the perl script, it did not appear to be working; =
entering the database afterwards would show that the data was still =
present. After finding and uncommenting a 'commit' statement at the =
end, I am now ending up with the error message=20
>> =20
>> Can't locate object method "commit" via package "DBI::st" at =
../purge_acnt line
>> 242 (#1)
>> (F) You called a method correctly, and it correctly indicated a =
package
>> functioning as a class, but that package doesn't define that =
particular
>> method, nor does any of its base classes. See perlobj.
>> Uncaught exception from user code:
>> Can't locate object method "commit" via package "DBI::st" at =
../purge_acnt line 242.
>> at ./purge_acnt line 242
>> Database handle destroyed without explicit disconnect at ./purge_acnt =
line 242.
>> Database handle destroyed without explicit disconnect at ./purge_acnt =
line 242.
>> =20
>> I am asuming that the last two lines are because the program =
crashed, artificially severing the connections to the DB. (The last =
couple of lines of the program are normal disconnect statements, but =
occur after the 'commit' line that triggered the error.) It's the rest =
of the error that I am confused about. If I am reading it correctly, it =
seems to be saying that there is no 'commit' function in the DBI. Do I =
need to update my DBI files somehow, or is there another way to prevent =
this error from occuring? And if I have to update, would someone please =
be kind enough to link me to a good set of instructions on doing so?
>> =20
>> Thank you for your consideration.
>>
>> =20
>> ---------------------------------
>> Sucker-punch spam with award-winning protection.
>> Try the free Yahoo! Mail Beta.
>> =20
>=20
>=20
> --=20
> Alexander Foken
> mailto:alexander@foken.de http://www.foken.de/alexander/
>=20
>

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: Perl-Postgres connection: "Commit" method not found in DBI. Advice?

------=_Part_61807_23242594.1172725982246
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 2/28/07, Martin Gainty wrote:
>
> yep--
> Aparently postgres thinks its smarter than anyone that wants to use it and
> has Auto-commit ALWAYS turned on
> If you find a way to turn this *feature* off let me know because it is
> massively counter intuitive to normal operation of any other db on the
> planet
>


I'm not sure about PostgreSQL, but Informix has a somewhat similar mode - or
two somewhat similar modes. An unlogged (Informix) database has no
transaction support at all; you cannot do transactions, and each statement
is nominally a self-contained transaction (except that if an error occurs
part way through, the changes made so far are not undone - for DML
statements). In a regular logged database, each statement is a separate
transaction - complete with rollback so that if a statement fails part way
through, the database is left as if the statement had never been executed.
In a logged database, you can suppress the auto-commit mode by an explicit
BEGIN WORK statement. This begins a (multi-statement) transaction that is
terminated by COMMIT WORK or ROLLBACK WORK. If something happens to the
client before COMMIT is executed, the transaction is rolled back.

It doesn't take a lot of searching around the PostgreSQL documentation to
find:

BEGIN -- start a transaction blockSynopsis

BEGIN [ WORK | TRANSACTION ] [ *transaction_mode* [, ...] ]

where *transaction_mode* is one of:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY


I suspect you'll find that this 'turns off autocommit' for you.

--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0226 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_61807_23242594.1172725982246--

RE: Perl-Postgres connection: "Commit" method not found in DBI. Advice?

--0-1335275504-1172755712=:25214
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

@Philip: You were correct. Thanks for the advice; I'm new to Perl, and I didn't realize that wasn't correct. After changing it as you suggested, the program seems to work. Again, thanks.

@Martin: I'll take a look around the system I'm working with and see if I can figure out where the auto-commit gets set. I'll post when/if I find something.


"Garrett, Philip (MAN-Corporate)" wrote:

It looks like you're calling commit() on the $sth instead of the $dbh.

Philip


---------------------------------
Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
--0-1335275504-1172755712=:25214--

Re: Perl-Postgres connection: "Commit" method not found in DBI. Advice?

------=_NextPart_000_0009_01C75C3E.8CBB95A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Yes I would prefer to steer clear of Postgres functions as they seem to =
be able to only handle fetches from a single table
But as of right now this is the only option for disabling Autocommit =
*feature*

Thanks/Merci Beaucoups-
Martin--
------------------------------------------------------------ -------------=
--=20
This e-mail message (including attachments, if any) is intended for the =
use of the individual or entity to which it is addressed and may contain =
information that is privileged, proprietary , confidential and exempt =
from disclosure. If you are not the intended recipient, you are notified =
that any dissemination, distribution or copying of this communication is =
strictly prohibited.
------------------------------------------------------------ -------------=
--=20
Le pr=E9sent message =E9lectronique (y compris les pi=E8ces qui y sont =
annex=E9es, le cas =E9ch=E9ant) s'adresse au destinataire indiqu=E9 et =
peut contenir des renseignements de caract=E8re priv=E9 ou confidentiel. =
Si vous n'=EAtes pas le destinataire de ce document, nous vous signalons =
qu'il est strictement interdit de le diffuser, de le distribuer ou de le =
reproduire.
----- Original Message -----=20
From: Jonathan Leffler=20
To: Martin Gainty=20
Cc: DBI Users Mailing List=20
Sent: Thursday, March 01, 2007 12:13 AM
Subject: Re: Perl-Postgres connection: 'Commit' method not found in =
DBI. Advice?





On 2/28/07, Martin Gainty wrote:
yep--
Aparently postgres thinks its smarter than anyone that wants to use =
it and has Auto-commit ALWAYS turned on
If you find a way to turn this *feature* off let me know because it =
is massively counter intuitive to normal operation of any other db on =
the planet=20



I'm not sure about PostgreSQL, but Informix has a somewhat similar =
mode - or two somewhat similar modes. An unlogged (Informix) database =
has no transaction support at all; you cannot do transactions, and each =
statement is nominally a self-contained transaction (except that if an =
error occurs part way through, the changes made so far are not undone - =
for DML statements). In a regular logged database, each statement is a =
separate transaction - complete with rollback so that if a statement =
fails part way through, the database is left as if the statement had =
never been executed. In a logged database, you can suppress the =
auto-commit mode by an explicit BEGIN WORK statement. This begins a =
(multi-statement) transaction that is terminated by COMMIT WORK or =
ROLLBACK WORK. If something happens to the client before COMMIT is =
executed, the transaction is rolled back.=20

It doesn't take a lot of searching around the PostgreSQL documentation =
to find:

BEGIN -- start a transaction block
Synopsis
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]where =
transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | =
REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
I suspect you'll find that this 'turns off autocommit' for you.

--=20
Jonathan Leffler < jonathan.leffler@gmail.com> #include =

Guardian of DBD::Informix - v2007.0226 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
------=_NextPart_000_0009_01C75C3E.8CBB95A0--