RE: Using q() to define a query

Greetings.

I have a DBI (DBD::Pg) application I'm building in mod_perl. My
queries tend to look something like the following.

my $sql = q(SELECT departure_date, eq.name AS equipment,
dp.full_city AS departure_city, ap.full_city AS arrival_city,
ca.name AS carrier_name, number
FROM jsjourneys
FULL OUTER JOIN jscarriers AS ca ON jsjourneys.carrier = ca.id
FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id
JOIN jsports AS dp ON jsjourneys.departure_port = dp.id
JOIN jsports AS ap ON jsjourneys.arrival_port = ap.id
ORDER BY departure_date);

And, then, I execute them as follows.

$dbh->selectall_arrayref($sql, { Slice => {} });

Which works quite well.

However, I'm concerned about $sql because when I output it to
Apache's
debug log, it looks like this:

[Fri Jan 11 03:49:09 2008] [debug] Log.pm(36): [client
192.168.171.80]
[JetSet] SELECT departure_date, eq.name AS equipment,\n dp.full_city
AS departure_city, ap.full_city AS arrival_city,\n ca.name AS
carrier_name, number\n FROM jsjourneys\n FULL OUTER JOIN
jscarriers AS ca ON jsjourneys.carrier = ca.id\n FULL OUTER JOIN
jsequipment AS eq ON jsjourneys.equipment = eq.id\n JOIN jsports
AS dp ON jsjourneys.departure_port = dp.id\n JOIN jsports AS ap
ON jsjourneys.arrival_port = ap.id\n ORDER BY departure_date

Notice the newline characters in there. If those were really in the
query, I can't imagine the database would run it, so I suppose
they're
an artifact of the combination of using q() to quote my query and
using Apache's logger to output it.

All this leads up to a pretty simple question: is using q() to quote
my queries a bad thing, and/or will it cause trouble in the future?

(As an aside, how do you guys quote your queries? I find that for
anything longer than about 60 characters, q() and '' and everything
else start to look horribly inelegant.)

I've tried several different ways, global variables, local variables, modules but, FWIW, I've found putting long scripts in a subroutine works pretty well from a maintenance standpoint: (This is from an older report. Now I encourage the poor guy who has to maintain my stuff to use ? instead of %s whenever practical)

sub some_descriptive_script_name {

return qq{
select 'ZIP' as "Type", a.zipresultcode "Result Code", e.errordesc "Description", count(*) "Total"
from psn_cbarlog a, certmas c, psn_erroralertmas e
where a.certno=c.certno
and e.errorcode = a.zipresultcode
and c.certcrtdate >= to_date('%s','MM-DD-YYYY')
and c.certcrtdate < to_date('%s','MM-DD-YYYY')
and c.certmode = 'P' %s
and c.tranid in (1,2,3,91,99)
group by a.zipresultcode, e.errordesc
union
select 'History' as "Type", a.historyresultcode "Result Code", e.errordesc "Description", count(*) "Total"
from psn_cbarlog a, certmas c, psn_erroralertmas e
where a.certno=c.certno
and e.errorcode = a.historyresultcode
and c.certcrtdate >= to_date('%s','MM-DD-YYYY')
and c.certcrtdate < to_date('%s','MM-DD-YYYY')
and c.certmode = 'P' %s
and c.tranid in (1,2,3,91,99)
group by a.historyresultcode, e.errordesc
union
select 'GIS' as "Type", a.gisresultcode "Result Code", e.errordesc "Description", count(*) "Total"
from psn_cbarlog a, certmas c, psn_erroralertmas e
where a.certno=c.certno
and e.errorcode = a.gisresultcode
and c.certcrtdate >= to_date('%s','MM-DD-YYYY')
and c.certcrtdate < to_date('%s','MM-DD-YYYY')
and c.certmode = 'P' %s
and c.tranid in (1,2,3,91,99)
group by a.gisresultcode, e.errordesc
};
}

I cluster all the subroutines together at the end of the program.

In webreports I also include the SQL in the generated HTML as a comment.

--
Stephen Carville <stephen@xxxxxxxxxxxxxx>
Systems Engineer
Land America
1.626.667.1450 X1326
#####################################################################
Dulce et decorum est pro patria mori.
Si alius est effectus is.




Relevant Pages

  • Re: Using q() to define a query
    ... FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id ... JOIN jsports AS dp ON jsjourneys.departure_port = dp.id ... I'm concerned about $sql because when I output it to Apache's ... queries a bad thing, and/or will it cause trouble in the future? ...
    (perl.dbi.users)
  • Using q() to define a query
    ... My queries tend to look something like the following. ... FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id ... JOIN jsports AS dp ON jsjourneys.departure_port = dp.id ... However, I'm concerned about $sql because when I output it to Apache's debug log, it looks like this: ...
    (perl.dbi.users)
  • Re: Relational question
    ... If one considers the question as purely a SQL question then, ... the two queries are equivalent but with further explanation ... Note that if this were an INNER JOIN we would only need to ... >> left outer join ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... "SQL Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... Even if I were to bypass the Hibernate generated SQL and roll my own, that did not work at all, it returned all rows unfiltered. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)