in reply to Class::DBI weirdness sql error

Class::DBI is often a mystery to me as well. It's heavily used at $Work as well, and I hate it with a passion. I avoid using Class::DBI as much as possible (basically, only using it if patching a piece of code that already uses it), preferring to write plain, clear SQL anytime.

I appreciate what Class::DBI is doing: writing trivial SQL - a handy tool for people who don't know any SQL. But it's hard to figure out what it is doing, and I find it almost impossible to use it to do something non-trivial. Certain things are impossible.

My advice: don't use Class::DBI. Writing SQL isn't hard. And useful database abstraction layers cannot be generalized anyway (as they are dependent on what's being stored anyway). Class::DBI isn't a database abstraction layer - it's just syntax.

Replies are listed 'Best First'.
Re^2: Class::DBI weirdness sql error
by perrin (Chancellor) on Mar 04, 2009 at 03:16 UTC
    Class::DBI isn't a database abstraction layer and its authors never intended it to be. It's just a library for saving some typing on the kind of repetitive SQL that you could write but shouldn't have to. On the projects where I've used it, I just switched over to manual SQL any time I needed to deal with something beyond single-table CRUD statements. If you don't expect too much from it, it can be a time-saver.
[reply]
      So they made a mistake in the description? :) Class::DBI - Simple Database Abstraction
[reply]
        If you read the archives of the mailings list, Schwern and Tony both talk about how it's not intended to hide anything about the database. They don't even like to call it an ORM.
[reply]
Re^2: Class::DBI weirdness sql error
by trwww (Priest) on Mar 04, 2009 at 01:05 UTC

    Hello,

    My advice: don't use Class::DBI. Writing SQL isn't hard.

    With all due respect, this is horrible advice.

    And useful database abstraction layers cannot be generalized anyway (as they are dependent on what's being stored anyway).

    Sure they can.

    Class::DBI isn't a database abstraction layer - it's just syntax.

    The words you are reading right now are not really words... they are just a sequence of light and dark pixels. By your reasoning, they aren't useful either. Perhaps not to you. But to me they are.

    To the OP: it looks like somehow you're double ->quote()ing your SQL parameters. Regardless of the exact problem, the general problem is your unique and untested way of using Class::DBI instead of Class::DBI itself. Step in to the debugger a level or two and I'm sure the problem will jump right out at you.

    Regards,

[reply]
      The words you are reading right now are not really words... they are just a sequence of light and dark pixels. By your reasoning, they aren't useful either.
      No, that's not what I said. Calling Class::DBI a database abstraction layer is calling a word processor a book writing abstraction layer. It isn't. It's just a different way of stringing letters into words and words into sentences. It still only manipulates words and letters for you - it doesn't deal with characters, plots or styles.

      Class::DBI doesn't abstract anything. It just provides a different syntax to get into a table. But a Class::DBI user needs to have an as good idea how entities are layed out in tables as someone writing plain SQL does. He may avoid writing SQL as long as keeps his application doing simple inserts/selects/updates/deletes, but nothing is abstracted.

      With a proper abstraction layer, a user of the layer doesn't have to know how the data is put into tables. Or whether an entity is laid out in one or more tables. And the data can be layed out differently (even spread over different databases) without the user of the layer knowing.

      And useful database abstraction layers cannot be generalized anyway (as they are dependent on what's being stored anyway).
      Sure they can.
      I've yet to see one. I've seen many database abstraction layers. Bad ones. Good ones. All geared towards the data stored, and needs of the application(s). But a 1-1 table-module mapping isn't an database abstraction layer. I won't deny it's a layer. But the database layout still shines through.
[reply]

        I'll concede that Class::DBI doesn't abstract the database perfectly. Not even close. But CDBI abstracts a database well enough to be extremely useful when used within its constraints. When I see someone suggesting otherwise, I feel compelled to respectfully object.

        To keep the talk technical, It seems that your main complaint is that CDBI "gets in the way..." Whenever CDBI is getting in your way, you simply do a:

        my $dbh = CDBI::SubClass->db_Main

        and you are back to doing plain database calls. So at the very least, it is a very handy database connection manager.

        Regards,

[reply]
[d/l]
      Thanks for the help.
      In the end it took some debugging to find the very very very silly mistake that I made. Here's a hint, it's in this piece... 
      # within FS::Model::Items->new();
$self->table('items');
$self->columns( 
    qw/item item_type name lng lat zip url phone fax address/
    );

      [download]
      Yea, my columns definition is wrong. It's expecting a hash with the id being 'All'. So, 
      # within FS::Model::Items->new();
$self->table('items');
$self->columns( All =>
    qw/item item_type name lng lat zip url phone fax address/
    );

      [download]
      Done. It works. Boo carelessness.
[reply]
[d/l]
[select]

        :0)

        Thanks for letting us know.

        It is surprising to me that you can use InsideOut with it.

        If you get a chance, please let us know how it works out!

        Regards,

[reply]
        You're not passing a hash; colums takes a list, of which the first element tells what kind of columns the rest are. If it were a hash, you'd would have had key value pairs like "item_type => 'name'", "lng => 'zip'", etc. Which I doubt you want.
[reply]

In Section Seekers of Perl Wisdom