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.
Besides that, after all my work on this we can't use it since the version
of MSSQL we're using is limited to 5 connections. Bah! (I developed it on
Sybase but the product (not mod_perl related) ships on MSSQL's mini
version).
--
<Matt/>
Details: FastNet Software Ltd - XML, Perl, Databases.
Tagline: High Performance Web Solutions
Web Sites: http://come.to/fastnet http://sergeant.org
Available for Consultancy, Contracts and Training.
