Re: database access
- Original Message - From: "Gunther Birznieks" <[EMAIL PROTECTED]> To: "Les Mikesell" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: "Tim Bunce" <[EMAIL PROTECTED]>; "Aaron" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, November 16, 2000 3:04 AM Subject: Re: database access > 1. I don't see the scenario you are talking about (dynamic connection > pooling) actually working too well in practice because with most web sites > there is usually peak times and non-peak times. Your database still has to > handle the peak times and keep the connection open, so why not just leave > the connection open during non-peak. It doesn't seem like it would really > matter. Do you have a real scenario that this is a concern in? This is not so much a problem where you have one or two databases on a production server that is used by most pages - you are right that you just have to be able to handle those connections. The problem I see is on an internal intranet server where there are lots of little special-purpose databases - separate calendar, forums, and assorted applications, each with its own database and usage pattern. If these all get used at once, the backend database server accumulates a lot of connections. At the moment I don't have run-time user/password based connections per user, but that would be an even bigger problem. > 2. I do see adding a regex on the connect string to explicitly stop > Apache::DBI from caching the connection being valuable. That would probably be enough - or the other way around to specify the one(s) you know should be cached. > 3. As a plug, I had also suggested a couple years ago that I would like the > feature to be able to specifically not having the ping() method by > Apache::DBI if the database had already been pinged within a set period of > time. That would fall into place if it timestamped the usage per handle and also did a real disconnect on any that hadn't been used recently. Les Mikesell [EMAIL PROTECTED]
RE: database access
Uh, I was just seeing if anyone actually read my emails. :) Actually, I am quite happy this was added. Thanks! At 09:02 AM 11/16/00 -0500, Geoffrey Young wrote: > > -Original Message- > > From: Gunther Birznieks [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, November 16, 2000 4:04 AM > > To: Les Mikesell; [EMAIL PROTECTED] > > Cc: Tim Bunce; Aaron; [EMAIL PROTECTED] > > Subject: Re: database access > > > > > > 3. As a plug, I had also suggested a couple years ago that I > > would like the > > feature to be able to specifically not having the ping() method by > > Apache::DBI if the database had already been pinged within a > > set period of > > time. > > > >Apache::DBI->setPingTimeOut($data_source, $timeout) > >no? > >I similarly pointed out last last year that there is a (small) bug in >Apache::DBI that when you want it to ping _every_ time it won't if two pings >are in the same 'second' due to the granularity of time() (ie, a content >handler and log handler that access the db within one second of eachother, >but in separate phases). > >--Geoff __ Gunther Birznieks ([EMAIL PROTECTED]) eXtropia - The Web Technology Company http://www.extropia.com/
RE: database access
> -Original Message- > From: Gunther Birznieks [mailto:[EMAIL PROTECTED]] > Sent: Thursday, November 16, 2000 4:04 AM > To: Les Mikesell; [EMAIL PROTECTED] > Cc: Tim Bunce; Aaron; [EMAIL PROTECTED] > Subject: Re: database access > > > 3. As a plug, I had also suggested a couple years ago that I > would like the > feature to be able to specifically not having the ping() method by > Apache::DBI if the database had already been pinged within a > set period of > time. > Apache::DBI->setPingTimeOut($data_source, $timeout) no? I similarly pointed out last last year that there is a (small) bug in Apache::DBI that when you want it to ping _every_ time it won't if two pings are in the same 'second' due to the granularity of time() (ie, a content handler and log handler that access the db within one second of eachother, but in separate phases). --Geoff
Re: database access
1. I don't see the scenario you are talking about (dynamic connection pooling) actually working too well in practice because with most web sites there is usually peak times and non-peak times. Your database still has to handle the peak times and keep the connection open, so why not just leave the connection open during non-peak. It doesn't seem like it would really matter. Do you have a real scenario that this is a concern in? 2. I do see adding a regex on the connect string to explicitly stop Apache::DBI from caching the connection being valuable. 3. As a plug, I had also suggested a couple years ago that I would like the feature to be able to specifically not having the ping() method by Apache::DBI if the database had already been pinged within a set period of time. I think the conclusion to #3 was that it would be good to add, but that I should add it. Unfortunately, I don't run a huge site on mod_perl so I didn't really care about eeking out performance this way (I think it was off the thread that the ping() method was very heavyweight on DBD::Sybase). Thus, I never added it and I don't know if anyone else ever was motivated to either. Later, Gunther At 07:53 PM 11/15/2000 -0600, Les Mikesell wrote: >- Original Message - >From: <[EMAIL PROTECTED]> >To: "Les Mikesell" <[EMAIL PROTECTED]> >Cc: "Tim Bunce" <[EMAIL PROTECTED]>; "Aaron" <[EMAIL PROTECTED]>; ><[EMAIL PROTECTED]> >Sent: Wednesday, November 15, 2000 2:21 AM >Subject: Re: database access > > > > On Tue, 14 Nov 2000, Les Mikesell wrote: > > > > > I wonder if Apache::DBI could figure out what connections are worth > > > holding open by itself? It could have some hints in the config file > > > like regexps to match plus a setting that would tell it not to > > > make a connection persist unless it had seen x instances of that > > > connect string in y seconds. > > > > That really, really sucks, but Apache is selecting on the HTTP socket, and > > nothing matters beyond that, except signals of course. What you are > > implying is that DBI will be aware of the connections being closed or > > SIGALRM coming thru to the apache and on its lap, but it won't happen. > >No, I realize there is nothing to do after the fact - what I meant was that >Apache::DBI might allow disconnect to really happen the first few >times it sees a connect string after a child startup. If it saved the >string with a timestamp and waited until it had seen the same string >several times within a short interval it would be fairly likely that it >would be worth staying connected. You'd trade some slower hits >off against not accumulating a huge number of little-used database >connections. > > Les Mikesell > [EMAIL PROTECTED] __ Gunther Birznieks ([EMAIL PROTECTED]) eXtropia - The Web Technology Company http://www.extropia.com/
Re: database access
- Original Message - From: <[EMAIL PROTECTED]> To: "Les Mikesell" <[EMAIL PROTECTED]> Cc: "Tim Bunce" <[EMAIL PROTECTED]>; "Aaron" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 15, 2000 2:21 AM Subject: Re: database access > On Tue, 14 Nov 2000, Les Mikesell wrote: > > > I wonder if Apache::DBI could figure out what connections are worth > > holding open by itself? It could have some hints in the config file > > like regexps to match plus a setting that would tell it not to > > make a connection persist unless it had seen x instances of that > > connect string in y seconds. > > That really, really sucks, but Apache is selecting on the HTTP socket, and > nothing matters beyond that, except signals of course. What you are > implying is that DBI will be aware of the connections being closed or > SIGALRM coming thru to the apache and on its lap, but it won't happen. No, I realize there is nothing to do after the fact - what I meant was that Apache::DBI might allow disconnect to really happen the first few times it sees a connect string after a child startup. If it saved the string with a timestamp and waited until it had seen the same string several times within a short interval it would be fairly likely that it would be worth staying connected. You'd trade some slower hits off against not accumulating a huge number of little-used database connections. Les Mikesell [EMAIL PROTECTED]
Re: database access
On Tue, 14 Nov 2000, Les Mikesell wrote: > I wonder if Apache::DBI could figure out what connections are worth > holding open by itself? It could have some hints in the config file > like regexps to match plus a setting that would tell it not to > make a connection persist unless it had seen x instances of that > connect string in y seconds. That really, really sucks, but Apache is selecting on the HTTP socket, and nothing matters beyond that, except signals of course. What you are implying is that DBI will be aware of the connections being closed or SIGALRM coming thru to the apache and on its lap, but it won't happen. Here you have to decide wether you will keep the connection or not, and if it something happens, like closing, you have to wait until user hits the page. Yes you can make ModPerl timestamp DBI links, so that they can be timed out next time a user calls one of the DBI functions, maybe connect? But asynchrosity is out of the question. It would be cool however if you can insert your socket into apaches select queue... isn't that how it works? Then you can really do some funky stuff... just 2c Pavel
Re: database access
- Original Message - From: "Tim Bunce" <[EMAIL PROTECTED]> > > > > > > > Don't get me wrong here, "but", it would be nice if the undocumented > > > > somehow made it to the documented status. > > > > > > > yeah... but Apache::DBI and DBI are in cahoots! it's a secret love that > > no documentation can break apart! > > > > no, really it would be nice if the DBI connection "hook" was documented. it > > might even turn out to be useful for other module authors. > > The next version will support a dbi_connect_method attribute to > DBI->connect() to tell it which driver class::method to use. I wonder if Apache::DBI could figure out what connections are worth holding open by itself? It could have some hints in the config file like regexps to match plus a setting that would tell it not to make a connection persist unless it had seen x instances of that connect string in y seconds. Les Mikesell [EMAIL PROTECTED]
Re: database access
On Mon, Nov 13, 2000 at 01:55:38PM -0500, Aaron wrote: > > > > > Don't get me wrong here, "but", it would be nice if the undocumented > > > somehow made it to the documented status. > > > > yeah... but Apache::DBI and DBI are in cahoots! it's a secret love that > no documentation can break apart! > > no, really it would be nice if the DBI connection "hook" was documented. it > might even turn out to be useful for other module authors. The next version will support a dbi_connect_method attribute to DBI->connect() to tell it which driver class::method to use. Tim.
Re: database access
> > > Don't get me wrong here, "but", it would be nice if the undocumented > > somehow made it to the documented status. > yeah... but Apache::DBI and DBI are in cahoots! it's a secret love that no documentation can break apart! no, really it would be nice if the DBI connection "hook" was documented. it might even turn out to be useful for other module authors. aaron
RE: database access
Hi there, On Mon, 13 Nov 2000, George Sanderson wrote: > Don't get me wrong here, "but", it would be nice if the undocumented > somehow made it to the documented status. Well don't get me wrong either, but when I thought the Guide could do with a re-write, I re-wrote it. :) 73, Ged.
RE: database access
At 08:04 AM 11/13/00 -0500, you wrote: >I haven't followed this thread too closely, so forgive me if I'm not quite >with it... > >you can already do this with Apache::DBI... > >change DBI->connect() to the (undocumented) 6 parameter call: > > Don't get me wrong here, "but", it would be nice if the undocumented somehow made it to the documented status. A related difficulty, is reading and understanding the documentation with my set of problems (problems are the reason why I read most documentation). I understand it is a difficult task, but someone has to do it (we will all benifit). Thanks.
RE: database access
I haven't followed this thread too closely, so forgive me if I'm not quite with it... > > It would be very useful to be able to specify at connect time that > you don't want a particular connection to be persistent. If you have > a lot of small databases or some with different user/password > permissions > you accumulate too many backend servers - but if you also have one > or more connections used all the time you don't want to give up > Apache::DBI. you can already do this with Apache::DBI... change DBI->connect() to the (undocumented) 6 parameter call: $dbh = DBI->connect($dbase, $user, $pass, \%attr, undef, "connect") || die $DBI::errstr; which will override the default Apache::DBI behavior of caching new connections... HTH --Geoff
Re: database access
Les Mikesell wrote: > > Perrin Harkins wrote: > > > > On Fri, 10 Nov 2000, Tim Sweetman wrote: > > > > Would you be interested in adding support for resetting some of these to > > > > Apache::DBI? It's pretty easy to do, using PerlCleanupHandler like the > > > > auto-rollback does. It would be database-specific though, so you'd have > > > > to find a way for people to explicitly request cleanups. > > > > > > I suspect automating via DBI would be a major pain, because you'd have > > > to be able to identify the "dangerous" changes in state. Probably > > > requiring SQL to be parsed. :( > > > > The current rollback cleanup doesn't parse SQL. It knows that a rollback > > won't do damage if there are no transactions to be cleaned up, so it's > > safe to do every time. If there are other things that work this way, you > > could add them. Probably wouldn't work for things like MySQL table locks > > though. People will have to do that themselves. > > If the backend supports rollback, couldn't you just try that instead > of the ping method to see if you are still connected and get the > cleanup as a side effect? > > > > > In principle, you could probably have a ->do_not_reuse method which > > > could be called before someone does something dangerous. As long as they > > > remember. > > > > But that would also mean no more persistent connection. Maybe that would > > work if you don't do it very often. > > It would be very useful to be able to specify at connect time that > you don't want a particular connection to be persistent. If you have > a lot of small databases or some with different user/password > permissions > you accumulate too many backend servers - but if you also have one > or more connections used all the time you don't want to give up > Apache::DBI. I'm not sure it is worth caching MySQL connections > on the same host, either since it is so fast to start up. Has > anyone timed the difference? > >Les Mikesell >[EMAIL PROTECTED] as far as i can tell the problem here is in the design of the particular db your connecting to no one database is going to be architected in the same way so providing an abstracted layer to handle this will be a very tough call it would be great to see a DBI::persist or APache::DBI::persist but it would be hard to implement down the line to the DBD::*** as each one of those drivers would have to be able to handle the persistence call in their own way in some cases this would be an easily handled affair and in others a real horror show of coding correct?
Re: database access
Perrin Harkins wrote: > > On Fri, 10 Nov 2000, Tim Sweetman wrote: > > > Would you be interested in adding support for resetting some of these to > > > Apache::DBI? It's pretty easy to do, using PerlCleanupHandler like the > > > auto-rollback does. It would be database-specific though, so you'd have > > > to find a way for people to explicitly request cleanups. > > > > I suspect automating via DBI would be a major pain, because you'd have > > to be able to identify the "dangerous" changes in state. Probably > > requiring SQL to be parsed. :( > > The current rollback cleanup doesn't parse SQL. It knows that a rollback > won't do damage if there are no transactions to be cleaned up, so it's > safe to do every time. If there are other things that work this way, you > could add them. Probably wouldn't work for things like MySQL table locks > though. People will have to do that themselves. If the backend supports rollback, couldn't you just try that instead of the ping method to see if you are still connected and get the cleanup as a side effect? > > In principle, you could probably have a ->do_not_reuse method which > > could be called before someone does something dangerous. As long as they > > remember. > > But that would also mean no more persistent connection. Maybe that would > work if you don't do it very often. It would be very useful to be able to specify at connect time that you don't want a particular connection to be persistent. If you have a lot of small databases or some with different user/password permissions you accumulate too many backend servers - but if you also have one or more connections used all the time you don't want to give up Apache::DBI. I'm not sure it is worth caching MySQL connections on the same host, either since it is so fast to start up. Has anyone timed the difference? Les Mikesell [EMAIL PROTECTED]
Re: database access
On Fri, 10 Nov 2000, Tim Sweetman wrote: > > Would you be interested in adding support for resetting some of these to > > Apache::DBI? It's pretty easy to do, using PerlCleanupHandler like the > > auto-rollback does. It would be database-specific though, so you'd have > > to find a way for people to explicitly request cleanups. > > I suspect automating via DBI would be a major pain, because you'd have > to be able to identify the "dangerous" changes in state. Probably > requiring SQL to be parsed. :( The current rollback cleanup doesn't parse SQL. It knows that a rollback won't do damage if there are no transactions to be cleaned up, so it's safe to do every time. If there are other things that work this way, you could add them. Probably wouldn't work for things like MySQL table locks though. People will have to do that themselves. > In principle, you could probably have a ->do_not_reuse method which > could be called before someone does something dangerous. As long as they > remember. But that would also mean no more persistent connection. Maybe that would work if you don't do it very often. - Perrin
Re: database access
Perrin Harkins wrote: > > On Thu, 9 Nov 2000, Tim Sweetman wrote: > > Apache::DBI is, as far as I know, dangerous, and people rarely seem to > > warn of this. > > It's no more dangerous than any other scheme for persistent connections, > like JDBC pooling, etc. > > > It's dangerous because: > > (a) Although it rolls back any transactions automatically, it may not > > deal with other persistant > > state on the handle. (Does it clear MySQL table locks, for > > instance?). Other settings, such > > as the lock timeout (SQL: "SET LOCK MODE TO WAIT nnn") or isolation > > levels, will also persist. > Would you be interested in adding support for resetting some of these to > Apache::DBI? It's pretty easy to do, using PerlCleanupHandler like the > auto-rollback does. It would be database-specific though, so you'd have > to find a way for people to explicitly request cleanups. I suspect automating via DBI would be a major pain, because you'd have to be able to identify the "dangerous" changes in state. Probably requiring SQL to be parsed. :( In principle, you could probably have a ->do_not_reuse method which could be called before someone does something dangerous. As long as they remember. > I used to do something like this to call finish() on all of my cached > statement handles, back when prepare_cached() used to die on unfinished > handles. (Now it just warns.) > > > (b) Presumably, what were previously separate transactions on the same > > database on different handles > > can become entangled: > > > > TX1: BEGIN WORK > > TX2: BEGIN WORK > > TX1: INSERT A > > TX2: COMMIT > > TX1: INSERT B > > > > ... this would probably cause either fatal errors, or very strange > > things to happen. > > Wouldn't this be the same with Apache::DBI or without it? Or are you > saying that this would have been done with two separate database > connections before Apache::DBI came into the mix? You can still get > multiple connections through Apache::DBI if you really want to. Just vary > anything at all in the connect string ({MyBogusParam => 1}) and you'll get > a different handle. I believe without Apache::DBI there'd be two handles. This could be useful for some purposes... though is, in itself, dangerous if TX1 and TX2 touch the same tables because you can get into a deadlock that your database won't know about. > So you would check handles in and out? I suppose that would work, but it > sounds like a lot of trouble to handle a very unusual case to me. Could be. I _have_ seen such cases. I don't know how rare they are. Checking handles in & out is a mixed blessing (since it slows you down if you have lots of processes grabbing handles in autocommit mode & performing single statements, in which case sharing handles is harmless). Cheers -- Tim Sweetman A L Digital "Any technology distinguishable from magic is insufficiently advanced"
Re: database access
On Thu, 9 Nov 2000, Tim Sweetman wrote: > Apache::DBI is, as far as I know, dangerous, and people rarely seem to > warn of this. It's no more dangerous than any other scheme for persistent connections, like JDBC pooling, etc. > It's dangerous because: > (a) Although it rolls back any transactions automatically, it may not > deal with other persistant > state on the handle. (Does it clear MySQL table locks, for > instance?). Other settings, such > as the lock timeout (SQL: "SET LOCK MODE TO WAIT nnn") or isolation > levels, will also persist. Would you be interested in adding support for resetting some of these to Apache::DBI? It's pretty easy to do, using PerlCleanupHandler like the auto-rollback does. It would be database-specific though, so you'd have to find a way for people to explicitly request cleanups. I used to do something like this to call finish() on all of my cached statement handles, back when prepare_cached() used to die on unfinished handles. (Now it just warns.) > (b) Presumably, what were previously separate transactions on the same > database on different handles > can become entangled: > > TX1: BEGIN WORK > TX2: BEGIN WORK > TX1: INSERT A > TX2: COMMIT > TX1: INSERT B > > ... this would probably cause either fatal errors, or very strange > things to happen. Wouldn't this be the same with Apache::DBI or without it? Or are you saying that this would have been done with two separate database connections before Apache::DBI came into the mix? You can still get multiple connections through Apache::DBI if you really want to. Just vary anything at all in the connect string ({MyBogusParam => 1}) and you'll get a different handle. > (b) could be trapped by Apache::DBI refusing to give out two copies of > the same DBH at the same So you would check handles in and out? I suppose that would work, but it sounds like a lot of trouble to handle a very unusual case to me. I don't think I've ever seen an application that used multiple unrelated transactions on the same database with interleaved operations like this. It looks like something that would be more likely to happen in a threaded environment where database handles were shared across threads. - Perrin
[OT] RE: database access
> I am trying to access access Microsoft SQL server on NT from Apache Server > under Linux, > Can some one give me a direction, how/where to looking into it? > > Appreciate for any help Check out the FreeTDS libs at: http://www.freetds.org/ I use it now to contact a MS SQL 6.5 server from Linux. I think it also works with 7.0 but haven't tried it myself. Joshua
RE[2]: database access => How To: Linux => WinNT ODBC Proxy
Check out DBI::Proxy Here is how I implemented Linux->Windows ODBC integration using DBI::Proxy 1) install all of the necessary modules on Windows machine (get off CPAN) -Net-Daemon-0.31 -PlRPC-0.2012 you will have to use nmake to compile the modules but they are a snap (nmake15.exe on Microsoft's site) 2) make a C:\tmp [/tmp] directory (needs to be this - tmp!) 3) run from a DOS prompt: dbiproxy --debug --logfile log --localport this script comes with DBI only really seems to print to screen not to log file Now from your Linux box (make sure you have DBI installed): #!/usr/bin/perl # We created a DSN called MYDSN in the Windows control panel ODBC dialog my $dsn_name = "MYDSN"; my $dsn = "dbi:ODBC:$dsn_name"; my $uid = 'username'; my $pass = 'password'; my $hostname = '192.168.0.4'; my $port = ; my $tablename = 'Customers'; my $proxy = "hostname=$hostname;port=$port"; my $dbh = DBI->connect("dbi:Proxy:$proxy;dsn=$dsn", $uid, $pass); my $request= "SELECT * FROM \"$tablename""; my $sth= $dbh->prepare($request); my $res= $sth->execute(); while(my $r = $sth->fetchrow_hashref) { print"$_ = $r->{$_}\n" for sort keys %$r; print"**\n"; ; } Here is how to install a service: Installing the Service A) Install the attached files into the system32 directory -instsrv.exe (installs services) -srvany.exe (allows applications to run as a service) B) Install your service: INSTSRV [Service_name] c:\winnt\system32\srvany.exe C) Configure the service via the Services Applet (in Control Panel) using the "Startup..." button/dialog (not really much to do here besides manual or automatic - which we all know which one we want ;-) Specifying the application A) Run the registry editor (regedt32.exe) B) under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\[Service_name] -create a 'Parameters' key (looks like a directory when added) C) under the 'Parameters' key, create a key value 'Application' of type REG_SZ and specify the full path of the executable (or batch file) including the extension Application: REG_SZ: c:\dbi_prox.bat Check out the readme.wri that is in the srvany_x86 zip file for complete instructions At 08:16 AM 11/9/00 -0600, you wrote: >I am trying to access access Microsoft SQL server on NT from Apache Server >under Linux, >Can some one give me a direction, how/where to looking into it? > >Appreciate for any help > >PC Wang > >-Original Message- >From: clayton cottingham [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, November 08, 2000 4:11 PM >To: Greg Cope >Cc: Jason Liu; [EMAIL PROTECTED] >Subject: Re: database access > > >Greg Cope wrote: > > > > Jason Liu wrote: > > > > > > Is Apache::DBI absolutely necessary if you want to establish persistent > > > database connection per child? > > > > No you can write your own (its open source remember ;-) but why bother - > > standing on the shoulders of giants etc > > > > Greg > > > > > > > > Thanks, > > > > > > Jason > > > > > > > -Original Message- > > > > From: David Hodgkinson [mailto:[EMAIL PROTECTED]] > > > > Sent: Monday, November 06, 2000 5:10 AM > > > > To: Jason Liu > > > > Cc: [EMAIL PROTECTED] > > > > Subject: Re: database access > > > > > > > > > > > > "Jason Liu" <[EMAIL PROTECTED]> writes: > > > > > > > > > In general, how should database connections be handled between > > > > parent and > > > > > child processes? Can you establish database connections from within >a > > > > > handler? > > > > > > > > Absolutely. And using Abache::DBI caches the connection handle. > > > > > > > > -- > > > > Dave Hodgkinson, http://www.hodgkinson.org > > > > Editor-in-chief, The Highway Star http://www.deep-purple.com > > > > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > > > > - > > > > > > > >the problem can also be resolved depending on the type of db used > >yes
Re: database access
clayton cottingham wrote: > > Greg Cope wrote: > > > > Jason Liu wrote: > > > > > > Is Apache::DBI absolutely necessary if you want to establish persistent > > > database connection per child? > > > > No you can write your own (its open source remember ;-) but why bother - > > standing on the shoulders of giants etc > > > i usually use something that does all the basic statement handling and > db connect strings > > then i can just > use PGForm; #my module to go to Pg > PGform($db,$user,$password,$sqlstatement); > > and > if a select returns a array of arrays > > else if insert update delete etc returns ok or error I agree with Clayton about wrappers, but for slightly different reasons: Apache::DBI is, as far as I know, dangerous, and people rarely seem to warn of this. It's dangerous because: (a) Although it rolls back any transactions automatically, it may not deal with other persistant state on the handle. (Does it clear MySQL table locks, for instance?). Other settings, such as the lock timeout (SQL: "SET LOCK MODE TO WAIT nnn") or isolation levels, will also persist. A one-shot script may simply set them without thinking about what happens if they persist. Even something written for mod-Perl would have to set them all back to their previous values, and catch its own errors in an eval {}, to avoid unpredictable things happening in case of errors. (b) Presumably, what were previously separate transactions on the same database on different handles can become entangled: TX1: BEGIN WORK TX2: BEGIN WORK TX1: INSERT A TX2: COMMIT TX1: INSERT B ... this would probably cause either fatal errors, or very strange things to happen. (a) would be v. hard to avoid without tracking whatever state is used in your database & application, which is where a wrapper comes in. (b) could be trapped by Apache::DBI refusing to give out two copies of the same DBH at the same time. AS FAR AS I KNOW it doesn't do this. Cheers -- Tim Sweetman A L Digital moving sideways --->
RE: database access
I am trying to access access Microsoft SQL server on NT from Apache Server under Linux, Can some one give me a direction, how/where to looking into it? Appreciate for any help PC Wang -Original Message- From: clayton cottingham [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 08, 2000 4:11 PM To: Greg Cope Cc: Jason Liu; [EMAIL PROTECTED] Subject: Re: database access Greg Cope wrote: > > Jason Liu wrote: > > > > Is Apache::DBI absolutely necessary if you want to establish persistent > > database connection per child? > > No you can write your own (its open source remember ;-) but why bother - > standing on the shoulders of giants etc > > Greg > > > > > Thanks, > > > > Jason > > > > > -Original Message- > > > From: David Hodgkinson [mailto:[EMAIL PROTECTED]] > > > Sent: Monday, November 06, 2000 5:10 AM > > > To: Jason Liu > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: database access > > > > > > > > > "Jason Liu" <[EMAIL PROTECTED]> writes: > > > > > > > In general, how should database connections be handled between > > > parent and > > > > child processes? Can you establish database connections from within a > > > > handler? > > > > > > Absolutely. And using Abache::DBI caches the connection handle. > > > > > > -- > > > Dave Hodgkinson, http://www.hodgkinson.org > > > Editor-in-chief, The Highway Star http://www.deep-purple.com > > > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > > > - > > > the problem can also be resolved depending on the type of db used yes you can program your own levels of persistence finding a database that can do this for you can be a great help. postgres for instance has concurrent locking , on table , row and or column. using its Pg module instead of DBD::Pg and DBI handles the opening and closing of the connections too ;) making your own wrapper module ito interface with DBI or PG is a good thing to do as well i usually use something that does all the basic statement handling and db connect strings then i can just use PGForm; #my module to go to Pg PGform($db,$user,$password,$sqlstatement); and if a select returns a array of arrays else if insert update delete etc returns ok or error
Re: database access
Greg Cope wrote: > > Jason Liu wrote: > > > > Is Apache::DBI absolutely necessary if you want to establish persistent > > database connection per child? > > No you can write your own (its open source remember ;-) but why bother - > standing on the shoulders of giants etc > > Greg > > > > > Thanks, > > > > Jason > > > > > -Original Message- > > > From: David Hodgkinson [mailto:[EMAIL PROTECTED]] > > > Sent: Monday, November 06, 2000 5:10 AM > > > To: Jason Liu > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: database access > > > > > > > > > "Jason Liu" <[EMAIL PROTECTED]> writes: > > > > > > > In general, how should database connections be handled between > > > parent and > > > > child processes? Can you establish database connections from within a > > > > handler? > > > > > > Absolutely. And using Abache::DBI caches the connection handle. > > > > > > -- > > > Dave Hodgkinson, http://www.hodgkinson.org > > > Editor-in-chief, The Highway Star http://www.deep-purple.com > > > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > > > - > > > the problem can also be resolved depending on the type of db used yes you can program your own levels of persistence finding a database that can do this for you can be a great help. postgres for instance has concurrent locking , on table , row and or column. using its Pg module instead of DBD::Pg and DBI handles the opening and closing of the connections too ;) making your own wrapper module ito interface with DBI or PG is a good thing to do as well i usually use something that does all the basic statement handling and db connect strings then i can just use PGForm; #my module to go to Pg PGform($db,$user,$password,$sqlstatement); and if a select returns a array of arrays else if insert update delete etc returns ok or error
RE: database access
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Greg Cope > Sent: Tuesday, November 07, 2000 10:53 AM > To: Jason Liu > Cc: [EMAIL PROTECTED] > Subject: Re: database access > > > Jason Liu wrote: > > > > Is Apache::DBI absolutely necessary if you want to establish persistent > > database connection per child? > > No you can write your own (its open source remember ;-) but why bother - > standing on the shoulders of giants etc Well, there is a section in the mod_perl guide on the subject here: http://perl.apache.org/guide/performance.html#Efficient_Work_with_Databases_ un
Re: database access
Jason Liu wrote: > > Is Apache::DBI absolutely necessary if you want to establish persistent > database connection per child? No you can write your own (its open source remember ;-) but why bother - standing on the shoulders of giants etc Greg > > Thanks, > > Jason > > > -Original Message- > > From: David Hodgkinson [mailto:[EMAIL PROTECTED]] > > Sent: Monday, November 06, 2000 5:10 AM > > To: Jason Liu > > Cc: [EMAIL PROTECTED] > > Subject: Re: database access > > > > > > "Jason Liu" <[EMAIL PROTECTED]> writes: > > > > > In general, how should database connections be handled between > > parent and > > > child processes? Can you establish database connections from within a > > > handler? > > > > Absolutely. And using Abache::DBI caches the connection handle. > > > > -- > > Dave Hodgkinson, http://www.hodgkinson.org > > Editor-in-chief, The Highway Star http://www.deep-purple.com > > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > > - > >
RE: database access
Thank you for the help everyone. Jason
RE: database access
HI Jason, Have a look at the source of Apache::DBI. An important part of the module is a ping method that ensures the connection handle (e.g., $dbh) doesn't go stale. You can write your own ping method by doing something like: $dbh->do("select 1") Which should return true (i.e., 1) if your handle is OK (unless your using Interbase where 'select 1' blows up!! ). We maintain persistent database connections per child without using Apache::DBI. Each child has its own lexically scoped database handle. We periodically ping the handle to make sure it is still alive -> if it isn't we create a new connection. This system works because we only talk to one database with one user. If you have lots of databases and/or database users you will need something more exotic. Regards, NIge
RE: database access
Is Apache::DBI absolutely necessary if you want to establish persistent database connection per child? Thanks, Jason > -Original Message- > From: David Hodgkinson [mailto:[EMAIL PROTECTED]] > Sent: Monday, November 06, 2000 5:10 AM > To: Jason Liu > Cc: [EMAIL PROTECTED] > Subject: Re: database access > > > "Jason Liu" <[EMAIL PROTECTED]> writes: > > > In general, how should database connections be handled between > parent and > > child processes? Can you establish database connections from within a > > handler? > > Absolutely. And using Abache::DBI caches the connection handle. > > -- > Dave Hodgkinson, http://www.hodgkinson.org > Editor-in-chief, The Highway Star http://www.deep-purple.com > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > - >
Re: database access
"Jason Liu" <[EMAIL PROTECTED]> writes: > In general, how should database connections be handled between parent and > child processes? Can you establish database connections from within a > handler? Absolutely. And using Abache::DBI caches the connection handle. -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire -
RE: database access
Thanks for reply. I need some information in the Oracle database for access control. We have some methods in our proprietary library for Oracle access. I have a file called GateKeeper.pm that contains the access handler, sub handler{ ... }. I placed the same code inside and outside of the handler subroutine in GateKeeper.pm. When the Apache starts up, those code outside of the handler subroutine got executed. It queries the Oracle and writes the result in a file. This part works fine. However, when I requests a file and the access handler is triggered, I got an error message says "Undefined subroutine &dbChannel::command called at /usr/up/papi/lib/PAPIDatabase.pm", which is a library file that works. In general, how should database connections be handled between parent and child processes? Can you establish database connections from within a handler? Jason > > "Jason Liu" <[EMAIL PROTECTED]> writes: > > > Hi, > > > > I can access oracle database from the main Apache process, but > not from any > > child processes. I am fairly new to this subject, can someone > give me some > > advice? > > What error are you getting? You looked in the error logs? What do you > mean by "main" apache process? > > -- > Dave Hodgkinson, http://www.hodgkinson.org > Editor-in-chief, The Highway Star http://www.deep-purple.com > Apache, mod_perl, MySQL, Sybase hired gun for, well, hire > - >
Re: database access
Are you trying to establish a persistent connection using Apache::DBI ? IIRC Oracle connections don't persist across forks so you should use the method described in the Apache::DBI manpage to establish one connection per child: Apache::DBI->connect_on_init($data_source, $username, $auth, \%attr) But I could be wrong as I don't use Oracle ! You might want to check out the mailing list archive at http://forum.swarthmore.edu/epigone/modperl/ HTH, Simon Wilcox. From "Jason Liu" <[EMAIL PROTECTED]>Date 6 November 2000 To [EMAIL PROTECTED] Time 12:02 Copy to (bcc: Simon Wilcox/BASE/WilliamsLea) Bcc Simon Wilcox/BASE/WilliamsLea Fax to Subject database access Hi, I can access oracle database from the main Apache process, but not from any child processes. I am fairly new to this subject, can someone give me some advice? Thanks in advance, Jason __ This email contains proprietary information some or all of which may be legally privileged. It is for the intended recipient only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or reply on this email.
Re: database access
"Jason Liu" <[EMAIL PROTECTED]> writes: > Hi, > > I can access oracle database from the main Apache process, but not from any > child processes. I am fairly new to this subject, can someone give me some > advice? What error are you getting? You looked in the error logs? What do you mean by "main" apache process? -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire -
database access
Hi, I can access oracle database from the main Apache process, but not from any child processes. I am fairly new to this subject, can someone give me some advice? Thanks in advance, Jason