Caching DBI 'prepare' statements for re-use with an Apache server

[Dave Cardwell]

Hello.

Is there any way to cache the statement handles returned by prepare or
perhaps prepare_cached across scripts when using the DBI module?
Specifically I'd like to re-use a few of my queries with placeholders across
multiple calls to CGI scripts. I've looked at the documentation for
Apache:BI and it didn't mention the caching of prepares, so I guess that
doesn't do it.
I'd rather not have to hard-code my common prepare statements anywhere
if possible.

Regards,

 

[Sherm Pendley]

Is there any way to cache the statement handles returned by prepare or
perhaps prepare_cached across scripts when using the DBI module?

Sure. Just declare a package-scoped variable to hold it using either 'use
vars' or 'our' - 'our' is preferred, unless you're working on a very old
Perl where it isn't supported.

Then simply assign it with:

$sth ||= prepare(...);

That's a shortcut for:

$sth = ($sth || prepare(...) );

Perl is lazy (in a good way) with regard to logical ORs; it bails out upon
finding the first true clause. So, if $sth is already defined it doesn't
call prepare().

You can use this technique in many other ways, too. I've found it *very*
useful when working with XSLT objects, as parsing the style sheet and
creating the transformer object is relatively expensive.

sherm--

 

[Ben Morrow]

^^^^^^^^^^^^^^
Sure. Just declare a package-scoped variable to hold it using either 'use
vars' or 'our' - 'our' is preferred, unless you're working on a very old
Perl where it isn't supported.

You missed the important bit.

Ben

 

[Sherm Pendley]

You missed the important bit.

Ouch. Yep, I did.

The technique is still valid - although the details are different. To share
the variable across multiple scripts, you need to refer to it by its fully
qualified name, including the package its in.

Something like:

$MyCachePackage::sth ||= prepare(...);

The OP mentioned Apache:BI, but he also mentioned CGI, so I suppose I
should also point out that I'm assuming a mod_perl environment.

sherm--

 

[Anno Siegel]

Dave Cardwell wrote:
[...]

Then simply assign it with:

$sth ||= prepare(...);

That's a shortcut for:

$sth = ($sth || prepare(...) );

Perl is lazy (in a good way) with regard to logical ORs; it bails out upon
finding the first true clause. So, if $sth is already defined it doesn't
call prepare().

You can use this technique in many other ways, too. I've found it *very*
useful when working with XSLT objects, as parsing the style sheet and
creating the transformer object is relatively expensive.

It's known as the "orcish maneuver" (from "or cache"). See
http://perlmonks.thepen.com/Orcish Maneuver.html
for the full background.

It's going to be even more versatile with the upcoming //=, which is like
||=, but sensitive to defined-ness, not boolean value. We can still
call it "orcish maneuver", // is an or-like operation.

Anno