dbi-interbase-devel

[Dbi-interbase-devel] DBD and Interbase functionality

[Chris W. <ch...@cw...>]

I'm trying to learn just enough about InterBase and DBD::InterBase to be
dangerous :-) I'm the author of SPOPS -- Simple Perl Object Persistence
with Security -- and would like to create an IB driver for SPOPS. If it
works fully, this also means that folks could use IB as the datasource
behind the OpenInteract application server.

This is fairly simple, boiling down to a few SQL questions -- how do you
select the current time? how do you generate unique key values? -- and
some DBD-functionality questions -- are the {TYPE} and {NAME} statement
handle attributes supported? does quote() take two arguments and use
them?

I have all that more or less done and things work great. However, I ran
into a problem trying to run multiple statements simultaneously on a
single database handle? (Test script and error message attached.) Is
this supported with DBD::InterBase? If so, do I need to do anything
special?

Thanks much,

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.

[Dbi-interbase-devel] DBD and Interbase functionality

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

> From: Chris Winters <ch...@cw...>
> Subject: [Dbi-interbase-devel] DBD and Interbase functionality

> I have all that more or less done and things work great. However, I ran
> into a problem trying to run multiple statements simultaneously on a
> single database handle? (Test script and error message attached.) Is
> this supported with DBD::InterBase? If so, do I need to do anything
> special?

Chris, I think that you are being bitten by an issue that is described in
the FAQ for the driver. See http://dbi.interbase.or.id/faq.html. In summary
Interbase keeps track of transaction context in the database handle. With
AutoCommit on (the default) the DBI behaviour is to call an implicit commit
upon certain events, one of those being a final fetch from a statement
handle. So your St1 hits it's final fetch and AutoCommit does it's thing but
unfortunately for your example (because the transaction context is pegged o
the dbh) this also commits St2 which hasn't done anything yet!

I believe that my rejigged test code (not tested) will solve this problem in
this simple case, however for other situations (like nested select
statements) the best solution is to manage your own transactions or
investigate the new ib_softcommit driver specific option.

> #!/usr/bin/perl
>
> use strict;
> use Data::Dumper qw( Dumper );
> use DBI;
>
> $Data::Dumper::Indent = 0;
>
> my $DSN    = 'DBI:InterBase:dbname=3Dcwtest.gdb';
> my $USER   = 'sysdba';
> my $PASS   = 'password';
> my $TABLE1 = 'foo';
> my $TABLE2 = 'spops_user';
>
> {
>     my $dbh =3D DBI->connect( $DSN, $USER, $PASS )
>                     || die "Cannot connect: $DBI::errstr";
>     $dbh->{RaiseError} =3D 1;
>     my $sth =3D $dbh->prepare( "select * from $TABLE1" );
>     $sth->execute;
>
>     while ( my $row =3D $sth->fetchrow_arrayref ) {
>         print "St1: ", Dumper( $row ), "\n";
>     }
>
>     my $sth2 =3D $dbh->prepare( "select * from $TABLE2" );
>     $sth2->execute;
>
>     while ( my $row =3D $sth2->fetchrow_arrayref ) {
>         print "St2: ", Dumper( $row ), "\n";
>     }
> }

Re: [Dbi-interbase-devel] DBD and Interbase functionality

[Chris W. <ch...@cw...>]

On Tue, 2002-04-30 at 16:02, Adam Clarke wrote:
> Chris, I think that you are being bitten by an issue that is described in
> the FAQ for the driver. See http://dbi.interbase.or.id/faq.html. In summary
> Interbase keeps track of transaction context in the database handle. With
> AutoCommit on (the default) the DBI behaviour is to call an implicit commit
> upon certain events, one of those being a final fetch from a statement
> handle. So your St1 hits it's final fetch and AutoCommit does it's thing but
> unfortunately for your example (because the transaction context is pegged o
> the dbh) this also commits St2 which hasn't done anything yet!

Ah, this makes sense. Thanks for the lucid explanation.

> I believe that my rejigged test code (not tested) will solve this problem in
> this simple case, however for other situations (like nested select
> statements) the best solution is to manage your own transactions or
> investigate the new ib_softcommit driver specific option.

The modified script should work, but only because it avoids the problem
-- which seems in this case the best solution :-)

AFAIK this only happens in one place, so I may see about modifying that
code.

Thanks much for the help.

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.

Re: [Dbi-interbase-devel] DBD and Interbase functionality

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

> > In summary
> > Interbase keeps track of transaction context in the database handle.

actually InterBase is just about the only RDBMS I know of that does not 
do this. It is one of the few whose C API allows for explicit manipulation
of multiple, independent, concurrent transaction contexts on a single database
connection.

But DBI after all these years still has no standardized transaction API.
Plus, no one ever writes DBD implementations from scratch :).
So DBD::InterBase ended up following the common pattern.

Even though the InterBase C api requires a transaction handle for both
prepare and execute, a prepared statement handle should certainly
be able to survive a commit (i.e. prepare and execute in different transactions),
or there wouldn't be much point to a prepare.

So there should be some way to make the perl code work unchanged.
But the isc_* C api is so messy i long ago gave up on understanding its subtleties....

> > I believe that my rejigged test code (not tested) will solve this problem in
> > this simple case, however for other situations (like nested select
> > statements) the best solution is to manage your own transactions or
> > investigate the new ib_softcommit driver specific option.

explicit commit should take care of it,
if you don't mind the extra code.

ib_softcommit changes semantics so it might not be a solution.
isc_commit_retaining widens transaction windows, which can result
in starvation in multi-user scenarios.
(i know this, because the first releases of DBD::InterBase *always*
used isc_commit_retaining, with no recourse from the perl side.)

> The modified script should work, but only because it avoids the problem
> -- which seems in this case the best solution :-)
> 
> AFAIK this only happens in one place, so I may see about modifying that
> code.

Using DBI's builtin prepare_cached() support might be easier, if you are open
to changing your code.
That way you needn't do explicit prepare calls at all.
Since the prepare cache for a particular statement won't be set until the first
time it is actually executed, you get the same call order as in your changed
code. See the source to DBI.pm for more info.

-mda

Re: [Dbi-interbase-devel] DBD and Interbase functionality

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

> This is fairly simple, boiling down to a few SQL questions -- how do you
> select the current time?

depends on your interbase version and your dialect.
See http://www.ibphoenix.com/ibp_60_sql_date_fs.html
I use current_timestamp for my needs.
btw, another time related factoid you might need to know is that the
default time format is  '%d/%m/%Y %H:%M:%S'

> how do you generate unique key values?

interbase uses the term "generator" for what oracle calls "sequences".

interbase does not have autoincrement columns as some rdbms's do.

in my own home-grown SPOPS :), here is the data structure i'm using
to declare how to manage interbase generators:

my %INTERBASE_GENERATOR_OPS = (
       inc_get_clause => 'GEN_ID(%s,1)', # name
       inc_get => 'SELECT GEN_ID(%s,1) from RDB$DATABASE', # name
       get => 'SELECT GEN_ID(%s,0) from RDB$DATABASE', # name
       set => 'SET GENERATOR %s TO %%s', # name, val (but only sub name initially)
       create => ['CREATE GENERATOR %s', 'SET GENERATOR %s TO 0'],
       destroy => 'DELETE FROM RDB$GENERATORS WHERE RDB$GENERATORS_NAME = \'%s\'',
       );

> I have all that more or less done and things work great. However, I ran
> into a problem trying to run multiple statements simultaneously on a
> single database handle? (Test script and error message attached.) Is
> this supported with DBD::InterBase? If so, do I need to do anything
> special?

you aren't the first to see this; try google terms: interbase 504 "unknown cursor"
I don't see a resolution even though it was reported in year 2000.

fyi i gave up on the DBI::InterBase driver last year (i just lurk on this list).
what i'm currently using is DBI:ODBC, calling the unixodbc driver manager, calling
the unixodbc interbase driver.
such an approach introduces an extra layer, but all the layers are more
mature than DBI::InterBase, and have more users.
all my mysterious bugs disappeared when i made the move.

-mda

Re: [Dbi-interbase-devel] DBD and Interbase functionality

[Chris W. <ch...@cw...>]

On Tue, 2002-04-30 at 01:08, Mark D. Anderson wrote:
> depends on your interbase version and your dialect.
> See http://www.ibphoenix.com/ibp_60_sql_date_fs.html
> I use current_timestamp for my needs.
> btw, another time related factoid you might need to know is that the
> default time format is  '%d/%m/%Y %H:%M:%S'

This is good to know, thanks.

> > how do you generate unique key values?
> 
> interbase uses the term "generator" for what oracle calls "sequences".

There seems to be one difference: you cannot get the "current" value
from a generator. For instance, from a sequence you can do:

 INSERT INTO foo ( id ) VALUES ( NEXTVAL( seq ) );
 SELECT CURRVAL( seq );

To insert the next ID into a table and get back the ID you just
inserted. Using a generator, you need to first select the next value
from the generator then stick it into the statement. This isn't
difficult, but it would seem to make it difficult to use the trigger
method to make this happen, since you have no way of getting the ID just
inserted.

> you aren't the first to see this; try google terms: interbase 504 "unknown cursor"
> I don't see a resolution even though it was reported in year 2000.
> 
> fyi i gave up on the DBI::InterBase driver last year (i just lurk on this list).
> what i'm currently using is DBI:ODBC, calling the unixodbc driver manager, calling
> the unixodbc interbase driver.
> such an approach introduces an extra layer, but all the layers are more
> mature than DBI::InterBase, and have more users.
> all my mysterious bugs disappeared when i made the move.

Also good to know. I'll probably just work around it -- IME ODBC
installs on Unix machines aren't as common as using the client libraries
directly.

Thanks!

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.

Re: [Dbi-interbase-devel] DBD and Interbase functionality

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

> There seems to be one difference: you cannot get the "current" value
> from a generator. For instance, from a sequence you can do:
> 
>  INSERT INTO foo ( id ) VALUES ( NEXTVAL( seq ) );
>  SELECT CURRVAL( seq );

Yes, and no, i think: you can do that also in interbase, but with both
oracle and interbase, that approach only works if you wrap it all in one transactional
context, for example in a stored procedure body.

To read the current value of an interbase generator
      SELECT GEN_ID(seq,0) from RDB$DATABASE
will get you the current value of the generator called "seq" 
(last i checked interbase does not support a select statement without a "from" clause).

In my own home-grown SPOPS, i work around the problem of a standards-based insert
not returning anything, by issuing one sql request to get a unique value of a generator,
and then just using that value in a later insert (in a separate transaction context).
generators are guaranteed (like sequences) to never repeat a value.
this approach has the downside of having two roundtrips, but it has the upside of working on
every rdbms.
it also has the characteristic of potentially skipping some values in the sequence, but i
don't care about that.

-mda

Re: [Dbi-interbase-devel] DBD and Interbase functionality

[Chris W. <ch...@cw...>]

On Tue, 2002-04-30 at 11:53, Mark D. Anderson wrote:
> > There seems to be one difference: you cannot get the "current" value
> > from a generator. For instance, from a sequence you can do:
> > 
> >  INSERT INTO foo ( id ) VALUES ( NEXTVAL( seq ) );
> >  SELECT CURRVAL( seq );
> 
> Yes, and no, i think: you can do that also in interbase, but with both
> oracle and interbase, that approach only works if you wrap it all in one transactional
> context, for example in a stored procedure body.

Or I assume in the context of a declared transaction:
BEGIN TRAN;
insert...
select currval...
commit;
 
> To read the current value of an interbase generator
>       SELECT GEN_ID(seq,0) from RDB$DATABASE
> will get you the current value of the generator called "seq" 
> (last i checked interbase does not support a select statement without a "from" clause).

Ah, of course! 

> In my own home-grown SPOPS, i work around the problem of a standards-based insert
> not returning anything, by issuing one sql request to get a unique value of a generator,
> and then just using that value in a later insert (in a separate transaction context).
> generators are guaranteed (like sequences) to never repeat a value.
> this approach has the downside of having two roundtrips, but it has the upside of working on
> every rdbms.

My approach also -- in almost every scenario you need two roundtrips:

 - Identity: Do insert, then retrieve "environment" variable (e.g.,
'@@identity')
 - Sequence A: Retrieve sequence value and use in SQL for insert
 - Sequence B: Do insert with SQL to insert next value automatically,
then retrieve current sequence value
 - Auto-increment: Client library contains 'last incrment created' value
after an insert so you do not need another roundtrip.

> it also has the characteristic of potentially skipping some values in the sequence, but i
> don't care about that.

Me neither, just that they're unique. Relying on other properties of
automatically generated keys is bound to get you into trouble at some
point.

Thanks for the information!

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.