Hello All,
I've spent quite a bit of time looking through all the messages at
http://archive.develooper.com/dbi-users@;perl.org/
And have found some information, but nothing exactly what I was looking for,
so I thought I would try the list.
My question is 2 fold..
What is the best method of getting all SQL statements out of a script and
into a central location/file/module etc.
What is the best method of dealing with a situation where you want to have
different SQL based on a specific incoming param (such as a CGI param).
I came across a module on cpan called SQL::Catalog that can do what I'm
looking for but this results in putting the SQL statements in the DB which
results in 2 calls to the DB for every statement and I don't want to do
that.
The only way I have gotten this to work is storing my SQL within 2 hash of
hashes within a package and then based on a incoming CGI param I make
whichever statement hash I want the default..
For example, here is a snippet of the package that has the SQL statements:
%sql_statements_standard = (
display_search => {
select_countyid =>
"Select CountyId, County from County where StateId
= ?",
},
display_user_reg => {
select_all_from_user => "Select * from
User where username = ?",
},
%sql_statements_rental = (
display_search => {
select_countyid =>
"rentalsql",
},
display_user_reg => {
select_all_from_user => "rentalsql",
},
I'm breaking it up as $hash_type{sub in script}{sql statement}
Then at the start of my script I get all the SQL I want, for example:
if ($form_data{'sec_type'} eq 'rental') {
%sql_statements = %sql_statements_rental;
} else {
%sql_statements = %sql_statements_standard;
}
Then I just call $sql_statements{sub routine}{sql} and I don't have to have
a bunch of IF statements nor do I have to have a 2nd script just to take
care of the "rental" condition.
Although this does work, it doesn't seem to be the best solution, for
example I can't have something like:
"order by Priority desc, $sort limit $recno, $entries_per_page"; at the end
of the statement within the hash, it doesn't work (I have to add it within
my script, like my $sql_st =
$sql_statements{'simple_search_database'}{select_long_lat_listing} . "order
by Priority desc, $sort limit $recno, $entries_per_page";)
Is there a better way of doing this? I've been reading that mailing list and
checking the groups on google but coming up empty.
Thanks In Advance!!
-Chris
