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
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" [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
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
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: [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
- 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
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
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
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
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
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 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 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
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
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
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 ---
[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: 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
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
-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
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
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
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
Thank you for the help everyone. Jason
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 -
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
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
"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 -
