Trying to search using Class::DBI

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

When I try to do a basic search against a SQLite db it fails... bad sql query. Any ideas why this fails or a work around? Here's the code from the perl module of DB functions -

package IssueBot::Channels;
use base 'IssueBot::DBI';
__PACKAGE__->table( "channel_prefs" );
__PACKAGE__->columns( ALL => qw(idno channel channelpass loginon greet
+on active subscriber_ids server_id) );
__PACKAGE__->add_searcher( search  => "Class::DBI::Search::Basic" );
[download]

This is the command that fails -

use DBD::SQLite;      
use Class::DBI;
use IssueBot::DBI;
use IssueBot::db_functions;
IssueBot->connection("dbi:$db_type:$db_name");

my $true='true';
my $channel_it = IssueBot::Channels->search( active => $true, subscrib
+er_ids => '10000' );
[download]

Here's the error I'm getting -

DBD::SQLite::db prepare_cached failed: near "FROM": syntax error(1) at
+ dbdimp.c line 271 [for Statement "SELECT 
FROM   channel_prefs
WHERE  active = ? AND subscriber_ids = ?
"] at /usr/local/lib/perl5/site_perl/5.8.8/Ima/DBI.pm line 398.
[download]

I also tried this -

my @active_channels = IssueBot::Channels->retrieve_from_sql(qq{
    active = 'true' AND
    subscriber_ids like "%10000%"
  });
[download]

Error from above command -

DBD::SQLite::db prepare_cached failed: near "FROM": syntax error(1) at
+ dbdimp.c line 271 [for Statement "SELECT 
FROM   channel_prefs
WHERE  
    active = 'true' AND
    subscriber_ids like "%10000%"
  
"] at /usr/local/lib/perl5/site_perl/5.8.8/Ima/DBI.pm line 398.
[download]

Comment on Trying to search using Class::DBI Select or Download Code

Replies are listed 'Best First'.
Re: Trying to search using Class::DBI by moritz (Cardinal) on May 23, 2008 at 18:36 UTC
The SQL has invalid syntax because the column list is empty (normally it should be `SELECT column1, column2, col3 FROM ...`). That suggests that the column setup may not have worked, but I don't know Class::DBI (and your local class hierarchy) good enough to find the mistake.	[reply] [d/l]
Re: Trying to search using Class::DBI by jdrago_999 (Hermit) on May 23, 2008 at 18:40 UTC
If 'IssueBot::DBI' inherits from Class::DBI::SQLite (instead of from Class::DBI directly) then your IssueBot::Channels class could look like this: `package IssueBot::Channels; use strict; use warnings 'all'; use base 'IssueBot::DBI'; __PACKAGE__->set_up_table('channel_prefs'); 1;# return true` [download] Everything *should* just work. You might hear some complaining about "statement handle still active for query blah blah blah..." but that's it.	[reply] [d/l]
Re^2: Trying to search using Class::DBI by swares (Monk) on May 28, 2008 at 20:19 UTC
Thanks, using Class::DBI::SQLite directly makes things easier and eliminates the error I was getting. Then I got a no such column error: `DBD::SQLite::db prepare_cached failed: no such column: true(1) at dbdi +mp.c line 271 [for Statement "SELECT id FROM channel_prefs WHERE active = true "] at /usr/local/lib/perl5/site_perl/5.8.8/Ima/DBI.pm line 398.` [download] Looks like most of my other issues are mostly due to looking at differing examples then hard coding the data (poorly) into the initial code. Using place holders and/or quoting the key values properly eliminates this column not found problem. I think improper quoting is making it use the key and value together (keeping the value) for the table name.	[reply] [d/l]