Re: Dbi best practice question

[Jonathan Vanasco <suppressed>]

On Jun 1, 2007, at 7:47 PM, Matt Williamson wrote:

> Hi
>
> When using a MySQL database with modperl 2. I can find lots of docs on
> using DBI, but not much on what is the best practice...
>
> I am currently connecting, using the db and exiting on each  
> request, and
> relying on Apache::DBI to cache the connection. E.g.


well, the first thing i would change, is to add this:

	Apache::DBI->connect_on_init(
		$_db_config{'dbi'},
		$_db_config{'dbUser'},
		$_db_config{'dbPass'},
		$_db_config{'dbArgs'}
	);

that''ll instantiate a connection after the fork -- so you don't have  
the connection overhead on the first connect.
you still keep the DBI->connect -- this just preloads it.

you also might want to add a cleanup handler that just issues a  
rollback, which is something that  perrin often suggests, and I  
admittedly blindly follow.  i trust him on those things though :)

> If I understand Apache::DBI correctly, the DBI->connect picks up a
> cached connection. Is it possible to prepare queries in one place and
> reuse in all the child nodes? E.g. using prepare_cached. If so  
> where do
> I put that? In the startup file? In the same module?

i have no idea how to handle the prepared queries, but i will say this;
	i'm pretty sure that in mysql prepared queries work per connection  
( i know they do in pgsql ) -- so you wouldn't be able to do it in  
startup
	i blieve db connections close/respawn when a child dies / is born  
( ie maxrequests ) so you'll probably have to code for something to  
handle populating the statements.

// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -
|      FindMeOn.com - The cure for Multiple Web Personality Disorder
|      Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -
|      RoadSound.com - Tools For Bands, Stuff For Fans
|      Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -