> I want my variable to be valid and defined for the duration of a Session.
Okay, that's what the session object is for, as you appear to already know. ;)
> > example) is to use Apache::DBI. Apache::DBI will override the normal DBI
> I am using and have used Apache::DBI with good results. I'm sorry, I
> didn't mean to imply "on-the-fly" as from one script to the next. To be
> more precise I meant from one Session to the next.
Okay-- so if I'm understanding you correctly, connecting to the database each
time the script is executed (a la Script_OnStart) is okay, so what is the
problem with say storing the datasource parameters as session data and then
using it to reconstitute the handle each time? You can't cache the handle
itself, but you _can_ cache the name of the database connected to (and
whatever host/user info you need) as session scalars (or a hash-- or even
just store the actual DBI data source string itself if that's all you really
need). Then just use that information the next time around to re-connect to
the database.. Basically check to see if we've been connected before (ie. is
$..dbname defined, or whatever). If not, store it. When you're done, close
the connection and zap the data in the session variable (or abandon the
session altogether). Then go through the process all over again for the next
session..
You didn't say, but perhaps you are concerned about caching selected data from
a query and that's what the real bottom-line issue is?? (as in: why it would
be interesting/useful to be able to store a db handle in the session object
anyway...)
This is a trick I often use.. if the query is expensive (a full-text search or
something) and I don't want to have to rerun the query each time to get the
next chunk of data-- what I will do is run the query and ONLY select the
actual key(s) to the data itself in whatever order they're needed. (eg.
SELECT key_field FROM table WHERE blah blah ORDER BY blah blah) Then blast
the keys into a file-- several ways to do this quickly & effectively, the
easiest is to simply create a key array and use Storable to freeze it in a
file. Then you can just thaw it out later and use the list.
By using a key array, you can construct a query retrieving _only_ the items
needed for the specific page which is generally very fast (much much faster
usually) compared to running the query all over again (eg. SELECT
fields_i_need FROM table WHERE key=key1 or key=key2 or key=key3..etc) They
are already in the correct order so you don't need the overhead of an "order
by" clause.
When you're through, dump the key array whenever its convenient (or don't if
you don't care about disk space-- the frozen files are small, you can reap
them at your leisure).
If I'm displaying paged data, a simple rule I often employ is that either a
new sort order (clicking on a column header for instance) or retrieving page
zero will dump the cache & rerun the query (making a new key array). It
generally works quite well, and I've been very pleasantly surprised at how
small the frozen cache files are, even for large queries. And slurping up a
frozen array is pretty darned quick. If you hide all the mechanical bits
inside functions, pretty soon you forget how horrible a kludge this really is
and it almost begins to seem normal :) ;)
> > The next easiest thing to do is to figure out what database to connect to
> > and open it each time the script runs (the Script_OnStart() function is a
>
> This is kind of what I am doing now, and can't quite get things working.
> It seems it may actually be an issue with variable scoping. If I scope
> my module's instance variable in the Script_OnStart things seem to work.
> That is, I create the instance in Script_OnStart. If I create the
> instance in one of global.asa's sub's then it does not seem to work
> reliably. Bear in mind I am using global variables in global.asa
> by way of "use vars qw( $var2 $var2 $etc );". If this is incorrect
> or a better way exists, I'm all ears. Thanks again for your time.
Hmm- interesting. I do it in subs all the time with no problems whatsoever. I
declare the global variable the same way you do, "use vars qw( $db ... blah
blah);" at the top of my global.asa file (and again in EVERY package file
that is not a separate module-- not a separate namespace-- I break a lot of
my code up into sub-files to keep it more manageable). The scope should be
global. Might you have some my variable someplace that's stomping on the
global version??
I have a Db.pm module that I wrote that basically encapsulates most of the DBI
stuff I use-- so keep that in mind in the short example below-- it isn't
exactly what you're used to but it will be similar enough. In one of my
global.asa files, the flow goes something like this:
package Apache::ASP::STORES;
use vars qw{ $db ... [more vars] };
use lib Apache->request->dir_config('BaseDir')."/lib";
use Apache::ASP;
use Db;
[more use statements]
sub Script_OnStart {
[..do stuff]
connectToDB();
[.. do more stuff.. ]
}
sub connectToDB {
my $dbname = <my dbname>; ## actually declared elsewhere
my $dbhost = <my host>; ## for me, but you get the idea
return if ($db ne undef); ## got a handle thanks, don't need another
$db = new Db;
$db->Connect($dbname, $dbhost);
return $db;
}
In this case, the $db handle that gets returned is of my own "db" style that I
mentioned. But it only wraps around the usual dbh handle and is mostly
exposed except for wrapping commonly used constructs to make slapping
together code quicker, easier, and a bit more mindless. Its pretty easy
actually, even my cat could do it... (as it happens, I walked into the
computer room one day and there he was sitting building a database cataloging
north american migratory birds and cross-referencing them by recipe... but
that's another story altogether ;)
Hope this helps-
John Whitten
[EMAIL PROTECTED]
Wizard.Org, Inc.
>
> > The best way (but not necessarily the easiest) is to use some sort of
> > middleware such as SQLrelay or something similar that will sit between
> > your perl application and the database, maintain open connections and
> > drop or establish new connections as demanded. SQLrelay is a neat thing
> > but it is a pain-in-the-ass (IMO) to install and configure. (I should
> > also point out that my experience with it is some 6+months old so a newer
> > version which may address some of these issues may be available?)
> > SQLrelay works with just about any database DBI can work with, and in
> > addition can work across servers, and I *think* (perhaps it was a planned
> > feature, in any case I never used it myself) it had auto-failover support
> > in case one server didn't answer up-- won't swear to that though- perhaps
> > it was on the to-do list. In any case, it is pretty clever software to
> > say the least.
> >
> > On Monday 16 September 2002 05:11 pm, Stephen Bardsley spewed into the
ether:
> > > Greetings:
> > >
> > > I have been struggling with something and I hope
> > > someone can set me straight.
> > >
> > > <%
> > > I have a perl module, which contains a database
> > > handle (dbh).
> > >
> > > I would like to use this module under Apache::ASP.
> > > I would like to have users dynamically define a database
> > > for the module to connect to. I can/will constrain
> > > the lifetime of the module to that of an Apache::ASP
> > > $Session.
> > >
> > > The trouble I am having is that I cannot maintain
> > > the coherence of the dbh in the module. Sometimes
> > > the dbh is defined and sometimes not. I have used
> > > this module successfully in other Apache::ASP
> > > applications, but the database has always been
> > > statically defined to the module.
> > >
> > > Is there a way to allow the user to define the
> > > database to the module?
> > > %>
> > >
> > > Any suggestions will be greatly appreciated.
> > >
> > > Steve
> > > _____________________
> > > Stephen Bardsley
> > > RLW Inc.
> > > Malta, NY
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> > --
> >
> > -------------------------------------------------------------------------
> >------- Check out http://www.Wizard.Org for great deals on Electronic
> > Parts *NEW* Computer Parts & Accessories - Drives - LCD - Systems - Linux
> > -------------------------------------------------------------------------
> >------- ** Affordable Online Store w/Merchant Card Processing & Paypal **
> > Write to us: [EMAIL PROTECTED] -- Get your Store Online Today!
> > -------------------------------------------------------------------------
> >-------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
--
--------------------------------------------------------------------------------
Check out http://www.Wizard.Org for great deals on Electronic Parts
*NEW* Computer Parts & Accessories - Drives - LCD - Systems - Linux
--------------------------------------------------------------------------------
** Affordable Online Store w/Merchant Card Processing & Paypal **
Write to us: [EMAIL PROTECTED] -- Get your Store Online Today!
--------------------------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
