Loading ...
Sorry, an error occurred while loading the content.

Re: DBD::Informix "Not in transaction" error

Expand Messages
  • Rick Cochran
    My deepest apologies to all. By beating on the problem long enough, I discovered that although the database had transaction logging enabled, I was connecting
    Message 1 of 8 , May 26, 2000
    View Source
    • 0 Attachment
      My deepest apologies to all. By beating on the problem long enough, I
      discovered that although the database had transaction logging enabled, I
      was connecting with AutoCommit _on_. Thus, there was no possibility of a
      "select ... for update" being "in a transaction". I will venture a small
      gripe about the error message being cryptic, and retreat in embarrassed
      silence.

      The "complete, minimal, self-contained example" you suggest is surely the
      best advice possible.

      My transition from "no transaction logging" to "transaction logging" is
      proving to be something of a challenge.

      Thank you all for your kind responses. Is there a DBD::Informix FAQ?

      -Rick

      At 09:56 PM 5/25/2000 -0700, Jonathan Leffler wrote:
      >Rick Cochran wrote:
      >
      > > The following code:
      > >
      > > $select_account = $dbh->prepare("SELECT * FROM accounts WHERE usernetid
      > > = ? AND fundsource = ? FOR UPDATE")
      > > or return("Preparing select_account (".$dbh->errstr.")\n");
      > > $select_account->execute($usernetid, $fundsource)
      > > or return("Selecting account (".$dbh->errstr.")\n");
      > >
      > > gives me the following error:
      > >
      > > curdie> perl xx4
      > > DBD::Informix::st execute failed: SQL: -255: Not in transaction. at
      > npstdlib.pm
      > > line 575.
      > > Selecting account (SQL: -255: Not in transaction.)
      > >
      > > but only if transaction logging is enabled.
      > >
      > > Any clues?
      > >
      > > Using:
      > > perl 5.6.0
      > > DBI 1.13
      > > DBD::Informix 1.00.PC1
      > > Informix IDS 7.31.UC3-1
      > > AIX 4.3.2.0
      >
      >Frank Ullrich quite properly asked you if you're using BEGIN WORK
      >before you open the cursor for update. You responded that you are.
      >Kevin Brannen commented that he's encountered the error when AutoCommit
      >is not set as expected. They're both right on track.
      >
      >You get that error when you try to open a FOR UPDATE cursor when
      >there is no transaction in progress. Since you are getting the error,
      >it would appear that your transaction has been committed.
      >
      >About the only possible cause of trouble would be if there's a problem
      >with DBD::Informix such that the cursor isn't declared WITH HOLD as
      >well and AutoCommit is enabled.
      >
      >Can you *please* send a complete, minimal, self-contained example of
      >the problem. You may assume that the database has a transaction log,
      >but everything else should be supplied.
      >
      >--
      >Jonathan Leffler (jleffler@..., jleffler@...)
      >Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN
      >#include <disclaimer.h>

      |Rick Cochran phone: 607-255-7618|
      |Network and Computing Systems, Cornell CIT FAX: 607-255-8521|
      |730 Rhodes Hall, Ithaca, N.Y. 14853 email: rcc2@...|



      ------------------------------------------------------------------------------
      DBI HOME PAGE AND ARCHIVES: http://www.symbolstone.org/technology/perl/DBI/
      To unsubscribe from this list, please visit: http://www.isc.org/dbi-lists.html
      If you are without web access, or if you are having trouble with the web page,
      please send mail to dbi-users-request@... with the subject line of:
      'unsubscribe'.
      ------------------------------------------------------------------------------
    • Jonathan Leffler
      ... There are two possibilities here. * Informix servers do not allow a WITH HOLD cursor with FOR UPDATE too, so DBD::Informix can t handle it for you, or *
      Message 2 of 8 , May 27, 2000
      View Source
      • 0 Attachment
        Rick Cochran wrote:

        > My deepest apologies to all. By beating on the problem long enough, I
        > discovered that although the database had transaction logging enabled, I
        > was connecting with AutoCommit _on_. Thus, there was no possibility of a
        > "select ... for update" being "in a transaction".

        There are two possibilities here.
        * Informix servers do not allow a WITH HOLD cursor with FOR UPDATE
        too, so DBD::Informix can't handle it for you, or
        * DBD::Informix should be dealing with it for you.

        If it's the first, I should have documented it. I suspect there's
        an oversight somewhere and you're merely the first to run foul of it.
        This was mentioned tersely as a possibility in my original response.

        And, in case anybody's forgotten the beginning of March, AutoCommit
        is not my favourite subject.

        > I will venture a small gripe about the error message being cryptic, and retreat
        > in embarrassed
        > silence.

        Gripe accepted, though there's not much I can do for you about it.


        > The "complete, minimal, self-contained example" you suggest is surely the
        > best advice possible.
        >
        > My transition from "no transaction logging" to "transaction logging" is
        > proving to be something of a challenge.
        >
        > Thank you all for your kind responses. Is there a DBD::Informix FAQ?

        In the Notes/FAQ file. It doesn't cover this. Otherwise,
        Informix FAQs are found at the International Informix User's
        Group (IIUG) web site at:

        http://www.iiug.org

        > -Rick
        >
        > At 09:56 PM 5/25/2000 -0700, Jonathan Leffler wrote:
        > >Rick Cochran wrote:
        > >
        > > > The following code:
        > > >
        > > > $select_account = $dbh->prepare("SELECT * FROM accounts WHERE usernetid
        > > > = ? AND fundsource = ? FOR UPDATE")
        > > > or return("Preparing select_account (".$dbh->errstr.")\n");
        > > > $select_account->execute($usernetid, $fundsource)
        > > > or return("Selecting account (".$dbh->errstr.")\n");
        > > >
        > > > gives me the following error:
        > > >
        > > > curdie> perl xx4
        > > > DBD::Informix::st execute failed: SQL: -255: Not in transaction. at
        > > npstdlib.pm
        > > > line 575.
        > > > Selecting account (SQL: -255: Not in transaction.)
        > > >
        > > > but only if transaction logging is enabled.
        > > >
        > > > Any clues?
        > > >
        > > > Using:
        > > > perl 5.6.0
        > > > DBI 1.13
        > > > DBD::Informix 1.00.PC1
        > > > Informix IDS 7.31.UC3-1
        > > > AIX 4.3.2.0
        > >
        > >Frank Ullrich quite properly asked you if you're using BEGIN WORK
        > >before you open the cursor for update. You responded that you are.
        > >Kevin Brannen commented that he's encountered the error when AutoCommit
        > >is not set as expected. They're both right on track.
        > >
        > >You get that error when you try to open a FOR UPDATE cursor when
        > >there is no transaction in progress. Since you are getting the error,
        > >it would appear that your transaction has been committed.
        > >
        > >About the only possible cause of trouble would be if there's a problem
        > >with DBD::Informix such that the cursor isn't declared WITH HOLD as
        > >well and AutoCommit is enabled.
        > >
        > >Can you *please* send a complete, minimal, self-contained example of
        > >the problem. You may assume that the database has a transaction log,
        > >but everything else should be supplied.




        --
        Jonathan Leffler (jleffler@..., jleffler@...)
        Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN
        #include <disclaimer.h>




        ------------------------------------------------------------------------------
        DBI HOME PAGE AND ARCHIVES: http://www.symbolstone.org/technology/perl/DBI/
        To unsubscribe from this list, please visit: http://www.isc.org/dbi-lists.html
        If you are without web access, or if you are having trouble with the web page,
        please send mail to dbi-users-request@... with the subject line of:
        'unsubscribe'.
        ------------------------------------------------------------------------------
      • Bill Rothanburg
        ... Informix servers do allow a WITH HOLD cursor with a FOR UPDATE select. I m actually doing this in a program I wrote and needed to implement the Hold
        Message 3 of 8 , May 30, 2000
        View Source
        • 0 Attachment
          Jonathan Leffler wrote:

          > Rick Cochran wrote:
          >
          > > My deepest apologies to all. By beating on the problem long enough, I
          > > discovered that although the database had transaction logging enabled, I
          > > was connecting with AutoCommit _on_. Thus, there was no possibility of a
          > > "select ... for update" being "in a transaction".
          >
          > There are two possibilities here.
          > * Informix servers do not allow a WITH HOLD cursor with FOR UPDATE
          > too, so DBD::Informix can't handle it for you, or
          > * DBD::Informix should be dealing with it for you.
          >
          > If it's the first, I should have documented it. I suspect there's
          > an oversight somewhere and you're merely the first to run foul of it.
          > This was mentioned tersely as a possibility in my original response.
          >
          >

          Informix servers do allow a WITH HOLD cursor with a FOR UPDATE select. I'm actually
          doing this in a program I wrote and needed to implement the Hold Cursor option in
          DBD::Informix.

          I also have AutoCommit turned off. (I need the performance..;-)


          Btw, my system is running IDS 7.30 UC6 on HP-UX 10.20.


          Regards,

          Bill


          --
          Bill Rothanburg (brothanb@...)
          Network Integration Specialist
          DHL Regional Services, Ltd. [FLL-RSO]
          Fort Lauderdale, FL USA




          ------------------------------------------------------------------------------
          DBI HOME PAGE AND ARCHIVES: http://www.symbolstone.org/technology/perl/DBI/
          To unsubscribe from this list, please visit: http://www.isc.org/dbi-lists.html
          If you are without web access, or if you are having trouble with the web page,
          please send mail to dbi-users-request@... with the subject line of:
          'unsubscribe'.
          ------------------------------------------------------------------------------
        Your message has been successfully submitted and would be delivered to recipients shortly.