Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

How do I build a SQL statement in Class::DBI programmatically

by rlb3 (Deacon)
on Dec 08, 2004 at 22:15 UTC ( #413348=perlquestion: print w/ replies, xml ) Need Help??
rlb3 has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I'm having some trouble building an SQL statement from a
web form. I'm using Class::DBI's set_sql method to create and new method
that changes everytime through a loop.
my $tables = [ qw/ starting_point_topics related_topics other_topics / + ]; my $results = {}; foreach my $table (@{$tables}) { my $query = qq{ SELECT res.* FROM res,$table,topics WHERE $query_t +emp ORDER BY `rank` DESC }; Library::Data::Res->set_sql(full_text => $query); $results->{$table} = Library::Data::Res->search_full_text; }
A full SQL statement would look like this:
SELECT res.* FROM res,starting_point_topics,topics WHERE ((starting_point_topics.res = res.id) AND (starting_point_topics.topic = topics.id) AND (topics.name LIKE '%'))
The starting_point_topics would change as it loops and WHERE statement will change also but this is the one i'm testing right now. The error i'm getting is:
search_full_text() already exists at /data/web/perl/lib/Library/Result +s.pm line 95 ORDER BY `rank` DESC SELECT res.* FRO +M res,related_topics,topics WHERE ((starting_point_topics.res = +res.id) AND (starting_point_topics.topic = + topics.id) AND (topics.name LIKE '%')) AND ((related_topics.res = res.id) AND (related_topics.topic = topics +.id) AND (topics.name LIKE '%'))


I 'm not sure why the table names in the where statement are not changing.

rlb3

Comment on How do I build a SQL statement in Class::DBI programmatically
Select or Download Code
Re: How do I build a SQL statements in Class::DBI programmatically
by perrin (Chancellor) on Dec 08, 2004 at 22:31 UTC
    Don't do that. The set_sql() method is for class data that never changes. If you want to build statements on the fly, just grab the $dbh and use prepare_cached(). You can hand the results off to $sth_to_objects. See the Ima::DBI section of the Class::DBI docs for an example.
      Agreed - but in simpler terms: Class::DBI is meant to help you more easily do the easy things. It allows you to use objects to do simple interactions with your database. However, when you need more complex queries, it's better to just write them by hand.
        rb3 is already writing them by hand. The problem is the use of set_sql, which is not meant for this.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://413348]
Approved by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (16)
As of 2014-08-22 16:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (161 votes), past polls