How often do you change servers? What you describe below can be handled by editing the appropriate context / resource and restarting Tomcat. Perhaps more detail on the business requirement, rather then stating "I must define them on the fly" would enable us to give you a better solution.
-----Original Message----- From: Rob Gregory [mailto:[EMAIL PROTECTED] Sent: Sunday, January 22, 2006 5:41 PM To: 'Tomcat Users List' Subject: RE: Setting up connection pools "on the fly"... "One doesn't typically add new database servers "on the fly", and this option would require defining a new JNDI datasource for a new server" That's exactly what I need to be able to do... Is this possible on Tomcat start-up as it doesn't have to strictly on the fly just on start-up. I mainly need to remove the configuration details from the context.xml file and read these from the database before tomcat starts and creates the pools. Any help much appreciated. Rob -----Original Message----- From: Tim Lucia [mailto:[EMAIL PROTECTED] Sent: 22 January 2006 14:21 To: 'Tomcat Users List' Subject: RE: Setting up connection pools "on the fly"... The point of connection pooling is to eliminate the overhead of setting up and tearing down a (TCP, database, AAA) connection for every database transaction (typically, the web request in a web app.) This can add 100s or 1000s of milliseconds to every request, and is quite expensive. If you can architect your application so that there is a JNDI data source for each server (database server, instance, etc.) with its own pool of connections, you can always call setCatalog (http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html#setCatalog (java.lang.String)) on the connection returned from the pool. One doesn't typically add new database servers "on the fly", and this option would require defining a new JNDI datasource for a new server. Of course all catalogs processed in this way must be accesible to the user specified in the connnection pool properties. I would suggest setting the defaultCatalog property as well, so that you are not accidentally connected to the last-used catalog from the previous borrower of the connection. Tim -----Original Message----- From: Rob Gregory [mailto:[EMAIL PROTECTED] Sent: Sunday, January 22, 2006 8:23 AM To: 'Tomcat Users List' Subject: RE: Setting up connection pools "on the fly"... I also have the same requirement (to provide connection pools on the fly) but also need these pools available to Cocoon (so need them to map to the standard jdbc/mypool jndi location. Tomcat declares it's initial context as being readonly so I have no idea how to go about binding the pools I have created dynamically. Any pointers would be a great help. Thanks in advance Rob -----Original Message----- From: Alex Turner [mailto:[EMAIL PROTECTED] Sent: 20 January 2006 22:50 To: Tomcat Users List Subject: Re: Setting up connection pools "on the fly"... Maybe you don't even want a connection pool, rather a simple non-pooling datasource. If you generate a pool of connections for each and every database, you may end up with a great many open connections to your database server that hardly ever get used. I don't know too much about MySQL, but in most other databases, each connection has certain allocated resources, and the database typically limits the max connections. I have a similar situation, but I use a simple datasource, and a servlet to create a new datasource for each database on initialization based on a database entries. You can easily create a datasource on the fly in a servlet. I don't know if this is the 'right' answer, but it works for me. Alex On 1/20/06, Warrick Wilson <[EMAIL PROTECTED]> wrote: > I've got a project where there's a basic interface, with some basic > database access. However, when someone "signs up", then a new MySQL > database will be allocated for them. There's security associated with > it, and the user will have to log in to identify himself. > > How can I create a new connection pool for that new database for my > web app? My current pool is set up in context.xml - do I modify that > on the fly from my web app? > > What I'm doing currently is figuring out who the user is and which > database he's associated with, and when I do any database work, the > first statement is "USE <databasename>" to get the proper database. > > Is there a better approach? More like a proper approach? (Says he who > isn't sure he fully understands connection pools yet....) > > -- > Warrick Wilson > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]