On Tue, Oct 19, 1999 at 12:36:30PM +0100, Matt Sergeant wrote:
> On Mon, 18 Oct 1999, Tim Bunce wrote:
> > >
> > > I've never looked at prepare_cached() for DBD::Sybase, and Matt tried
> > > it out and it appeared not to work.
> >
> > "appeared not to work" isn't much more specific :-)
>
> Sorry, but I don't have enough tuits to tie it down exactly. As Michael
> said it's a case of trying to cache things on one open connection while
> doing other things on the same connection - Sybase doesn't like that (and
> neither does MSSQL I think). However I wrote some code that does work
> (needs to be adapted as some connection info is specific to my library):
>
>
> sub prepare_cached {
> my $self = shift;
> my $statement = shift;
> my $dbh;
> if (defined $self->{cached_sth}->{$statement}) {
> warn "prepare_cached: sth found\n";
> $dbh = $self->{cached_sth}->{$statement}->{dbh};
> if ($dbh->ping) {
> my $sth = $self->{cached_sth}->{$statement}->{sth};
> $sth->fetchall_arrayref if $sth->{Active}; # Fetch any rows
>left
> return $sth;
> }
> }
>
> # Otherwise create connection and statement handle
> my $connectstring = "dbi:" . $self->{dbi_driver};
> $connectstring .= ":$self->{dbi_extra}" if exists $self->{dbi_extra};
> $dbh = DBI->connect($connectstring, $self->{dbi_user}, $self->{dbi_passwd})
> or die "Can't connect to database with '$connectstring' : ",
>DBI->errstr;
> $dbh->trace(1) if $self->debug_level >= 2;
> my $sth = $dbh->prepare($statement);
> $self->{cached_sth}->{$statement}->{dbh} = $dbh;
> $self->{cached_sth}->{$statement}->{sth} = $sth;
> warn "prepare_cached: creating new sth\n";
>
> return $sth;
> }
>
> This is part of my module, not DBD::Sybase, so it's called as:
>
> my $sth = $Config->prepare_cached("select * from whatever");
>
> and $Config contains the connection info - I don't know if you can do that
> with DBI (retrieve the connection string) as I've never hacked DBI at that
> level before, but it should be possible. As you can see a cached statement
> uses a completely new connection to the database. Something that might not
> be too nice for some people.
If the ``$sth->fetchall_arrayref if $sth->{Active};'' logic was
added to the DBI's prepare_cached, wouldn't that be enough for you?
(Assuming you call prepare_cached for the first time for each
statement when there's no active statement handle.)
Anyway, I thought DBD::Sybase now has internal logic to create new
sessions transparently if needed.
Tim.