Re: SQL in files or File::Slurp?

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__
[download]

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

Profanity is the one language all programmers know best.

Comment on Re: SQL in files or File::Slurp? Download Code

Replies are listed 'Best First'.
Re^2: SQL in files or File::Slurp? by MidLifeXis (Monsignor) on Aug 04, 2014 at 18:49 UTC
DBIx::PreQL also handles that use case. --MidLifeXis	[reply]
Re^2: SQL in files or File::Slurp? by crusty_collins (Friar) on Aug 04, 2014 at 21:55 UTC
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; }` [download] Where USPS.sql is Read more... (1364 Bytes)	[reply] [d/l] [select]