vansance has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I'm getting a SQL error when using Class::DBI, and it's pretty baffling to me. I can't tell if I'm doing something wrong, or if I am missing a dependency or what is up. Here's the error, 
FS::Model::Items can't SELECT 
FROM   items
WHERE  address = ? AND zip = ?
: DBD::mysql::st execute failed: You have an error in your SQL syntax;
+ check the manual that corresponds to your MySQL server version for t
+he right syntax to use near 'FROM   items
WHERE  address = '170 W. 15th St.' AND zip = '10011'' at line 2 [for S
+tatement "SELECT 
FROM   items
WHERE  address = ? AND zip = ?
" with ParamValues: 0='170 W. 15th St.', 1='10011'] at /Library/Perl/5
+.8.8/DBIx/ContextualFetch.pm line 52.
 at /Library/Perl/5.8.8/Class/DBI/Search/Basic.pm line 169

[download]
Here's how i define the table in the objects initialization... 
# within FS::Model::Items->new();
$self->table('items');
$self->columns( 
    qw/item item_type name lng lat zip url phone fax address/
    );

[download]
And then this is the line from the object method that generates the error... 
# within FS::Model::Items->get_info
my $item = $self->search( 
    address => $item_info->{address}, zip => $item_info->{zip}, 
    );

[download]
It all comes together with something like this... 
my $item_obj = FS::Model::Items->new();
my $res_obj  = $item_obj->get_info($item_info);

[download]
The Items object is an InsideOut object which it inherits from my DBI Module which inherits from Class::DBI; So... 
package FS::Model::Items;

use Object::InsideOut qw(FS::Model::DBI);

[download]
and then... 
package FS::Model::DBI;
use base ( Class::DBI );

use Object::InsideOut;

[download]
I'm running on OS X 10.5. From the error, it seems like Class::DBI isn't trying to select any columns...but I really can't understand why. Any ideas? Thanks.

Replies are listed 'Best First'.
Re: Class::DBI weirdness sql error
by perrin (Chancellor) on Mar 03, 2009 at 21:53 UTC
    The Items object is an InsideOut object which it inherits from my DBI Module which inherits from Class::DBI; So...

    I don't think you can do that. Class::DBI is built with Class::Accessor, which makes assumptions about the data structure of its objects. You can't change it in class that inherit from it.

[reply]
Re: Class::DBI weirdness sql error
by JavaFan (Canon) on Mar 03, 2009 at 21:54 UTC
    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.

[reply]
      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]

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

Back to Seekers of Perl Wisdom