Hi John: It's ironic how similar our applications seem!
I have a perl module that wraps around DBI, and get this, it is called Db.pm also. Not to worry, it has a unique namespace prefix, so they will not clash if ever they meet. A good thing for modules I think. BTW -- I use the selective query tricks too. It's a big help. If you are able to use global vars (use vars) in your subs with no problem, then I've some work ahead of me. I think it points to the fact that either I have a systematic problem with my code or I have uncovered a bug/feature in some of the underlying code. I'm still open for suggestions on what might cause a global to be unreliable in subs or other .asp scripts. Thanks again. Regards, Steve > -----Original Message----- > From: Brat Wizard [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:50 AM > To: Stephen Bardsley; Apache::ASP Mailing List > Subject: Re: dynamic module > > > > 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]
