Dbi best practice question

[Matt Williamson <ma...@sanasecurity.com>]

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.

use Apache::DBI;

sub handler {
   
   # get db handle
   my $dbh = DBI->connect(DB_URL, DB_USER, DB_PASSWD ....

  #make some queries
  my $lastKey = $dbh->prepare("SELECT LAST_INSERT_ID()");

  #query
  $lastKey->execute;
 ($key) = $lastKey->fetchrow_array();

  # 
}

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?

Thanks in advance

Matt

Re: Dbi best practice question

[Perrin Harkins <pe...@elem.com>]

On 6/1/07, Jonathan Vanasco <jv...@2xlp.com> wrote:
> > 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...

A lot of it is covered here:
http://modperlbook.org/html/ch20_02.html

> 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 :)

Thanks!

Apache::DBI will actually do this for you automatically, but only if
you connect with autocommit turned off.  If you connect with
autocommit on and just turn it off for specific sections (as I do) you
will have to do the safety rollback yourself.

> > 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

Yes, don't do it on startup.  Instead, just use prepare_cached
everywhere you currently use prepare in your code.  It will cache the
statement handle for the next request to benefit from.  If Apache::DBI
notices a stale connection and reconnects, that clears this cache.

- Perrin

Re: Dbi best practice question

[Jonathan Vanasco <jv...@2xlp.com>]

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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -