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.)

Thanks.

Colin

Re: Using q() to define a query

------=_Part_1173_18774293.1200024861249
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Jan 10, 2008 7:59 PM, Colin Wetherbee wrote:

> 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.
>

If you're referring to the newlines in the $sql string - I'd be astonished
if the DBMS did not handle them OK. If you're referring to the \n notation
in the log output, I'd assume those are interpolated by the Apache logging
module.

>
> 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?
>

It's fine...


> (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.)
>

q{}; q%%; q[];

On occasion, I've done evil things like q"" and qq'' -- it seemed like a
good idea at the time.



>
> Thanks.
>
> Colin
>



--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0914 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

------=_Part_1173_18774293.1200024861249--

Re: Using q() to define a query

I beleive most SQL parsers will ignore white space like that in an SQL
query. Normally single quotes in parameters are the bug bear of the the
SQL programmer,

ie

select name,rank,ser_no from sailors where name = O'Tool

It is alway good practice to use parameterized queries instead that you
prepared first

my $sql="select name,rank,ser_no from sailors where name =:name";
my $c=$db->prepare($sql);
$c->bind_param(":name","O'Tool");
$c->execute();

or
my $sql="select name,rank,ser_no from sailors where name =?";
my $c=$db->prepare($sql);
$c->execute("O'Tool");


as DBI and DBD driver will take care of the quotes for you and it
prevents any SQL injection attacks on your app.

Cheers

Colin Wetherbee wrote:
> 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.)
>
> Thanks.
>
> Colin

Re: Using q() to define a query

Hi,

On 11/01/2008, Colin Wetherbee wrote:

> (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.)

Can't comment on your main question, but on the secondary query-layout
topic, I find this works quite nicely:
$query2 = qq/SELECT MTR_REFERENCE FROM ALLOCATED_METERS,METERS /;
$query2 .= qq/WHERE ALM_MTR_ID = MTR_ID /;
$query2 .= qq/AND ALM_LP_ID = ( /;
$query2 .= qq/ SELECT LP_ID /;
$query2 .= qq/ FROM LOGGER_POINTS,ALLOCATED_METERS,METERS /;
$query2 .= qq/ WHERE LP_ID=ALM_LP_ID /;
$query2 .= qq/ AND MTR_ID=ALM_MTR_ID /;
$query2 .= qq/ AND MTR_REFERENCE = ? ) /;

(In a mono font the right hand /;'s align, for neatness, but that's optional).

Nice thing doing it this way is that it does not confuse the editor's
auto indent.

Re: Using q() to define a query

On Fri, Jan 11, 2008 at 12:14:03PM +0000, Victor Churchill wrote:
> On 11/01/2008, Colin Wetherbee wrote:
>
> > (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.)
>
> Can't comment on your main question, but on the secondary query-layout
> topic, I find this works quite nicely:
> $query2 = qq/SELECT MTR_REFERENCE FROM ALLOCATED_METERS,METERS /;
> $query2 .= qq/WHERE ALM_MTR_ID = MTR_ID /;
> $query2 .= qq/AND ALM_LP_ID = ( /;
> $query2 .= qq/ SELECT LP_ID /;
> $query2 .= qq/ FROM LOGGER_POINTS,ALLOCATED_METERS,METERS /;
> $query2 .= qq/ WHERE LP_ID=ALM_LP_ID /;
> $query2 .= qq/ AND MTR_ID=ALM_MTR_ID /;
> $query2 .= qq/ AND MTR_REFERENCE = ? ) /;
>
> (In a mono font the right hand /;'s align, for neatness, but that's optional).
>
> Nice thing doing it this way is that it does not confuse the editor's
> auto indent.

On the other hand, you can't easily copy-n-paste the query into other
tools, which I'd find much more frustrating. I'd also find the extra
line noise distracting. And when the query needs changing I'd find the
extra effort to maintain that layout would be a pain, literally.

I almost always use q{} or qq{} for multi-line strings.

(Using a bracketing character like {, (, [ etc. means that perl will
also take care of skipping any nested brackets within the string without
them beeding to be escaped.)

Tim.

Re: Using q() to define a query

Tim Bunce wrote:
> On Fri, Jan 11, 2008 at 12:14:03PM +0000, Victor Churchill wrote:
>> On 11/01/2008, Colin Wetherbee wrote:
>>
>>> (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.)
>> Can't comment on your main question, but on the secondary query-layout
>> topic, I find this works quite nicely:
>> $query2 = qq/SELECT MTR_REFERENCE FROM ALLOCATED_METERS,METERS /;
>> $query2 .= qq/WHERE ALM_MTR_ID = MTR_ID /;
>> $query2 .= qq/AND ALM_LP_ID = ( /;
>> $query2 .= qq/ SELECT LP_ID /;
>> $query2 .= qq/ FROM LOGGER_POINTS,ALLOCATED_METERS,METERS /;
>> $query2 .= qq/ WHERE LP_ID=ALM_LP_ID /;
>> $query2 .= qq/ AND MTR_ID=ALM_MTR_ID /;
>> $query2 .= qq/ AND MTR_REFERENCE = ? ) /;
>>
>> (In a mono font the right hand /;'s align, for neatness, but that's optional).
>>
>> Nice thing doing it this way is that it does not confuse the editor's
>> auto indent.
>
> On the other hand, you can't easily copy-n-paste the query into other
> tools, which I'd find much more frustrating. I'd also find the extra
> line noise distracting. And when the query needs changing I'd find the
> extra effort to maintain that layout would be a pain, literally.

Agreed. I'd rather not have my query broken up like that.

> I almost always use q{} or qq{} for multi-line strings.
>
> (Using a bracketing character like {, (, [ etc. means that perl will
> also take care of skipping any nested brackets within the string without
> them beeding to be escaped.)

That sounds good. I would think q{} would be the best choice for SQL,
since () and [] are common SQL characters. Not that it really matters
in terms of Perl functionality, but I'd imagine it's at least slightly
less confusing in the long run.

Thanks.

Colin

Re: Using q() to define a query

Jonathan Leffler wrote:
> On Jan 10, 2008 7:59 PM, Colin Wetherbee > > wrote:
> [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.
>
> If you're referring to the newlines in the $sql string - I'd be
> astonished if the DBMS did not handle them OK. If you're referring to
> the \n notation in the log output, I'd assume those are interpolated by
> the Apache logging module.

I'm somewhat surprised Apache didn't parse the newlines, though... which
made me wonder whether they were showing up as literal '\n' in the
query, too? Probably not.

Colin

Re: Using q() to define a query

Colin Wetherbee schreef:

> 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);
>
> (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.)


my $sql = <<'SQL';

SELECT
jo.departure_date AS departure
, eq.name AS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.name AS carrier_name
, jo.number

FROM
jsjourneys AS jo

FULL OUTER JOIN
jscarriers AS ca
ON jo.carrier = ca.id

FULL OUTER JOIN
jsequipment AS eq
ON jo.equipment = eq.id

JOIN
jsports AS dp
ON jo.departure_port = dp.id

JOIN
jsports AS ap
ON jo.arrival_port = ap.id

ORDER BY
departure

SQL

(why aren't these "FULL OUTER JOIN"s not just "JOIN"s?)


BTW, some editors recognize certain heredoc-delimiters (like SQL) and
switch language for color coding, autocompletion, etc.

--
Affijn, Ruud

"Gewoon is een tijger."

Re: Using q() to define a query

"Carville, Stephen" schreef:

> and c.certcrtdate >= to_date('%s','MM-DD-YYYY')

Aaargh, you should always use 'YYYY-MM-DD'.

--
Affijn, Ruud

"Gewoon is een tijger."

Re: Using q() to define a query

------=_Part_5247_32288708.1200150660151
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

2008/1/12 Dr.Ruud :

> "Carville, Stephen" schreef:
>
> > and c.certcrtdate >= to_date('%s','MM-DD-YYYY')
>
> Aaargh, you should always use 'YYYY-MM-DD'.
>
>
Except when reality beckons.


--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

------=_Part_5247_32288708.1200150660151--

Re: Using q() to define a query

Dr.Ruud wrote:
> Colin Wetherbee schreef:
>> (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.)
>
> my $sql = <<'SQL';
>
> SELECT
> jo.departure_date AS departure
> , eq.name AS equipment
> , dp.full_city AS departure_city
> , ap.full_city AS arrival_city
> , ca.name AS carrier_name
> , jo.number
>
> FROM
> jsjourneys AS jo
>
> FULL OUTER JOIN
> jscarriers AS ca
> ON jo.carrier = ca.id
>
> FULL OUTER JOIN
> jsequipment AS eq
> ON jo.equipment = eq.id
>
> JOIN
> jsports AS dp
> ON jo.departure_port = dp.id
>
> JOIN
> jsports AS ap
> ON jo.arrival_port = ap.id
>
> ORDER BY
> departure
>
> SQL
>
> (why aren't these "FULL OUTER JOIN"s not just "JOIN"s?)

They capture NULL values on both sides of the JOIN.

> BTW, some editors recognize certain heredoc-delimiters (like SQL) and
> switch language for color coding, autocompletion, etc.

I'd like to see nedit do that. I'd imagine vim supports that
functionality, but I prefer to use graphical editors unless I'm just
hacking something together quickly. What do you use that can do that?

Colin

RE: Using q() to define a query

From: Carville, Stephen"
> 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 {

While prototypes are generaly not recommended this is a perfect case
for the empty prototype

sub some_descriptive_script_name () {

which will basicaly turn the subroutine into a constant that can be
inlined.

> return qq{

Looks to me like you are not interpolating any variables there so q{}
would probably be better.

Of course it would definitely be preferred to use placeholders
instead if inserting the data into the SQL with sprinf(). It's safer
and more efficient (allows the database to cache execution plans).

If supported by the database, stored procedures would work even
better. Especially if you enforce a rule that all database access is
doen via stored procedures and therefore the DBA has a chance to see
all SQL used against his database and can optimize the queries and/or
indexes and schema.

Jenda
===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: Using q() to define a query

--GID0FwUMdk1T2AWN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2008-01-12 11:54:03 +0100, Dr.Ruud wrote:
> "Carville, Stephen" schreef:
>=20
> > and c.certcrtdate >=3D to_date('%s','MM-DD-YYYY')
>=20
> Aaargh, you should always use 'YYYY-MM-DD'.=20

While I prefer YYYY-MM-DD, too (and where is MM-DD-YYYY used, anyway?
Isn't that usually written as MM/DD/YYYY or MM.DD.YYYY?), it doesn't
make a difference here: The string is converted to a date type, and
then two date values are compared. That one of them was computed from a
string is irrelevant.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--GID0FwUMdk1T2AWN
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHiy36MdFfQa64PCwRAhaKAJsE0T/toRsI9RAGPuW4wIlCL3lokQCf T2pm
tEQA9aa7DiIjFB33QCpFpFA=
=IuTH
-----END PGP SIGNATURE-----

--GID0FwUMdk1T2AWN--

Re: Using q() to define a query

--eqp4TxRxnD4KrmFZ
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2008-01-12 11:51:42 +0100, Dr.Ruud wrote:
> Colin Wetherbee schreef:
> > my $sql =3D 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 =3D ca.id
> > FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment =3D eq.id
> > JOIN jsports AS dp ON jsjourneys.departure_port =3D dp.id
> > JOIN jsports AS ap ON jsjourneys.arrival_port =3D ap.id
> > ORDER BY departure_date);
> >
> > (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.)
>=20
>=20
> my $sql =3D <<'SQL';
>=20
> SELECT
> jo.departure_date AS departure
> , eq.name AS equipment
> , dp.full_city AS departure_city
> , ap.full_city AS arrival_city
> , ca.name AS carrier_name
> , jo.number
>=20
> FROM
> jsjourneys AS jo
[...]
> SQL

I almost never use here documents because they cannot be properly
indented:


sub foo {
some;
code;
here;
if (bla) {
more;
code;
here;
my $sql =3D <<'SQL';
SELECT
jo.departure_date AS departure
, eq.name AS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.name AS carrier_name
, jo.number
[...]
SQL
even;
more;
}
code;
here;
}

just looks terrible. Of course with SQL leading whitespace doesn't
matter so you can just indent the whole statement and just have the
dangling terminator at the left edge[1] but that doesn't work for
multiline strings in general. Putting here documents in a function of
their own as proposed by Stephen helps, but the indentation is still
inconsistent.

Since initial whitespace doesn't matter in SQL, I'd just write that as:

sub foo {
some;
code;
here;
if (bla) {
more;
code;
here;
my $sql =3D q{
SELECT
jo.departure_date AS departure
, eq.name AS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.name AS carrier_name
, jo.number
[...]
};
even;
more;
}
code;
here;
}

(actually, I'd put the commas at the end of the lines)

As an aside, the SPL programming language[2] allows the terminator of a
here document to be indented and to strip off everything up to and
including some character from each line, so that could be written like
this:

function foo() {
some;
code;
here;
if (bla) {
more;
code;
here;
var sql =3D >>SQL|
|SELECT
| jo.departure_date AS departure
|, eq.name AS equipment
|, dp.full_city AS departure_city
|, ap.full_city AS arrival_city
|, ca.name AS carrier_name
|, jo.number
|[...]
SQL;
even;
more;
}
code;
here;
}

> BTW, some editors recognize certain heredoc-delimiters (like SQL) and
> switch language for color coding, autocompletion, etc.

That's a neat feature of course and an argument for using
here-documents.

hp


[1] No, I don't think =BBmy $sql =3D <<' SQL';=AB is a good idea.

[2] http://www.clifford.at/spl


--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--eqp4TxRxnD4KrmFZ
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHiz9TMdFfQa64PCwRAmg6AJ9ab7FYqYZQzGiDNLSwJwKl4Z8MTgCg iL/8
FhQpmY51ChqloP+eq/x0mr8=
=tQZT
-----END PGP SIGNATURE-----

--eqp4TxRxnD4KrmFZ--

Re: Using q() to define a query

From: "Peter J. Holzer"
> As an aside, the SPL programming language[2] allows the terminator of a
> here document to be indented and to strip off everything up to and
> including some character from each line, so that could be written like
> this:
>
> function foo() {
> some;
> code;
> here;
> if (bla) {
> more;
> code;
> here;
> var sql = >>SQL|
> |SELECT
> | jo.departure_date AS departure
> |, eq.name AS equipment
> |, dp.full_city AS departure_city
> |, ap.full_city AS arrival_city
> |, ca.name AS carrier_name
> |, jo.number
> |[...]
> SQL;
> even;
> more;
> }
> code;
> here;
> }

That kinda defeats the purpose of heredocs, namely allowing me to
copy&paste some data into/out of the script without having to escape
or modify anything. If you do this and then need to test and tweak
the SQL in some UI provided by your database you have to go and strip
and later reintroduce the |s. Not for me, I'd rather have a few lines
that are not indented. They are colored differently by my editor so
they stand out anyway. And in this case I can indent them as much as
I like anyway so it's just the terminator ... who the heck cares. The
only time I care is when I increase the indentation of the block
containing the heredoc ... if I could convince my editor to not
indent inside heredocs and the terminator if I select more code I'd
be completely happy.

Jenda
===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: Using q() to define a query

--N1GIdlSm9i+YlY4t
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2008-01-14 13:07:17 +0100, Jenda Krynicky wrote:
> From: "Peter J. Holzer"
> > As an aside, the SPL programming language[2] allows the terminator of a
> > here document to be indented and to strip off everything up to and
> > including some character from each line, so that could be written like
> > this:
> >=20
> > function foo() {
> > some;
> > code;
> > here;
> > if (bla) {
> > more;
> > code;
> > here;
> > var sql =3D >>SQL|
> > |SELECT
> > | jo.departure_date AS departure
> > |, eq.name AS equipment
> > |, dp.full_city AS departure_city
> > |, ap.full_city AS arrival_city
> > |, ca.name AS carrier_name
> > |, jo.number
> > |[...]
> > SQL;
> > even;
> > more;
> > }
> > code;
> > here;
> > }
>=20
> That kinda defeats the purpose of heredocs, namely allowing me to=20
> copy&paste some data into/out of the script without having to escape=20
> or modify anything.

In most cases this works with q{} just the same (the only exception is
the sequence \\, which unfortunately represents only a single backslash
inside q{}.

> If you do this and then need to test and tweak the SQL in some UI
> provided by your database you have to go and strip and later
> reintroduce the |s.

Yes, that works for SQL, as I wrote, but not for multi-line strings in
general, where leading whitespace may matter (think of formatted text
output).

Before Clifford introduced that feature into SPL, we discussed several
formats. I think one of them included a "column 0 marker" in the first
line - something like that:

var sql =3D >>^SQL|
SELECT
jo.departure_date AS departure
, eq.name AS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.name AS carrier_name
, jo.number
[...]
SQL;

where everything to the left of the marker ("^" here) would be stripped
off. That would simplify cut'n'paste a bit. I don't remember why he
didn't do it that way, but one obvious problem with this approach is
that "to the left of" is ambiguous if tabs and spaces are mixed.

> Not for me, I'd rather have a few lines that are not indented.

As I wrote I prefer just using q{}. As you can see in my example, it has
all lines indented and you can simply cut and paste.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--N1GIdlSm9i+YlY4t
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHi2JqMdFfQa64PCwRApagAJsEVVuuRbwoBFvU2prhwQJjD7EK/wCZ AeSH
a/MDSmVbkPrBy6pz+zwYesk=
=vqqL
-----END PGP SIGNATURE-----

--N1GIdlSm9i+YlY4t--

RE: Using q() to define a query

> -----Original Message-----
> From: Peter J. Holzer [mailto:hjp@wsr.ac.at]
> Sent: Monday, January 14, 2008 1:40 AM
> To: dbi-users@perl.org
> Subject: Re: Using q() to define a query
>=20
> On 2008-01-12 11:54:03 +0100, Dr.Ruud wrote:
> > "Carville, Stephen" schreef:
> >
> > > and c.certcrtdate >=3D to_date('%s','MM-DD-YYYY')
> >
> > Aaargh, you should always use 'YYYY-MM-DD'.
>=20
> While I prefer YYYY-MM-DD, too (and where is MM-DD-YYYY used, anyway?
> Isn't that usually written as MM/DD/YYYY or MM.DD.YYYY?), it doesn't
> make a difference here: The string is converted to a date type, and
> then two date values are compared. That one of them was computed from
a
> string is irrelevant.

That was a legacy format I inherited from the previous web report
writer. He wrote everything as JSP's and apparently someone had taught
him that MM-DD-YYYY was the proper format for dates in Oracle.
Consequently all his SQL and all his web forms used it. I inherited it.

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

Re: Using q() to define a query

From: "Peter J. Holzer"
> On 2008-01-14 13:07:17 +0100, Jenda Krynicky wrote:
> > From: "Peter J. Holzer"
> > > As an aside, the SPL programming language[2] allows the terminator of a
> > > here document to be indented and to strip off everything up to and
> > > including some character from each line, so that could be written like
> > > this:
> > >
> > > function foo() {
> > > some;
> > > code;
> > > here;
> > > if (bla) {
> > > more;
> > > code;
> > > here;
> > > var sql = >>SQL|
> > > |SELECT
> > > | jo.departure_date AS departure
> > > |, eq.name AS equipment
> > > |, dp.full_city AS departure_city
> > > |, ap.full_city AS arrival_city
> > > |, ca.name AS carrier_name
> > > |, jo.number
> > > |[...]
> > > SQL;
> > > even;
> > > more;
> > > }
> > > code;
> > > here;
> > > }
> >
> > That kinda defeats the purpose of heredocs, namely allowing me to
> > copy&paste some data into/out of the script without having to escape
> > or modify anything.
>
> In most cases this works with q{} just the same (the only exception is
> the sequence \\, which unfortunately represents only a single backslash
> inside q{}.

And with the exception of an unballanced { or } which causes the
literal to end either too soon or too late.

Unlikely to occure in SQL I admit, not so in other things. Besides
the only difference as far as formatting goes is the end marker. With
heredoc it has to be at the beginning of a line, in case of q{} it
may be anywhere. If the leading whitespace is meaningfull in whatever
it is you are quoting, you have exactly the same problem with here-
docs and q{}.

Jenda
===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery