dbi-interbase-devel

[Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[<pko...@me...>]

Hello!

Is there a good portable solution for doing the following:

1. select something
2. if prepare failed (e. g. the database tables don't exist) then goto 3.
     else goto 4.
3. create database tables
4. execute statement

I run into the problem, that create tables fails because of open
transactions. Even disconnect fails in Step 3.

"DBD::InterBase::db disconnect failed: Unsuccessful execution caused by
system er
ror that does not preclude successful execution of subsequent statements
-cannot disconnect database with open transactions (1 active)"

I use connect with AutoCommit set to true.

Would the following be a good portable solution for DBD::InterBase and other
database drivers, too???

  $db->{AutoCommit} = 0;
  $st = $db->prepare($sql);
  if (!$st) {
    print "error ...";
    $db->rollback();
    return;
  }
  $st->execute();
  if (!$st) {
    print "error ...";
    $db->rollback();
    return;
  }
  ...
  $db->commit();

With other DBD drivers, e. g. ODBC I had no problem with creating database
tables after a prepare failed, even if AutoCommit was set to true. There was
no error message about open transactions. I worry about database drivers
which don't support transactions.

Any suggestions?

Regards,

Peter

Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[Daniel R. <dan...@gm...>]

hello

i've created two scripts doing what you described. the first is with
AutoCommit = 0, the second with AutoCommit = 1.

first: create an empty db with ib console.
- when you first run the script you see the error message for the failed prepare,
  the table is cerated and some data is inserted into it.
- second run: display content of the table created before.

you can find the scripts at http://ritz.dnsart.com/interbase

one question: what version of dbd-interbase are you using?


rgds
-daniel

ps: if you problem still exists and you're using an up to date version of the dbd
then it's probaly a bug (a missing commit or something). in this case please send me
a code example that causes the error. i then try to fix it...

----- Original Message -----
From: "Peter Köller" <pko...@me...>
To: <dbi...@li...>
Sent: Monday, July 30, 2001 11:32 AM
Subject: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?


> Hello!
>
> Is there a good portable solution for doing the following:
>
> 1. select something
> 2. if prepare failed (e. g. the database tables don't exist) then goto 3.
>      else goto 4.
> 3. create database tables
> 4. execute statement
>
> I run into the problem, that create tables fails because of open
> transactions. Even disconnect fails in Step 3.
>
> "DBD::InterBase::db disconnect failed: Unsuccessful execution caused by
> system er
> ror that does not preclude successful execution of subsequent statements
> -cannot disconnect database with open transactions (1 active)"
>
> I use connect with AutoCommit set to true.
>
> Would the following be a good portable solution for DBD::InterBase and other
> database drivers, too???
>
>   $db->{AutoCommit} = 0;
>   $st = $db->prepare($sql);
>   if (!$st) {
>     print "error ...";
>     $db->rollback();
>     return;
>   }
>   $st->execute();
>   if (!$st) {
>     print "error ...";
>     $db->rollback();
>     return;
>   }
>   ...
>   $db->commit();
>
> With other DBD drivers, e. g. ODBC I had no problem with creating database
> tables after a prepare failed, even if AutoCommit was set to true. There was
> no error message about open transactions. I worry about database drivers
> which don't support transactions.
>
> Any suggestions?
>
> Regards,
>
> Peter
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[<pko...@me...>]

Hello Daniel!

> one question: what version of dbd-interbase are you using?
0.28

I tried your first example with AutoCommit =3D 0 and it works. What a sha=
me
for me. I looked into my code and I think that I do the same as you. The
only difference is that I want to create more tables and some ALTER
statements for creating constraints.

So I isolated my CREATE and ALTER statements, inserted it into your code =
and
... it worked, too! Then I had a very close look at my code (spreaded ove=
r
several modules), inserted debug output which prints out every of my SQL
statements or connection and disconnection to the database. I found no
difference!

I always get the following error after executing the following alter tabl=
e:

ALTER TABLE "AUCTIONS"
ADD CONSTRAINT "FKEY_AUCTIONS_BIDDER"
FOREIGN KEY ("BIDDER") REFERENCES BIDDERS ("ID")

DBD::InterBase::db do failed: Unsuccessful execution caused by system err=
or
that
 does not preclude successful execution of subsequent statements
-lock conflict on no wait transaction
-unsuccessful metadata update
-object BIDDERS is in use

But I haven't used the table BIDDERS (only for creating the table bevor t=
he
ALTER table statement). The only unsuccessful statement before was the
SELECT which can't access the table because it doesn't exist first. But t=
his
SELECT is prepared on the table ITEMS not on BIDDERs. After I disconnect =
no
tables exists in the database, although if they should be created with
$dbh->do(...) before the ALTER statements.

Okay second try: I removed all ALTER statements =3D> I get no errors, but=
 when
I look with IB Console in the database after disconnecting and the perl
script has finished: There is no table! But there was no error, too!

I made a new database with the IB Console, because I thought that the
database could be corrupted. Doesn't matter.

I tried to disconnect and connected again between the CREATE and ALTER
statements. The same errors like above.

The only thing I found was that disconnection after the failed prepare
didn't work. An error occurs. Even in your code. That was the only thing
that failed in my code and in your code, too.

--- cut
# try to select from a non existing table
my $sth =3D $dbh->prepare("SELECT id,title,item_id FROM items;");

# create the table if failed
unless ($sth)
{

  $dbh->disconnect(); exit;
  -------------------------

  # create the table..
  $dbh->do(qq{
  CREATE TABLE "ITEMS" (
--- cut

DBD::InterBase::db prepare failed: Undefined name
-Dynamic SQL Error
-SQL error code =3D -204
-Table unknown
-ITEMS
DBD::InterBase::db disconnect failed: Unsuccessful execution caused by
system er
ror that does not preclude successful execution of subsequent statements
-cannot disconnect database with open transactions (1 active)

It can't be disconnected. Could this open transaction be the problem for =
my
code?

I feel helpless because my perl script works fine with DBD:ODBC and an
InterBase ODBC driver. And it works with an MS Access database, too.

Regards,

Peter


> -----Urspr=FCngliche Nachricht-----
> Von: dbi...@li...
> [mailto:dbi...@li...]Im Auftrag von
> Daniel Ritz
> Gesendet am: Montag, 30. Juli 2001 19:11
> An: dbi...@li...
> Betreff: Re: [Dbi-interbase-devel] How to close open transactions with
> AutoCommit =3D 1 in case of a failed prepare?
>
> hello
>
> i've created two scripts doing what you described. the first is with
> AutoCommit =3D 0, the second with AutoCommit =3D 1.
>
> first: create an empty db with ib console.
> - when you first run the script you see the error message for the
> failed prepare,
>   the table is cerated and some data is inserted into it.
> - second run: display content of the table created before.
>
> you can find the scripts at http://ritz.dnsart.com/interbase
>
> one question: what version of dbd-interbase are you using?
>
>
> rgds
> -daniel
>
> ps: if you problem still exists and you're using an up to date
> version of the dbd
> then it's probaly a bug (a missing commit or something). in this
> case please send me
> a code example that causes the error. i then try to fix it...
>
> ----- Original Message -----
> From: "Peter K=F6ller" <pko...@me...>
> To: <dbi...@li...>
> Sent: Monday, July 30, 2001 11:32 AM
> Subject: [Dbi-interbase-devel] How to close open transactions
> with AutoCommit =3D 1 in case of a failed prepare?
>
>
> > Hello!
> >
> > Is there a good portable solution for doing the following:
> >
> > 1. select something
> > 2. if prepare failed (e. g. the database tables don't exist)
> then goto 3.
> >      else goto 4.
> > 3. create database tables
> > 4. execute statement
> >
> > I run into the problem, that create tables fails because of open
> > transactions. Even disconnect fails in Step 3.
> >
> > "DBD::InterBase::db disconnect failed: Unsuccessful execution caused =
by
> > system er
> > ror that does not preclude successful execution of subsequent stateme=
nts
> > -cannot disconnect database with open transactions (1 active)"
> >
> > I use connect with AutoCommit set to true.
> >
> > Would the following be a good portable solution for
> DBD::InterBase and other
> > database drivers, too???
> >
> >   $db->{AutoCommit} =3D 0;
> >   $st =3D $db->prepare($sql);
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   $st->execute();
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   ...
> >   $db->commit();
> >
> > With other DBD drivers, e. g. ODBC I had no problem with
> creating database
> > tables after a prepare failed, even if AutoCommit was set to
> true. There was
> > no error message about open transactions. I worry about database driv=
ers
> > which don't support transactions.
> >
> > Any suggestions?
> >
> > Regards,
> >
> > Peter
> >
> >
> > _______________________________________________
> > Dbi-interbase-devel mailing list
> > Dbi...@li...
> > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> >
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[<pko...@me...>]

Hello Daniel,

now I have isolated a test script which can reproduce the problem I
mentioned yesterday.

The problem seems to be the following:

- prepare fails because of non-existing table
- disconnect (because the user can select another database)
- connect again to the same database because the user wants the same one
- create tables
- ERROR!!!

If the disconnect and connect are left out, then no ERROR occurs.

I attached a test script derived from my perl program.

Is this a bug?

Peter

> -----Urspr=FCngliche Nachricht-----
> Von: dbi...@li...
> [mailto:dbi...@li...]Im Auftrag von
> Daniel Ritz
> Gesendet am: Montag, 30. Juli 2001 19:11
> An: dbi...@li...
> Betreff: Re: [Dbi-interbase-devel] How to close open transactions with
> AutoCommit =3D 1 in case of a failed prepare?
>
> hello
>
> i've created two scripts doing what you described. the first is with
> AutoCommit =3D 0, the second with AutoCommit =3D 1.
>
> first: create an empty db with ib console.
> - when you first run the script you see the error message for the
> failed prepare,
>   the table is cerated and some data is inserted into it.
> - second run: display content of the table created before.
>
> you can find the scripts at http://ritz.dnsart.com/interbase
>
> one question: what version of dbd-interbase are you using?
>
>
> rgds
> -daniel
>
> ps: if you problem still exists and you're using an up to date
> version of the dbd
> then it's probaly a bug (a missing commit or something). in this
> case please send me
> a code example that causes the error. i then try to fix it...
>
> ----- Original Message -----
> From: "Peter K=F6ller" <pko...@me...>
> To: <dbi...@li...>
> Sent: Monday, July 30, 2001 11:32 AM
> Subject: [Dbi-interbase-devel] How to close open transactions
> with AutoCommit =3D 1 in case of a failed prepare?
>
>
> > Hello!
> >
> > Is there a good portable solution for doing the following:
> >
> > 1. select something
> > 2. if prepare failed (e. g. the database tables don't exist)
> then goto 3.
> >      else goto 4.
> > 3. create database tables
> > 4. execute statement
> >
> > I run into the problem, that create tables fails because of open
> > transactions. Even disconnect fails in Step 3.
> >
> > "DBD::InterBase::db disconnect failed: Unsuccessful execution caused =
by
> > system er
> > ror that does not preclude successful execution of subsequent stateme=
nts
> > -cannot disconnect database with open transactions (1 active)"
> >
> > I use connect with AutoCommit set to true.
> >
> > Would the following be a good portable solution for
> DBD::InterBase and other
> > database drivers, too???
> >
> >   $db->{AutoCommit} =3D 0;
> >   $st =3D $db->prepare($sql);
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   $st->execute();
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   ...
> >   $db->commit();
> >
> > With other DBD drivers, e. g. ODBC I had no problem with
> creating database
> > tables after a prepare failed, even if AutoCommit was set to
> true. There was
> > no error message about open transactions. I worry about database driv=
ers
> > which don't support transactions.
> >
> > Any suggestions?
> >
> > Regards,
> >
> > Peter
> >
> >
> > _______________________________________________
> > Dbi-interbase-devel mailing list
> > Dbi...@li...
> > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> >
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[Daniel R. <dan...@gm...>]

hello peter

yep, it's a bug! a failed prepare does no rollback or commit in AutoCommit mode and disconnect doesn't too, but they
should...
i'll fix this asap. for now there's a little workaround:

set AutoCommit to 0 right before the disconnect: (this makes disconnect to do a commit)
    $_db->{AutoCommit} = 0;

with this added to line 87 in the test script -> the tables are created and altered.

rgds
-daniel


----- Original Message -----
From: "Peter Köller" <pko...@me...>
To: <dbi...@li...>
Sent: Tuesday, July 31, 2001 10:58 AM
Subject: Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?


Hello Daniel,

now I have isolated a test script which can reproduce the problem I
mentioned yesterday.

The problem seems to be the following:

- prepare fails because of non-existing table
- disconnect (because the user can select another database)
- connect again to the same database because the user wants the same one
- create tables
- ERROR!!!

If the disconnect and connect are left out, then no ERROR occurs.

I attached a test script derived from my perl program.

Is this a bug?

Peter

> -----Ursprüngliche Nachricht-----
> Von: dbi...@li...
> [mailto:dbi...@li...]Im Auftrag von
> Daniel Ritz
> Gesendet am: Montag, 30. Juli 2001 19:11
> An: dbi...@li...
> Betreff: Re: [Dbi-interbase-devel] How to close open transactions with
> AutoCommit = 1 in case of a failed prepare?
>
> hello
>
> i've created two scripts doing what you described. the first is with
> AutoCommit = 0, the second with AutoCommit = 1.
>
> first: create an empty db with ib console.
> - when you first run the script you see the error message for the
> failed prepare,
>   the table is cerated and some data is inserted into it.
> - second run: display content of the table created before.
>
> you can find the scripts at http://ritz.dnsart.com/interbase
>
> one question: what version of dbd-interbase are you using?
>
>
> rgds
> -daniel
>
> ps: if you problem still exists and you're using an up to date
> version of the dbd
> then it's probaly a bug (a missing commit or something). in this
> case please send me
> a code example that causes the error. i then try to fix it...
>
> ----- Original Message -----
> From: "Peter Köller" <pko...@me...>
> To: <dbi...@li...>
> Sent: Monday, July 30, 2001 11:32 AM
> Subject: [Dbi-interbase-devel] How to close open transactions
> with AutoCommit = 1 in case of a failed prepare?
>
>
> > Hello!
> >
> > Is there a good portable solution for doing the following:
> >
> > 1. select something
> > 2. if prepare failed (e. g. the database tables don't exist)
> then goto 3.
> >      else goto 4.
> > 3. create database tables
> > 4. execute statement
> >
> > I run into the problem, that create tables fails because of open
> > transactions. Even disconnect fails in Step 3.
> >
> > "DBD::InterBase::db disconnect failed: Unsuccessful execution caused by
> > system er
> > ror that does not preclude successful execution of subsequent statements
> > -cannot disconnect database with open transactions (1 active)"
> >
> > I use connect with AutoCommit set to true.
> >
> > Would the following be a good portable solution for
> DBD::InterBase and other
> > database drivers, too???
> >
> >   $db->{AutoCommit} = 0;
> >   $st = $db->prepare($sql);
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   $st->execute();
> >   if (!$st) {
> >     print "error ...";
> >     $db->rollback();
> >     return;
> >   }
> >   ...
> >   $db->commit();
> >
> > With other DBD drivers, e. g. ODBC I had no problem with
> creating database
> > tables after a prepare failed, even if AutoCommit was set to
> true. There was
> > no error message about open transactions. I worry about database drivers
> > which don't support transactions.
> >
> > Any suggestions?
> >
> > Regards,
> >
> > Peter
> >
> >
> > _______________________________________________
> > Dbi-interbase-devel mailing list
> > Dbi...@li...
> > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> >
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

Re: [Dbi-interbase-devel] How to close open transactions with AutoCommit = 1 in case of a failed prepare?

[<pko...@me...>]

Hello Daniel,

thanks for your prompt help. No more sleepless nights :-) Great work!

Regards,

Peter

> -----Urspr=FCngliche Nachricht-----
> Von: dbi...@li...
> [mailto:dbi...@li...]Im Auftrag von
> Daniel Ritz
> Gesendet am: Dienstag, 31. Juli 2001 14:00
> An: dbi...@li...
> Betreff: Re: [Dbi-interbase-devel] How to close open transactions with
> AutoCommit =3D 1 in case of a failed prepare?
>
> hello peter
>
> yep, it's a bug! a failed prepare does no rollback or commit in
> AutoCommit mode and disconnect doesn't too, but they
> should...
> i'll fix this asap. for now there's a little workaround:
>
> set AutoCommit to 0 right before the disconnect: (this makes
> disconnect to do a commit)
>     $_db->{AutoCommit} =3D 0;
>
> with this added to line 87 in the test script -> the tables are
> created and altered.
>
> rgds
> -daniel
>
>
> ----- Original Message -----
> From: "Peter K=F6ller" <pko...@me...>
> To: <dbi...@li...>
> Sent: Tuesday, July 31, 2001 10:58 AM
> Subject: Re: [Dbi-interbase-devel] How to close open transactions
> with AutoCommit =3D 1 in case of a failed prepare?
>
>
> Hello Daniel,
>
> now I have isolated a test script which can reproduce the problem I
> mentioned yesterday.
>
> The problem seems to be the following:
>
> - prepare fails because of non-existing table
> - disconnect (because the user can select another database)
> - connect again to the same database because the user wants the same on=
e
> - create tables
> - ERROR!!!
>
> If the disconnect and connect are left out, then no ERROR occurs.
>
> I attached a test script derived from my perl program.
>
> Is this a bug?
>
> Peter
>
> > -----Urspr=FCngliche Nachricht-----
> > Von: dbi...@li...
> > [mailto:dbi...@li...]Im Auftrag vo=
n
> > Daniel Ritz
> > Gesendet am: Montag, 30. Juli 2001 19:11
> > An: dbi...@li...
> > Betreff: Re: [Dbi-interbase-devel] How to close open transactions wit=
h
> > AutoCommit =3D 1 in case of a failed prepare?
> >
> > hello
> >
> > i've created two scripts doing what you described. the first is with
> > AutoCommit =3D 0, the second with AutoCommit =3D 1.
> >
> > first: create an empty db with ib console.
> > - when you first run the script you see the error message for the
> > failed prepare,
> >   the table is cerated and some data is inserted into it.
> > - second run: display content of the table created before.
> >
> > you can find the scripts at http://ritz.dnsart.com/interbase
> >
> > one question: what version of dbd-interbase are you using?
> >
> >
> > rgds
> > -daniel
> >
> > ps: if you problem still exists and you're using an up to date
> > version of the dbd
> > then it's probaly a bug (a missing commit or something). in this
> > case please send me
> > a code example that causes the error. i then try to fix it...
> >
> > ----- Original Message -----
> > From: "Peter K=F6ller" <pko...@me...>
> > To: <dbi...@li...>
> > Sent: Monday, July 30, 2001 11:32 AM
> > Subject: [Dbi-interbase-devel] How to close open transactions
> > with AutoCommit =3D 1 in case of a failed prepare?
> >
> >
> > > Hello!
> > >
> > > Is there a good portable solution for doing the following:
> > >
> > > 1. select something
> > > 2. if prepare failed (e. g. the database tables don't exist)
> > then goto 3.
> > >      else goto 4.
> > > 3. create database tables
> > > 4. execute statement
> > >
> > > I run into the problem, that create tables fails because of open
> > > transactions. Even disconnect fails in Step 3.
> > >
> > > "DBD::InterBase::db disconnect failed: Unsuccessful execution
> caused by
> > > system er
> > > ror that does not preclude successful execution of subsequent
> statements
> > > -cannot disconnect database with open transactions (1 active)"
> > >
> > > I use connect with AutoCommit set to true.
> > >
> > > Would the following be a good portable solution for
> > DBD::InterBase and other
> > > database drivers, too???
> > >
> > >   $db->{AutoCommit} =3D 0;
> > >   $st =3D $db->prepare($sql);
> > >   if (!$st) {
> > >     print "error ...";
> > >     $db->rollback();
> > >     return;
> > >   }
> > >   $st->execute();
> > >   if (!$st) {
> > >     print "error ...";
> > >     $db->rollback();
> > >     return;
> > >   }
> > >   ...
> > >   $db->commit();
> > >
> > > With other DBD drivers, e. g. ODBC I had no problem with
> > creating database
> > > tables after a prepare failed, even if AutoCommit was set to
> > true. There was
> > > no error message about open transactions. I worry about
> database drivers
> > > which don't support transactions.
> > >
> > > Any suggestions?
> > >
> > > Regards,
> > >
> > > Peter
> > >
> > >
> > > _______________________________________________
> > > Dbi-interbase-devel mailing list
> > > Dbi...@li...
> > > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> > >
> >
> >
> > _______________________________________________
> > Dbi-interbase-devel mailing list
> > Dbi...@li...
> > http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> >
>
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>