Well:

1. I'm also using it for "actual objects with properties that I change and save" - elsewhere;
2. indeed I had arrived at Ima::DBI myself, actually one of the links in my first posts points to it;
3. unfortunately I'm not much familiar with any of "these technologies" - but I'm slowly learning...

WRT the last point in particular: do you mean that I can use DBI methods directly on my objects? All I know is that our "top level" class, from which all other ones do inherit is a

use base qw/Class::DBI::Oracle Class::DBI::Cacheable/;
[download]

and I find that that provides me a convenient way to access and possibly write data hiding the underlying db details, which is good since I know Perl far better than I know databases themselves.

In short: I'd prefer to keep lo and behold as far as I can get it to work. Accessing data through methods is fine and since I can have my own objects incorporate callbacks that are actually closures around suitable lexical variables, that keeps me fine as the resulting code is clear and maintainable and easy to understand and nicely "encapsulated" (not necessarily in the strict OO acceptation) for my tastes.

I was saying you could use connect_cached and prepare_cached to do everything shown in your example. If you make use of Class::DBI in the normal way elsewhere, that wouldn't be a good solution.

Regarding what you're doing with set_sql here, this is also pretty unusual. The set_sql stuff is supposed to be part of the class definition and it adds permanent class methods to your CDBI module. Changing these on the fly means you probably should be doing this part in some other way, like using DBI directly.

I don't quite understand why you're overwriting this query. Are you trying to have subclasses that all define this query differently?

Regarding what you're doing with set_sql here, this is also pretty unusual. The set_sql stuff is supposed to be part of the class definition and it adds permanent class methods to your CDBI module. Changing these on the fly means you probably should be doing this part in some other way, like using DBI directly.

I must say that, albeit in my ignorance, I had a perception that this was the case - i.e.: I didn't have the slightest idea of what the solution may have been, but I understand what is the problem.

I don't quite understand why you're overwriting this query. Are you trying to have subclasses that all define this query differently?

Indeed I'm not overwriting it. I do not want to inadvertently overwrite it. That is the problem I refer to above.

Now, your suggestion which I'm understanding more and more is to skip set_sql() altogether, since I have other ways to get the only thing that it really buys me, that avoid its side effects (and I take this to be the heart of your comment to my code). For the moment I have devised a patch. As time permits and I become more confident with this stuff, I'll modify all existing modules written in this style at once.

Yes, you can use DBI directly by getting the database handle. This can be done by calling class->db_Main(). You can use it exactly like you would use regular DBI.

sub foo
{
  my $class = shift;
  my $dbh   = $class->db_Main();
  my $sth   = $dbh->prepare_cached("SELECT ...");

  $sth->execute(...);
  #... usual DBI stuff
}
[download]

If you then want your return to be objects from the data you retrieved, you may want to look into sth_to_objects() or construct() methods.