perl-cache-users

[Perl-cache-users] Help with caching DBD::mysql statement handles ($sth)

[danny m. <dan...@gm...>]

Hi list,

I am using DBD::mysql to connect to my db. I'm trying to cache my
already-executed statement handle ($sth) so i can reuse it over and
over again.

My code looks like this:

my $cache = Cache::FileCache->new();

my $sth = $cache->get_object(md5_hex($sql));

if (not defined $sth){
  $sth = $dbh->prepare($sql);
  $sth->execute();
  $cache->set_object(md5_hex($sql),$sth);
}
while(my @r = $sth->fetchrow_array()) {....my loop.....}
.......the rest of my code......

And the error I get is:
Error: Can't store CODE items at P:/site/lib/Cache/CacheUtils.pm line 70.

Can someone give me an advise as to whether this is a right direction
I am going or how else can I accomplish such a mysql object caching.

Any comment would be appreciated.

Thanks,

Danny

Re: [Perl-cache-users] Help with caching DBD::mysql statement handles ($sth)

[DeWitt C. <de...@un...>]

Hi,

I didn't even know this list still existed!

The Cache::Cache library uses the Storable module for object
persistence.  While this library has been remarkably solid throughout
the years, the major limitation is that it can only persist data, not
live objects.  As such you would need to use a different means for
keeping a DB statement handle cached.

Are you seeing a major overhead in preparing statements?  I would
expect that even if you did, you would be able to simply pre-prepare
each statement when launching the app, or alternately, on first use.
Caching them to disk, or caching them for a set length of time
wouldn't seem to offer any advantages.

Hope this helps!

-DeWitt


On 9/12/06, danny miles <dan...@gm...> wrote:
> Hi list,
>
> I am using DBD::mysql to connect to my db. I'm trying to cache my
> already-executed statement handle ($sth) so i can reuse it over and
> over again.
>
> My code looks like this:
>
> my $cache = Cache::FileCache->new();
>
> my $sth = $cache->get_object(md5_hex($sql));
>
> if (not defined $sth){
>   $sth = $dbh->prepare($sql);
>   $sth->execute();
>   $cache->set_object(md5_hex($sql),$sth);
> }
> while(my @r = $sth->fetchrow_array()) {....my loop.....}
> .......the rest of my code......
>
> And the error I get is:
> Error: Can't store CODE items at P:/site/lib/Cache/CacheUtils.pm line 70.
>
> Can someone give me an advise as to whether this is a right direction
> I am going or how else can I accomplish such a mysql object caching.
>
> Any comment would be appreciated.
>
> Thanks,
>
> Danny
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Perl-cache-users mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perl-cache-users
>

Re: [Perl-cache-users] Help with caching DBD::mysql statement handles ($sth)

[danny m. <dan...@gm...>]

Just a follow up to my yesterday's mail, with the help of DeWitt I was
able to find the right method I needed to use in DBD::mysql and cache
all my results at once so I can loop through them whenever I want.

My code looks like this now:

use Cache::FileCache;
my $cache = Cache::FileCache->new({
	namespace => "test",
	cache_root =>"cache/"
	});
my $all_arrays = $cache->get("testcache");
if (not defined $all_arrays){
	use DBI qw/:sql_types/;
	my $dbtype = "mysql";
	my $dbname = "test_db";
	my $dbuser = "test_user";
	my $dbpass = "12345678";
	my $tbl = "mybooks";
	my $default_cache_time = "30 minutes" ;
	my $dsn = "DBI:$dbtype:dbname=$dbname;host=localhost";
	my $dbh = DBI->connect($dsn, $dbuser, $dbpass);
	my $sql = "select id, name from $tbl order by id desc";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	$all_arrays = $sth->fetchall_arrayref ;
	$sth->finish;
	$dbh->disconnect ;
	$cache->set("testcache",$all_arrays,$default_cache_time);
}
foreach $r (@{$all_arrays}) {
	print "@$r\n";
}

Obviously, I can play with @$r by getting @$r[0] and @$r[1] and so on...

Thank you again DeWitt for showing me mysql's fetchall_arrayref and
other methods!

--Danny