Problems with Class::DBI::AbstractSearch

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

Hello Monks, For the last few projects I have be using Class::DBI, Template Toolkit, and CGI::Application to build my web apps. Today was the first time I have tried to use Class::DBI::AbstractSearch, and it seems to be giving me trouble. Ok I’m going to get donw to it and show some code:

package Library::Data::Base;

use strict;
use warnings;
use Class::DBI::AbstractSearch;

use base qw(Class::DBI::Pg);
__PACKAGE__->set_db(Main =>
      'dbi:Pg:dbname=library;host=10.0.0.1, ‘user’, ‘pass’,{AutoCommit
+ => 1});


package Library::Data::Res;

use strict;
use warnings;

use base qw( Library::Data::Base );

__PACKAGE__->set_up_table('res');



package WebApp;

use strict;
use warnings;

use Library::Data::Res;

my @results   = Library::Data::Res->search_where(
                                information_type        =>      \@info
+_type,
                                format                  =>      \@form
+ats,
                                starting_point          =>      \@topi
+cs
        );
[download]

I get theses errors:

[Tue Apr  6 15:46:33 2004] [error] Library::Data::ResLibrary::Data::Re
+s can't SELECT id FROM
   res WHERE  ( ( ( format = ? ) ) AND  AND ( ( 
starting_point = ? ) ) )

: DBD::Pg::st execute failed: ERROR:  syntax error at or 
near "AND" at character 126 
[for Statement "SELECT id 
FROM   res WHERE  ( ( ( format = ? ) ) AND  AND ( ( starting_point = ?
+ ) ) )
"] at /usr/lib/perl5/site_perl/5.8.1/Ima/DBI.pm line 714.

errDBD::Pg::st execute failed: ERROR:  syntax error at or near "AND" a
+t character 126 [for Statement "SELECT id

FROM   res WHERE  ( ( ( format = ? ) ) AND  AND ( ( starting_point = ?
+ ) ) )
"] at /usr/lib/perl5/site_perl/5.8.1/Ima/DBI.pm line 714.

 at /usr/lib/perl5/site_perl/5.8.3/Class/DBI/AbstractSearch.pm line 26
[download]

I’m not exactly sure what I’m seeing here. It looks as if either its adding an extra AND or its leaving out one of the fields it should be searching on. This is the first time I’m trying to use Class::DBI::AbstractSearch so I could be doing something very wrong. Anyway, thanks for the help.

rlb3

Update: \@info_type,\@formats,\@topics are from a web form. I have tested it to check to see if data is getting in. it is.

Comment on Problems with Class::DBI::AbstractSearch Select or Download Code

Replies are listed 'Best First'.
Re: Problems with Class::DBI::AbstractSearch by jdtoronto (Prior) on Apr 07, 2004 at 03:15 UTC
From the errors you are getting returned it would seem that the values you are coming from your form are not making any sense at all! I would suggest a close and careful read of SQL::Abstract for that is the module provinding the abstraction. I think it is the 'shape' of your structures that are problematic. jdtoronto	[reply]
Re: Problemns with Class::DBI::AbstractSearch by matija (Priest) on Apr 06, 2004 at 21:05 UTC
You didn't show us the most crucial thing: What is in `@formats, @topics, @info_type`? From the looks of the error messages it looks to me like the arrays might be empty, or contain information that would confuse the method which constructs the queries. If you pass the array reference, you need to put in a list of values that you want to search for. If you want to put in a comparison operator and a value, than you need to pass a hash reference. What happens if you put in just one of those parameters, and print out it's values (to make sure the array is properly constructed) just before the call?	[reply]
Re: Problems with Class::DBI::AbstractSearch by simonm (Vicar) on Apr 07, 2004 at 16:33 UTC
`my @results = Library::Data::Res->search_where( information_type => \@info_type, format => \@formats, starting_point => \@topics ); SELECT id FROM res WHERE ( ( ( format = ? ) ) AND AND ( ( starting_p +oint = ? ) ) )` [download] It looks like the "missing piece" between the two ANDs is where your information_type argument would go. Might @info_type be empty? Try commenting that one line out to confirm that fixes it.	[reply] [d/l]