Personally what I would do is have a single module that connects to the database, caches the handle, and returns it. Every other module just asks the single module to give them the appropriate connection, and gets it without worrying about whether a connect just happened or not. Then each module just has to never close it, and not leave any state.

State you ask? Well yes. For instance if you create #tables in Sybase (ie connection-specific temporary tables) you should also delete them when you are done. To be sure that that kind of state is properly cleaned up what you can do is have the functions that create state use ReleaseAction to make sure that the state will be cleaned up appropriately.

Of course that assumes that the authors of the modules can hold to a reasonable standard. To quote Larry Wall, Give people enough rope to hang themselves, and they'll usually figure out how not to, after several successes. It is your decision whether the people writing those modules have had enough successes already...

I think you're wrong about your cleanliness assesment.

I think it will be cleaner to pass them a DB handle, because then they don't care what DB they're operating on, and if the main code changes it so that 2 of them use one DB and 3 use another, you only have to change the main program. in short, think of the modules as "doing stuff to a database" rather than "doing stuff to database X", and think of main as "calling the modules on database X" -- because a different main could then use those same modules on a different database.

update: yes, but are they also passed what type of DB it is? it is perhaps better to pass them handles, so that one can use a BerkeleyDB and another use DBD::CSV ... and yes, you can pass that information, too, but a handle sums it all up, doesn't it?

that's my take, anyway.

That data is passed to the modules by the main code anyway. The modules connect, fetch data and close up. :)

Update: All data required for a clean connect is either passed along in constructor (User, Password, DB Type, DB Name, Port) or are Module vars (table name).

Greetz
Beatnik
... Quidquid perl dictum sit, altum viditur.

Have every module do a 'my $dbh = MyDBI->connect(...)' and in your MyDBI module, the first call actually connects while subsequent calls just reuse the same handle (stored perhaps in a package global variable).

The disconnect would have to be at the end of the application I suppose.

Update: I take it all (well, most of it) back. Use connect_cached() and just let every module connect, just don't disconnect until the end of the app.

Another update:Looking at the docs for connect_cached(), I'm not sure the author really recommends it, so maybe my first suggestion is the way to go for production code.

Altho connect_cached() looks nice, I'm still not sure I should be using it.
Programming the Perl DBI lists it as (NEW) and also states 'it can also cause problems and should be used with care. The exact behaviour of this method is liable to change'.

To answer your question (which you then took back), the number of requests made depends on the usage of the application.

Greetz
Beatnik
... Quidquid perl dictum sit, altum viditur.

I think I gave this solution about a year ago here before. {grin}

-- Randal L. Schwartz, Perl hacker