I've moved to a new database project using DBI and DBD::ODBC. I'm in the design phase now, and I've run up against an odd requirement. I'm well aware of the value of abstracting SQL from the Perl that calls it -- but I now have the requirement not to use Stored Procedures. I'm not allowed to change this requirement.

So, what I can't figure out is: what's the best way, barring stored procedure use, to abstract the SQL as much as possible away from my perl code?

Update 2004-09.Sep-30:
I have solved the problem using ideas given by repliers combined with some of my own. I thought the Monastery might be interested.

The methodology is simple:

1. Put SQL into separate files, named with query_name.sql.
2. Run this:

   use strict;
   use warnings;
   
   my %queries;
   
   for (@ARGV) {
      open my $FH, '<', $_ or die("failed opening $_ :$!");
      s'\.sql$'';
      while (chomp($queries{$_}.=<$FH>)) {}
   }
   
   # the PERLINST env var holds the location of our private
   # Perl Module store
   open my $MODULE, '>', "$ENV{PERLINST}/MyProject/SQL.pm"
     or die("Unable to open SQL module");
   
   print $MODULE q(
   package Private::MyProject::SQL;
   require Exporter;
   use vars (@ISA, @EXPORT);
   );
   
   print $MODULE '@EXPORT = qw(',join(' ',keys %queries),");\n";
   
   for (keys %queries) {
      print $MODULE 'use constant '.$_.' => q('.$queries{$_}.");\n";
   }
   
   print $MODULE '1;\n';
   close $MODULE;
   [download]

3. Pack code using PAR

The end result is that the SQL files get drafted into a module, which can be included in the solid .exe that PAR generates. The SQL maintainer updates the .sql files and runs a batch file which calls the above script, then packs with PAR. No perl is ever apparent to the SQL maintainer.

package Private::ProjectName::SQL;
require Exporter;
use warnings;
use strict;
use vars qw($VERSION @ISA @EXPORT);

$VERSION = 0.01;
@ISA = qw(Exporter);
@EXPORT = qw(sql_queryname1 sql_queryname2 ...);

use constant sql_queryname1 = qq(
SELECT RecordID FROM Management 
WHERE IQ > 40
AND Salary < (SELECT UngodlyAmount FROM References)
); #don't trim this line!

use constant sql_queryname2 =....

1;
[download]

Thank you, wise monks, for bestowing wisdom upon me.