SQL in files or File::Slurp?

[crusty_collins]

I have various Perl programs that access a database. My question is do I use File::Slurp to read the SQL into the program or do I just put the SQL into the Perl program?

I think it makes the program cleaner but I am worried about someone deleting the SQL files

File Slurp

   $sql = read_file("$ref->{config}->{SQLDIR}/USPS.sql");

   &getOrders($ref,$sql);

File in program

my $sql = "         select
            ORDER_ID,
            BATCH_ID,
            FIRST_NAME,
            LAST_NAME,
            TITLE,
            ADDRESS1,
            ADDRESS2,
            CITY,
            STATE,
            POSTAL_CODE,
            PHONE,
            EMAIL_ADDRESS,
            STORE_NUMBER,
            DELIVERY_METHOD,
            DELIVERY_ERROR,
            VALUE,
            CRM_REFERENCE,
            ASSIGNED_CARD_ID,
            CARD_ACTIVE_DATE,
            CARD_EXPIRED_DATE,
            REQUEST,
            ORDER_DATE,
            ITEM_CODE,
            CONVERT_ELIGIBLE,
            PROCESS_BATCH_ID,
            RESPONSE,
            PASSPHRASE,
            CONVERSION_SERIAL
            from INTEGRATION.OL_ORDER
            WHERE
            CLIENT_ID = 97
            AND BATCH_ID  =
                (SELECT * FROM (SELECT
                    BATCH_ID
                    FROM OL_ORDER
                    WHERE CLIENT_ID = 97
                    AND DELIVERY_METHOD ='USPS'
                    GROUP BY BATCH_ID
                    ORDER BY BATCH_ID DESC NULLS LAST)
                  WHERE ROWNUM <= 1)";
&getOrders($ref,$sql);

[MidLifeXis]

Are you also worried about people deleting the scripts?

I can see a benefit to the first if multiple scripts use the same SQL. See DBIx::PreQL for an alternative approach.

A third option is to step one layer back, load a module (use OurSystem::Orders), and call my @orders = OurSystem::Orders::getOrders. Store how you construct the data in the module (OurSystem::Orders), and don't let it near your scripts. Then if your back-end representation changes (different database, normalization of data, web service, whatever), you don't need to change all of your scripts. You also consolidate all of the DBI handling in one location.

[roboticus]

crusty_collins:

If I'm going to be updating the database, I like to just call stored procedures to do the work. This way there's a consistent method for performing various tasks and you don't have to worry about your different scripts from getting out of synchronization with each other. It also allows you to ensure that error checking is done consistently. That way it gets to be pretty easy and the code is short, so it doesn't get in the way:

my $ST = $DB->prepare("foo.bar.baz(?, ?, ?)");
$ST->execute($arg1, $arg2, $arg3);

Having said that--I frequently have to do a quickie reports or database fixit scripts with no stored procedures that perform the tasks I want to do. In those cases, I generally keep the SQL inline with the code, as I like to use placeholders. That way if I have to change the SQL, it's near the execute statement I need to update if I change the parameter bindings:

my $ST = $DB->prepare(<<EOSQL);
  UPDATE TABLE1 SET FOO=?, BAR=?, BAZ=?
  WHERE KEYCOL1=? AND KEYCOL2=?
EOSQL
$ST->execute($foo, $bar, $baz, $key1, $key2);

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[NetWallah]

It is frequently necessary to use SQL placeholders, or to use perl variables as values in SELECT statements, as in:

my $myClient = 97;
my $deliverymethod = 'USPS';

my $sql =<<"__SQL__";
SELECT
            ORDER_ID,
      ....
            CONVERSION_SERIAL
FROM  INTEGRATION.OL_ORDER
WHERE
            CLIENT_ID = $myClient
            AND BATCH_ID  =
                (SELECT * FROM (SELECT
                    BATCH_ID
                    FROM OL_ORDER
                    WHERE CLIENT_ID = $myClient
                    AND DELIVERY_METHOD ='$deliverymethod'
                    GROUP BY BATCH_ID
                    ORDER BY BATCH_ID DESC NULLS LAST)
                  WHERE ROWNUM <= 1)
__SQL__

So - this makes a case for in-lining the SQL.

[MidLifeXis]

DBIx::PreQL also handles that use case.

[crusty_collins]

Yes, but you can do that with the File::Slurp as well (untested)

my $myClient = 97;
my $deliverymethod = 'USPS';
my $sql = read_file("$ref->{config}->{SQLDIR}/USPS.sql");

   &getOrders($ref,$sql,$myClient,$deliverymethod);

   sub getOrders {
      my $ref = shift(@_);
      my $sql = shift(@_);
      my $myClient = shift(@_);
      my $deliverymethod = shift(@_);

      my $query = $dbh->prepare($sql);
      push(@ARRAY,$deliverymethod);
      # have to put this var in array twice
      #  because we reference it twice
      push(@ARRAY,$myClient);
      push(@ARRAY,$myClient);
      $query->execute(@ARRAY);
   return;
 }

Where USPS.sql is
Read more... (1364 Bytes)