On Tue, Oct 22, 2002 at 10:51:20AM -0400, Chris Faust wrote: > What is the best method of getting all SQL statements out of a > script and into a central location/file/module etc.
There is SQL::Snippet, available on CPAN. I have never used it myself. I have written extensions to XML::Generator::DBI which can parse SQL stored as XML. This allows you to, among other things, define parameters in the SQL which can then be used to generate HTML forms for the user. See http://xsql.sourceforge.net/. It is under active development, but usable in its present state. > 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). > > 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.. > > 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. This seems like a nice solution. > 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";) You could do this with your method by putting the variables you do not want interpolated when you set the hash values in single quotes. Then eval the sql before you use it to interpolate the values. Of course, for variable locations that allow placeholders, you should use them. dd -- David Dooling
