dbi-interbase-devel — Development list for DBD::InterBase

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?

[<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?

[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
>

[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

[Dbi-interbase-devel] Re: Quoted Identifiers don't work?

[Adam C. <Ada...@St...>]

As Daniel noted use a version of Interbase that supports dialect 3 (IB or
Firebird 6), also make sure you specify it when connecting. As in Daniel's
example.

Cheers
Adam

<pko...@me...> wrote

> I quote my identifiers so that they don't conflict with InterBase key
words
> or data types. The following doesn't work with InterBase, although it
works
> if entered through the InterBase console. The Database/View Metadata
command
> shows quoted identifiers, too.

<dan...@gm...> then wrote

> hmm.... it works for me! what version of interbase do you have?
> older versions of interbase doesn't support quoted identifiers. the view
metadata window in ibconsle
> generate the code itself from metadata (it is not generated by interbase)
and does quote all identifiers.

Re: [Dbi-interbase-devel] Quoted Identifiers don't work?

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

Hello Daniel,

yes, that works fine. It seems that ib_dialect=3D3 makes the difference. =
Sorry
for my stupid problem. Next time I have a closer look at the POD of
DBD::InterBase.

Thank you,

Peter

> -----Urspr=FCngliche Nachricht-----
> Von: dbi...@li...
> [mailto:dbi...@li...]Im Auftrag von
> Daniel Ritz
> Gesendet am: Sonntag, 29. Juli 2001 16:11
> An: dbi...@li...
> Betreff: Re: [Dbi-interbase-devel] Quoted Identifiers don't work?
>
> hello
>
> hmm.... it works for me! what version of interbase do you have?
> older versions of interbase doesn't support quoted identifiers.
> the view metadata window in ibconsle
> generate the code itself from metadata (it is not generated by
> interbase) and does quote all identifiers.
>
> the following code works for me using interbase 6.0,
> dbd-interbase 0.28.x, perl 5.6.1 on win2k / linux.
>
> regards
> -daniel
>
> the code:
> -----------------------------------------
> #!/usr/bin/perl
> use strict;
> use DBI;
>
> my $dbh =3D DBI->connect("DBI:InterBase:db=3Dc:\\temp\\test.gdb;ib_dial=
ect=3D3",
>                        'sysdba', 'masterkey');
>
> $dbh->do(qq{
> CREATE TABLE "ITEMS" (
>  "ID"      INTEGER     NOT NULL,
>  "TITLE"   VARCHAR(45) NOT NULL,
>  "INTEGER" INTEGER     NOT NULL,
>  "ITEM_ID" VARCHAR(15) NOT NULL,
>  CONSTRAINT "PKEY_ITEMS_ID" PRIMARY KEY ("ID")
> )});
>
> $dbh->disconnect
> ----------------------------------------------
>
> ----- Original Message -----
> From: "Peter K=F6ller" <pko...@me...>
> To: <dbi...@li...>
> Sent: Sunday, July 29, 2001 12:26 PM
> Subject: [Dbi-interbase-devel] Quoted Identifiers don't work?
>
>
> > Hello,
> >
> > I quote my identifiers so that they don't conflict with
> InterBase key words
> > or data types. The following doesn't work with InterBase,
> although it works
> > if entered through the InterBase console. The Database/View
> Metadata command
> > shows quoted identifiers, too.
> >
> >   $db->do(qq{
> > CREATE TABLE "ITEMS" (
> >  "ID"                       INTEGER     NOT NULL,
> >  "TITLE"     VARCHAR(45) NOT NULL,
> >  "ITEM_ID"             VARCHAR(15) NOT NULL,
> >  CONSTRAINT "PKEY_ITEMS_ID" PRIMARY KEY ("ID")
> > )})
> >
> > Is there any workaround?
> >
> >
> > Thanks, in advance,
> >
> > 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] Quoted Identifiers don't work?

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

hello

hmm.... it works for me! what version of interbase do you have?
older versions of interbase doesn't support quoted identifiers. the view metadata window in ibconsle
generate the code itself from metadata (it is not generated by interbase) and does quote all identifiers.

the following code works for me using interbase 6.0, dbd-interbase 0.28.x, perl 5.6.1 on win2k / linux.

regards
-daniel

the code:
-----------------------------------------
#!/usr/bin/perl
use strict;
use DBI;

my $dbh = DBI->connect("DBI:InterBase:db=c:\\temp\\test.gdb;ib_dialect=3",
                       'sysdba', 'masterkey');

$dbh->do(qq{
CREATE TABLE "ITEMS" (
 "ID"      INTEGER     NOT NULL,
 "TITLE"   VARCHAR(45) NOT NULL,
 "INTEGER" INTEGER     NOT NULL,
 "ITEM_ID" VARCHAR(15) NOT NULL,
 CONSTRAINT "PKEY_ITEMS_ID" PRIMARY KEY ("ID")
)});

$dbh->disconnect
----------------------------------------------

----- Original Message -----
From: "Peter Köller" <pko...@me...>
To: <dbi...@li...>
Sent: Sunday, July 29, 2001 12:26 PM
Subject: [Dbi-interbase-devel] Quoted Identifiers don't work?


> Hello,
>
> I quote my identifiers so that they don't conflict with InterBase key words
> or data types. The following doesn't work with InterBase, although it works
> if entered through the InterBase console. The Database/View Metadata command
> shows quoted identifiers, too.
>
>   $db->do(qq{
> CREATE TABLE "ITEMS" (
>  "ID"                       INTEGER     NOT NULL,
>  "TITLE"     VARCHAR(45) NOT NULL,
>  "ITEM_ID"             VARCHAR(15) NOT NULL,
>  CONSTRAINT "PKEY_ITEMS_ID" PRIMARY KEY ("ID")
> )})
>
> Is there any workaround?
>
>
> Thanks, in advance,
>
> Peter
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

[Dbi-interbase-devel] Quoted Identifiers don't work?

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

Hello,

I quote my identifiers so that they don't conflict with InterBase key words
or data types. The following doesn't work with InterBase, although it works
if entered through the InterBase console. The Database/View Metadata command
shows quoted identifiers, too.

  $db->do(qq{
CREATE TABLE "ITEMS" (
 "ID"                       INTEGER     NOT NULL,
 "TITLE"		    VARCHAR(45) NOT NULL,
 "ITEM_ID"	            VARCHAR(15) NOT NULL,
 CONSTRAINT "PKEY_ITEMS_ID" PRIMARY KEY ("ID")
)})

Is there any workaround?


Thanks, in advance,

Peter

[Dbi-interbase-devel] bug in set_txn_param

[Mark D. A. <md...@di...>]

first off i'm so glad there is now the ability to set transaction parameters (as an archive
search will show; i've been campaigning for a while :)).

there is a missing comma in this documentation in InterBase.pm:
 $dbh->func( 
    -isolation_level => ['read_committed', 'record_version']
    'set_tx_param'
 );

also, this works for me:
 $dbh->func(
     -access_mode => 'read_write',
     -isolation_level => 'read_committed', 
     -lock_resolution => 'wait', 
     'set_tx_param');

but this change gets me the spurious error "Invalid -isolation_level value":
   -isolation_level => ['read_committed'],

and this change gets me a perl crash:
  -isolation_level => ['read_committed', 'record_version'],

looking at the code in InterBase.xs, it is not obvious to me what is wrong.
rc is not reset to zero in the outer loop, but that should not affect this case.
there also seems to be some unsafe assumptions about sv being a valid string,
but i'm not enough experienced with xs to say.

this is with 28.2 on win32

-mda

Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start

[Marco <pro...@sc...>]

hi Daniel,

Daniel Ritz wrote:
> > i'll try to solve the problem soon...
> soon means now!
> 
> check out the new 0.28.2 release on sourceforge. binary ppm for windows follows soon.
> http://prdownloads.sourceforge.net/dbi-interbase/DBD-InterBase-0.28.2.tar.gz

genius! that solved my problem!

thanx a lot,
	Marco

Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start

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

and now there's a binary ppm, compiled with bcc
and activeperl build 628. tested with build 623 too.

http://prdownloads.sourceforge.net/dbi-interbase/DBD-InterBase-0.28.2.zip


enjoy!

----- Original Message ----- 
From: "Daniel Ritz" <dan...@gm...>
To: <dbi...@li...>
Sent: Monday, July 16, 2001 11:58 PM
Subject: Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start


> hello again
> 
> > i'll try to solve the problem soon...
> soon means now!
> 
> check out the new 0.28.2 release on sourceforge. binary ppm for windows follows soon.
> http://prdownloads.sourceforge.net/dbi-interbase/DBD-InterBase-0.28.2.tar.gz
> 
> 
> regards
> -daniel
> 
> dbd-interbase porter

Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start

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

hello again

> i'll try to solve the problem soon...
soon means now!

check out the new 0.28.2 release on sourceforge. binary ppm for windows follows soon.
http://prdownloads.sourceforge.net/dbi-interbase/DBD-InterBase-0.28.2.tar.gz


regards
-daniel

dbd-interbase porter


----- Original Message -----
From: "Daniel Ritz" <dan...@gm...>
To: <dbi...@li...>
Sent: Monday, July 16, 2001 10:25 PM
Subject: Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start


> ok, i can see the problem. the problem is, that the dbd forgots to start a new
> transaction after a commit, followed by an execute w/o a prepare between
> commit and execute. but in autocommit mode it works.
> i'll try to solve the problem soon...
>
> regards
> -daniel
>
>
> ps for marco:
> wieso hast du autocommit auf off wenn du ja nach einem insert gleich ein commit machsts?
> versuch doch mal autocommit auf on zu setzen und den commit wegzulassen..
> eine andere methode wäre autocommit = off und do() zu verwenden. dann kannst du
> zwar nicht mit bind_param arbeiten, aber mit $dbh->quote kannst du die werte quoten und
> direkt in den string einsetzen...
>
> ----- Original Message -----
> From: "Marco Schlünß" <pri...@sc...>
> To: <dbi...@li...>
> Sent: Monday, July 16, 2001 8:08 PM
> Subject: Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start
>
>
> > hi Daniel,
> >
> > > i can't reproduce the error. i made a test database and a test script doing the same
> > > thing as described. it works without any problem. it uses two database connections
> > > and copies the content form one table to another. i tested it with two connections
> > > to the same database and with connections to two different databases.
> >
> > i use the once prepared statement serveral times for inserting data. after each insert i
> > do commit, the code worked fine with version 0.21, but from 0.27 on i get the following:
> >
> > DBD::InterBase::st execute failed: Unsuccessful execution caused by system error that d
> > oes not preclude successful execution of subsequent statements
> > -Dynamic SQL Error
> > -SQL error code = -901
> > -invalid transaction handle (expecting explicit transaction start)
> > Could not update last update of groups of server at /mnt/serverspace/prog/news/store_gr
> >
> > > you can find the test database and the test script at:
> > > http://ritz.dnsart.com/interbase/
> >
> > thank you for your help, but i could not see anything, that i would have done wrong?
> > but here's the code pasted together (it's spread over serval modules), perhaps you can see
> > something:
> >
> > my $db_connect =
> > "dbi:InterBase:database=/var/interbase/tndb.gdb;ib_dialect=3;ib_charset=ISO8859_1";
> > my $db_user = "NEWS_S";
> > my $db_passwd = "NEWS_S";
> > print "Try to connect.." if ($debug);
> > if (!defined($sel_nws_dbh))
> > {
> > # database-handle just for selects
> > print "sel->" if ($debug);
> > $sel_nws_dbh = DBI->connect($db_connect, $db_user, $db_passwd, {RaiseError=>0}) or die
> > $DBI::errstr;
> > print "OK.." if ($debug);
> > }
> >
> > if (!defined($mod_nws_dbh))
> > {
> > # database-handle just for modifications
> > print "mod->" if ($debug);
> > $mod_nws_dbh = DBI->connect($db_connect, $db_user, $db_passwd,
> > {AutoCommit=>0,RaiseError=>0}) or die "connect:\n".$DBI::errstr;
> > print "OK.." if ($debug);
> > }
> > print "..I'am connected\n" if ($debug);
> >
> > sub sth_upd_server_last_upd
> > {
> > if (!defined($upd_server_last_upd))
> > {
> > my $sql_upd_server_last_upd = <<EOF;
> > update nws_server
> > set nws_server_last_grp_upd = ?
> > where nws_server_idx = ?
> > EOF
> > $upd_server_last_upd = $mod_nws_dbh->prepare( $sql_upd_server_last_upd) or
> > dbherror( $mod_nws_dbh, "preparing upd_server_last_upd");
> > }
> > return $upd_server_last_upd;
> > }
> >
> > < here comes now serveral other statements, that get prepared in the same way as
> > $upd_server_last_upd >
> > my $upd_server_last_upd=sth_upd_server_last_upd();
> >
> > < now the program is ready and waiting for data, this can take time, so i have: >
> >
> > local $SIG{ALRM} = sub { $sel_nws_dbh->ping(); $mod_nws_dbh->ping(); alarm
> > $cfg_time_for_db_ping };
> >
> > < because Interbase would cut the connection otherwise >
> >
> > < finally, then the programm exits, it trys to increase a timestamp-field, and there it
> > pops up the error >
> >
> > $upd_server_last_upd->bind_param( 1, strftime( "%Y-%m-%d %H:%M:%S", localtime(
> > $start_time-60)));
> > $upd_server_last_upd->bind_param( 2, $nws_server_idx);
> > $upd_server_last_upd->execute() or die "Could not update last update of groups of server";
> > if ($use_commit)
> > {
> > $mod_nws_dbh->commit();
> > }
> >
> >
> > this is not the only place, i get this error, there are serveral others. i could life with
> > a workaround, if there's one, because it's my newsdatabase, i really looking forward to
> > get it running again...
> >
> > cu, Marco
> > --
> > Marco Schluenss            pri...@sc...
> > S-H-K                      www.schluenss.com
> > Spinnerstr. 35             Tel 0531/576450
> > 38114 Braunschweig
> >
> > _______________________________________________
> > 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] 901 / expecting explicit transaction start

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

ok, i can see the problem. the problem is, that the dbd forgots to start a new
transaction after a commit, followed by an execute w/o a prepare between
commit and execute. but in autocommit mode it works.
i'll try to solve the problem soon...

regards
-daniel


ps for marco:
wieso hast du autocommit auf off wenn du ja nach einem insert gleich ein commit machsts?
versuch doch mal autocommit auf on zu setzen und den commit wegzulassen..
eine andere methode wäre autocommit = off und do() zu verwenden. dann kannst du
zwar nicht mit bind_param arbeiten, aber mit $dbh->quote kannst du die werte quoten und
direkt in den string einsetzen...

----- Original Message -----
From: "Marco Schlünß" <pri...@sc...>
To: <dbi...@li...>
Sent: Monday, July 16, 2001 8:08 PM
Subject: Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start


> hi Daniel,
>
> > i can't reproduce the error. i made a test database and a test script doing the same
> > thing as described. it works without any problem. it uses two database connections
> > and copies the content form one table to another. i tested it with two connections
> > to the same database and with connections to two different databases.
>
> i use the once prepared statement serveral times for inserting data. after each insert i
> do commit, the code worked fine with version 0.21, but from 0.27 on i get the following:
>
> DBD::InterBase::st execute failed: Unsuccessful execution caused by system error that d
> oes not preclude successful execution of subsequent statements
> -Dynamic SQL Error
> -SQL error code = -901
> -invalid transaction handle (expecting explicit transaction start)
> Could not update last update of groups of server at /mnt/serverspace/prog/news/store_gr
>
> > you can find the test database and the test script at:
> > http://ritz.dnsart.com/interbase/
>
> thank you for your help, but i could not see anything, that i would have done wrong?
> but here's the code pasted together (it's spread over serval modules), perhaps you can see
> something:
>
> my $db_connect =
> "dbi:InterBase:database=/var/interbase/tndb.gdb;ib_dialect=3;ib_charset=ISO8859_1";
> my $db_user = "NEWS_S";
> my $db_passwd = "NEWS_S";
> print "Try to connect.." if ($debug);
> if (!defined($sel_nws_dbh))
> {
> # database-handle just for selects
> print "sel->" if ($debug);
> $sel_nws_dbh = DBI->connect($db_connect, $db_user, $db_passwd, {RaiseError=>0}) or die
> $DBI::errstr;
> print "OK.." if ($debug);
> }
>
> if (!defined($mod_nws_dbh))
> {
> # database-handle just for modifications
> print "mod->" if ($debug);
> $mod_nws_dbh = DBI->connect($db_connect, $db_user, $db_passwd,
> {AutoCommit=>0,RaiseError=>0}) or die "connect:\n".$DBI::errstr;
> print "OK.." if ($debug);
> }
> print "..I'am connected\n" if ($debug);
>
> sub sth_upd_server_last_upd
> {
> if (!defined($upd_server_last_upd))
> {
> my $sql_upd_server_last_upd = <<EOF;
> update nws_server
> set nws_server_last_grp_upd = ?
> where nws_server_idx = ?
> EOF
> $upd_server_last_upd = $mod_nws_dbh->prepare( $sql_upd_server_last_upd) or
> dbherror( $mod_nws_dbh, "preparing upd_server_last_upd");
> }
> return $upd_server_last_upd;
> }
>
> < here comes now serveral other statements, that get prepared in the same way as
> $upd_server_last_upd >
> my $upd_server_last_upd=sth_upd_server_last_upd();
>
> < now the program is ready and waiting for data, this can take time, so i have: >
>
> local $SIG{ALRM} = sub { $sel_nws_dbh->ping(); $mod_nws_dbh->ping(); alarm
> $cfg_time_for_db_ping };
>
> < because Interbase would cut the connection otherwise >
>
> < finally, then the programm exits, it trys to increase a timestamp-field, and there it
> pops up the error >
>
> $upd_server_last_upd->bind_param( 1, strftime( "%Y-%m-%d %H:%M:%S", localtime(
> $start_time-60)));
> $upd_server_last_upd->bind_param( 2, $nws_server_idx);
> $upd_server_last_upd->execute() or die "Could not update last update of groups of server";
> if ($use_commit)
> {
> $mod_nws_dbh->commit();
> }
>
>
> this is not the only place, i get this error, there are serveral others. i could life with
> a workaround, if there's one, because it's my newsdatabase, i really looking forward to
> get it running again...
>
> cu, Marco
> --
> Marco Schluenss            pri...@sc...
> S-H-K                      www.schluenss.com
> Spinnerstr. 35             Tel 0531/576450
> 38114 Braunschweig
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start

[Marco <pri...@sc...>]

hi Daniel,

> i can't reproduce the error. i made a test database and a test script doing the same
> thing as described. it works without any problem. it uses two database connections
> and copies the content form one table to another. i tested it with two connections
> to the same database and with connections to two different databases.

i use the once prepared statement serveral times for inserting data. after each insert i
do commit, the code worked fine with version 0.21, but from 0.27 on i get the following:

DBD::InterBase::st execute failed: Unsuccessful execution caused by system error that d
oes not preclude successful execution of subsequent statements
-Dynamic SQL Error
-SQL error code = -901
-invalid transaction handle (expecting explicit transaction start)
Could not update last update of groups of server at /mnt/serverspace/prog/news/store_gr

> you can find the test database and the test script at:
> http://ritz.dnsart.com/interbase/

thank you for your help, but i could not see anything, that i would have done wrong?
but here's the code pasted together (it's spread over serval modules), perhaps you can see
something:

my $db_connect =
"dbi:InterBase:database=/var/interbase/tndb.gdb;ib_dialect=3;ib_charset=ISO8859_1"; 
my $db_user = "NEWS_S";
my $db_passwd = "NEWS_S";
print "Try to connect.." if ($debug);
if (!defined($sel_nws_dbh))
{
	# database-handle just for selects
	print "sel->" if ($debug);
	$sel_nws_dbh = DBI->connect($db_connect, $db_user, $db_passwd, {RaiseError=>0}) or die
$DBI::errstr;
	print "OK.." if ($debug);
}
	
if (!defined($mod_nws_dbh))
{
	# database-handle just for modifications
	print "mod->" if ($debug);
	$mod_nws_dbh = DBI->connect($db_connect, $db_user, $db_passwd,
{AutoCommit=>0,RaiseError=>0}) or die "connect:\n".$DBI::errstr;
	print "OK.." if ($debug);
}
print "..I'am connected\n" if ($debug);

sub sth_upd_server_last_upd
{
	if (!defined($upd_server_last_upd))
	{
		my $sql_upd_server_last_upd = <<EOF;
		update nws_server
		set nws_server_last_grp_upd = ?
		where nws_server_idx = ?
EOF
		$upd_server_last_upd = $mod_nws_dbh->prepare( $sql_upd_server_last_upd) or 
			dbherror( $mod_nws_dbh, "preparing upd_server_last_upd");
	}
	return $upd_server_last_upd;
}

< here comes now serveral other statements, that get prepared in the same way as
$upd_server_last_upd >
my $upd_server_last_upd=sth_upd_server_last_upd();

< now the program is ready and waiting for data, this can take time, so i have: >

local $SIG{ALRM} = sub { $sel_nws_dbh->ping(); $mod_nws_dbh->ping(); alarm
$cfg_time_for_db_ping };

< because Interbase would cut the connection otherwise >

< finally, then the programm exits, it trys to increase a timestamp-field, and there it
pops up the error >

$upd_server_last_upd->bind_param( 1, strftime( "%Y-%m-%d %H:%M:%S", localtime(
$start_time-60)));
$upd_server_last_upd->bind_param( 2, $nws_server_idx);
$upd_server_last_upd->execute() or die "Could not update last update of groups of server";
if ($use_commit)
{
	$mod_nws_dbh->commit();
}


this is not the only place, i get this error, there are serveral others. i could life with
a workaround, if there's one, because it's my newsdatabase, i really looking forward to
get it running again...

cu, Marco
-- 
Marco Schluenss            pri...@sc...
S-H-K                      www.schluenss.com
Spinnerstr. 35             Tel 0531/576450
38114 Braunschweig

Re: [Dbi-interbase-devel] 901 / expecting explicit transaction start

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

hello marco

i can't reproduce the error. i made a test database and a test script doing the same
thing as described. it works without any problem. it uses two database connections
and copies the content form one table to another. i tested it with two connections
to the same database and with connections to two different databases.

you can find the test database and the test script at:
http://ritz.dnsart.com/interbase/

(i tested it with interbase 6.0 / perl 5.6 / dbd-interbase 0.28
 on windows 2000 and on linux 2.2.19)

may be the script is an answer to you. if not, send me the part of your script that fails
and i try to find the error.


regards
-daniel

dbd-interbase porter


----- Original Message -----
From: "Marco Schlünß" <pri...@sc...>
To: <dbi...@li...>
Sent: Monday, July 16, 2001 12:12 PM
Subject: [Dbi-interbase-devel] 901 / expecting explicit transaction start


> Hi,
>
> i just upgraded my DBD::InterBase-driver from version 0.21 to 0.28 and i get this error:
>
> DBD::InterBase::st execute failed: Unsuccessful execution caused by system error that d
> oes not preclude successful execution of subsequent statements
> -Dynamic SQL Error
> -SQL error code = -901
> -invalid transaction handle (expecting explicit transaction start)
>
> the script, that produces this error, uses 2 db-handles, one for reading data and one for
> updating the database.
> AutoCommit is turned on for the reading one, and turned of for the writing one.
> SQL-statements are prepared upon script-start.
>
> but how do i explicit start a transaction? due to dbd does not support transaction, i
> think, i have to use a sideeffect of the existing methods? should i use a commit just
> before starting to bind and execute?
>
> --
> Marco Schluenss            pro...@sc...
> Spinnerstr. 35             Tel D-0531/576450
> 38114 Braunschweig
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

[Dbi-interbase-devel] 901 / expecting explicit transaction start

[Marco <pri...@sc...>]

Hi,

i just upgraded my DBD::InterBase-driver from version 0.21 to 0.28 and i get this error:

DBD::InterBase::st execute failed: Unsuccessful execution caused by system error that d
oes not preclude successful execution of subsequent statements
-Dynamic SQL Error
-SQL error code = -901
-invalid transaction handle (expecting explicit transaction start)

the script, that produces this error, uses 2 db-handles, one for reading data and one for
updating the database.
AutoCommit is turned on for the reading one, and turned of for the writing one.
SQL-statements are prepared upon script-start.

but how do i explicit start a transaction? due to dbd does not support transaction, i
think, i have to use a sideeffect of the existing methods? should i use a commit just
before starting to bind and execute?

-- 
Marco Schluenss            pro...@sc...
Spinnerstr. 35             Tel D-0531/576450
38114 Braunschweig

Re[4]: [Dbi-interbase-devel] (no subject)

[Andrew L. <al...@ba...>]

Hello Daniel,

Friday, July 13, 2001, 8:42:25 PM, you wrote:

DR> i made a new release (0.28.1) that fixes the compile problem with build 626+ / BCC
  build 628 also passed the tests. thank you. :)


-- 
Best regards,
 Andrew                            mailto:al...@ba...

Re: Re[2]: [Dbi-interbase-devel] (no subject)

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

hello again

check out the file list on sourceforge. 
i made a new release (0.28.1) that fixes the compile 
problem with build 626+ / BCC

rgds
-daniel


----- Original Message ----- 
From: "Andrew Luckovenko" <al...@ba...>
To: "Daniel Ritz" <dbi...@li...>
Sent: Thursday, July 12, 2001 10:06 PM
Subject: Re[2]: [Dbi-interbase-devel] (no subject)


> Hello Daniel,
> 
> Thursday, July 12, 2001, 7:21:57 PM, you wrote:
> 
> DR> the problem is that activestate changed some defines/macros in the header files.
> DR> i was not yet able to find the problem, but the problem are definitifley
> DR> defines/macros in perl.h or in a header included from perl.h.
>   it compiles ok with Visual C++ 6.0, but fails some tests... what's the
>   difference between VC and BCC?
> 
> DR> the only answer i have at this time is to use the binary distro (ppm) which is compiled
> DR> using build 623, confirmed to work with build 626 and i think 628 too (not tested).
>   i could test it if you will send me ready-to-make-test directory
>   containing already compiled under 623 files.
> 
> DR> if you are able to find the problem, please let me know.
>   ok. thank you.
>   
> 
> -- 
> Best regards,
>  Andrew                            mailto:al...@ba...
> 
> 
> 
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
> 

Re[2]: [Dbi-interbase-devel] (no subject)

[Andrew L. <al...@ba...>]

Hello Daniel,

Thursday, July 12, 2001, 7:21:57 PM, you wrote:

DR> the problem is that activestate changed some defines/macros in the header files.
DR> i was not yet able to find the problem, but the problem are definitifley
DR> defines/macros in perl.h or in a header included from perl.h.
  it compiles ok with Visual C++ 6.0, but fails some tests... what's the
  difference between VC and BCC?

DR> the only answer i have at this time is to use the binary distro (ppm) which is compiled
DR> using build 623, confirmed to work with build 626 and i think 628 too (not tested).
  i could test it if you will send me ready-to-make-test directory
  containing already compiled under 623 files.

DR> if you are able to find the problem, please let me know.
  ok. thank you.
  

-- 
Best regards,
 Andrew                            mailto:al...@ba...

Re: [Dbi-interbase-devel] (no subject)

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

hello

the problem is that activestate changed some defines/macros in the header files.
i was not yet able to find the problem, but the problem are definitifley
defines/macros in perl.h or in a header included from perl.h.

i have this problem since build 626 so i went back to 623 where all works fine.
the only answer i have at this time is to use the binary distro (ppm) which is compiled
using build 623, confirmed to work with build 626 and i think 628 too (not tested).

if you are able to find the problem, please let me know.


regards
-daniel
dan...@gm...


ps: build 623 is still on ftp://ftp.activestate.com



----- Original Message -----
From: "Andrew Luckovenko" <al...@ba...>
To: <Dbi...@li...>
Sent: Thursday, July 12, 2001 4:42 PM
Subject: [Dbi-interbase-devel] (no subject)


> Hello Dbi-interbase-devel,
>
>   I have a problem installing DBD::Interbase under ActivePerl
>   5.6.1.628. There are messages like
>
>
bcc32.exe -c -I"d:\lang\ib\include" -I"D:/perl/site/lib/auto/DBI" -I"d:\lang\bcc55\include" -a4 -DWIN32 -DNO_STRICT -O2 
          -DVERSION=\"0.28\"    -DXS_VERSION=\"0.28\"  -ID:\perl\lib\CORE  InterBase.c
> Borland C++ 5.5.1 for Win32 Copyright (c) 1993, 2000 Borland
> InterBase.c:
> Warning W8065 InterBase.xsi 36: Call to function 'get_dbistate' with no prototype in function
XS_DBD__InterBase__dr_discon_all_
>
>   while dmake'ing. PatchBCC has been applied. Whats wrong?
>
> --
> Best regards,
>  Andrew                          mailto:al...@ba...
>
>
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>

[Dbi-interbase-devel] (no subject)

[Andrew L. <al...@ba...>]

Hello Dbi-interbase-devel,

  I have a problem installing DBD::Interbase under ActivePerl
  5.6.1.628. There are messages like
  
bcc32.exe -c -I"d:\lang\ib\include" -I"D:/perl/site/lib/auto/DBI" -I"d:\lang\bcc55\include" -a4 -DWIN32 -DNO_STRICT -O2           -DVERSION=\"0.28\"    -DXS_VERSION=\"0.28\"  -ID:\perl\lib\CORE  InterBase.c
Borland C++ 5.5.1 for Win32 Copyright (c) 1993, 2000 Borland
InterBase.c:
Warning W8065 InterBase.xsi 36: Call to function 'get_dbistate' with no prototype in function XS_DBD__InterBase__dr_discon_all_

  while dmake'ing. PatchBCC has been applied. Whats wrong?

-- 
Best regards,
 Andrew                          mailto:al...@ba...

[Dbi-interbase-devel] Question about select , update or insert...

[swade <sw...@di...>]

Hello, I'm new here...I'm sure this question has been asked a million =
times but I've
recently switched over to DBI:Interbase from DBI:Oracle...

reading over some of tims online docs, I saw something like this
aka update...if not there, then insert.=20

$upd=3D$dbh->prepare("update test set COUNT=3DCOUNT+1 where =
account_id=3D?");
$ins=3D$dbh->prepare("insert into test (account_id,COUNT) values =
(?,?)");
$rows=3D$upd->execute($account_id);
print "ROWS: $rows\n";
$ins->execute($account_id,1) if $rows < 0;
$dbh->disconnect;

Reading the dbi interbase docs, and running the above, I've surmised =
that $rows will always return a -1.
Is there another technique to accomplish something similiar, or do you =
have to:

select X from X where X=3D?
yada....return to $myvar
if ($myvar eq "") {
    insert....
} else {
    update....
}

Thanks for any pointers....
shawn

Re: [Dbi-interbase-devel] PPM package for DBD::InterBase

[Mike K. <mi...@ma...>]

Ed, Daniel and Peter

I can't tell you how happy this makes me :-)))

At last I can do the same stuff on my desktop that my Linux server can do !

Mike

At 08:06 AM 26/06/2001 -0400, you wrote:
>Hi all,
>
>it's available now from:
>http://download.sourceforge.net/dbi-interbase/DBD-InterBase-0.28.zip
>
>Thanks for Daniel and Peter who have made this release possible.
>
>rgds,
>Edwin.
>
>-- 
>PerlMonks home node: http://perlmonks.org/?node=pope
>CPAN home: http://search.cpan.org/search?author=edpratomo
>
>_______________________________________________
>Dbi-interbase-devel mailing list
>Dbi...@li...
>http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel

[Dbi-interbase-devel] PPM package for DBD::InterBase

[Edwin P. <ed...@po...>]

Hi all,

it's available now from:
http://download.sourceforge.net/dbi-interbase/DBD-InterBase-0.28.zip

Thanks for Daniel and Peter who have made this release possible.

rgds,
Edwin.

-- 
PerlMonks home node: http://perlmonks.org/?node=pope
CPAN home: http://search.cpan.org/search?author=edpratomo

Re: [Dbi-interbase-devel] problem on installing DBD::interbase(0.28)

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

hi

the problem is, that the borland make utility is not capable to work with the MakeMaker generated makefiles.
You have to type dmake or pmake instead of make. (you can find the download urls for these utils in the readme file)
with one of these utilities it should work.

i only had problems compiling the dbd with activeperl 5.6.1 (build 626). the compiler was unable to find some
definitions,
but i didn't have time to check why. i went back to build 623, because i needed perl running. if you are using build
626,
please let me know if you find the problem...

regards
-daniel


----- Original Message -----
From: "ychen56" <yc...@ne...>
To: <dbi...@li...>
Sent: Saturday, June 23, 2001 6:29 AM
Subject: [Dbi-interbase-devel] problem on installing DBD::interbase(0.28)


> Hi, People:
> I have problem to install DBD interbase driver under windows98. I am using
> activePerl.  When I ran "perl patchBCC", I had problem with the last rename
> statement. I thought this statement wanted to rename
> c:\perl\lib\core\perl56.lib to c:\perl\lib\core\perl56_coff.lib. I modified
> the source code, it could work fine. Then I ran "perl makefile.pl", it was
> okay.
> But when I ran "make", I got two errors,
> 1. error makefile line 129: colon expected
> 2. error makefile line 711 redefinition of target "Interbase.obj"
> I appreciate any of you can give me some help.
> Thanks
> Ye
>
>
> NetZero Platinum
> No Banner Ads and Unlimited Access
> Sign Up Today - Only $9.95 per month!
> http://www.netzero.net
>
> _______________________________________________
> Dbi-interbase-devel mailing list
> Dbi...@li...
> http://lists.sourceforge.net/lists/listinfo/dbi-interbase-devel
>