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