> 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 <[EMAIL PROTECTED]>
Systems Engineer
Land America
1.626.667.1450 X1326
#####################################################################
Dulce et decorum est pro patria mori.
Si alius est effectus is.
