Escaping and quoting ?????

pgduke65 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!

I am in need of assistance trying to use a system call to collect some output. The problem is that the database I am querying has table names in the form $hist_plan_prolog_1. I am using a vendor supplied query tool. The tool requires that the tables be enclosed in double quotes due to the existence of the $ in the name.

I am trying to run a query against the database and cannot seem to get the quoting correct? Below is the code and sample output:

# --------------------------------------------------------------------
+---------
# Start processing.
# --------------------------------------------------------------------
+---------

# Validation.
if ($StartTime eq "")       { Error($INVALID_START_DATE);   }
if ($EndTime eq ""  )       { Error($INVALID_END_DATE);     }
unless(IsDate($StartTime))  { Error($INVALID_START_DATE);   }
unless(IsDate($EndTime))    { Error($INVALID_END_DATE);     }


$SQL = "";
$SQL = $SQL . "SELECT ";
$SQL = $SQL . "to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIM
+E, ";
$SQL = $SQL . "to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME,
+ ";
$SQL = $SQL . "to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME, "
+;
$SQL = $SQL . "to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME,
+ ";
$SQL = $SQL . "pp.SESSIONID, ";
$SQL = $SQL . "pp.PLANID ";
$SQL = $SQL . "from ";
$SQL = $SQL . "\\\$hist_plan_prolog_1 pp ";
$SQL = $SQL . "where ";
$SQL = $SQL . "pp.SUBMITTIME between '2014-06-26 05:00' and '2014-06-2
+6 08:00:00' ";
$SQL = $SQL . "order by ";
$SQL = $SQL . "pp.SUBMITTIME";

print "[SQL]:\n\n\n$SQL\n\n";

@Output = qx { /nz/kit/bin/nzsql -d histdb  -c "$SQL" };
$RetCode=$?;
chomp(@Output);

foreach ( @Output ) { print "$_\n"; }
[download]

Output:

SELECT to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME, to_ch
+ar(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME, to_char(pp.PREPTIM
+E, 'YYYY-MM-DD HH:MM:SS') PREPTIME, to_char(pp.STARTTIME, 'YYYY-MM-DD
+ HH:MM:SS') STARTTIME, pp.SESSIONID, pp.PLANID from \$hist_plan_prolo
+g_1 pp where pp.SUBMITTIME between '2014-06-26 05:00' and '2014-06-26
+ 08:00:00' order by pp.SUBMITTIME

ERROR:  'SELECT to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTI
+ME, to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME, to_char(p
+p.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME, to_char(pp.STARTTIME, 'Y
+YYY-MM-DD HH:MM:SS') STARTTIME, pp.SESSIONID, pp.PLANID from $hist_pl
+an_prolog_1 pp where pp.SUBMITTIME between '2014-06-26 05:00' and '20
+14-06-26 08:00:00' order by pp.SUBMITTIME'
error                                                                 
+                                                                     
+                                                                     
+                                                             ^ found 
+"$" (at char 261) expecting an identifier found a keyword
[download]

I have tried a number of ideas to surround the table names with double quotes and have been miserably unsuccessful. Any assistance to get the $hist_plan_prolog_1 into double quotes would be very much appreciated. Thank you.

UPDATE: Thsnks for the reply. I have modified the code as follows:

my $SQL1=<<"_SQL1_";
SELECT
    to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME,
    to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME,
    to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME,
    to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME,
    to_char(pe.ENDTIME, 'YYYY-MM-DD HH:MM:SS') ENDTIME,
    (DATE_PART('second', pe.ENDTIME - pp.STARTTIME )) Seconds_Executio
+n,
    pp.SESSIONID,
    pp.PLANID,
    sp.SESSIONUSERNAME,
    pp.ESTIMATEDCOST,
    pp.ESTIMATEDDISK,
    pp.ESTIMATEDMEM,
    pp.TOTALSNIPPETS,
    pe.RESULTROWS,
    pp.NPSID,
    pp.NPSINSTANCEID,
    pp.OPID,
    pp.LOGENTRYID
from
    '\$hist_nps_1' ns,
    '\$hist_session_prolog_1' sp,
    '\$hist_plan_prolog_1' pp,
    '\$hist_plan_epilog_1' pe
where
    ns.NPSID = pp.NPSID
and ns.NPSID = pe.NPSID
and ns.NPSID = sp.NPSID
and sp.SESSIONID = pp.SESSIONID
and sp.SESSIONID = pe.SESSIONID
and pp.OPID = pe.OPID
and pp.SUBMITTIME between '$StartTime' and '$EndTime'
order by
    pp.SUBMITTIME
_SQL1_

@Output = qx { /nz/kit/bin/nzsql -d histdb  -c "$SQL1" };
$RetCode=$?;
chomp(@Output);
[download]

I run the script as follows: ./NZQueryHistoryExtract.pl '2014-06-26 05:00:00' '2014-06-26 08:00:00' and the output is as follows:

ERROR:  'SELECT
        to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME,
        to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME,
        to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME,
        to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME,
        to_char(pe.ENDTIME, 'YYYY-MM-DD HH:MM:SS') ENDTIME,
        (DATE_PART('second', pe.ENDTIME - pp.STARTTIME )) Seconds_Exec
+ution,
        pp.SESSIONID,
        pp.PLANID,
        sp.SESSIONUSERNAME,
        pp.ESTIMATEDCOST,
        pp.ESTIMATEDDISK,
        pp.ESTIMATEDMEM,
        pp.TOTALSNIPPETS,
        pe.RESULTROWS,
        pp.NPSID,
        pp.NPSINSTANCEID,
        pp.OPID,
        pp.LOGENTRYID
from
        '' ns,
        '' sp,
        '' pp,
        '' pe
where
        ns.NPSID = pp.NPSID
and     ns.NPSID = pe.NPSID
and ns.NPSID = sp.NPSID
and sp.SESSIONID = pp.SESSIONID
and sp.SESSIONID = pe.SESSIONID
and pp.OPID = pe.OPID
and pp.SUBMITTIME between '2014-06-26 05:00:00' and '2014-06-26 08:00:
+00'
order by
        pp.SUBMITTIME
'
error                                                                 
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                                                     
+                                   ^ found "'" (at char 580) expectin
+g an identifier found a keyword
[download]

Now, I am really confused as to what is going on? I used double quotes on the here document to allow for the dates to be passed on the command line. I thought I understood the differences between single and double quotes from an interpolation perspective? But the output below seems to contradict what I understood?

Comment on Escaping and quoting ????? Select or Download Code

Replies are listed 'Best First'.
Re: Escaping and quoting ????? by NetWallah (Canon) on Jul 03, 2014 at 00:56 UTC
If "$hist_plan_prolog_1" is a table name, and NOT a perl variable, the best bet is to use non-interpolating single quotes, and NOT escape the $. But the query would be more readable if you used "heredoc" syntax. here is a sample I use: `my $sql=<<"_SQL_"; SELECT cl_plugins.pid_number as pid, plugin_catalog.pid AS plugin_ +name, plugin_catalog.description AS name, plugin_catalog.version, bui +ld FROM cl_plugins, plugin_catalog WHERE cl_plugins.cid::text = '$proxy->{CID}' AND cl_plugins.pid_number = plugin_catalog.pid_number AND cl_plugins.version::text = plugin_catalog.version::text; _SQL_` [download] In my case, I DO use interpolation, and the string does expand the reference to $proxy->{CID}. Your query could be: `my $sql=<<"__SQL__"; SELECT to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME, to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME, to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME, to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME, pp.SESSIONID, pp.PLANID from '\$hist_plan_prolog_1' pp where pp.SUBMITTIME between '2014-06-26 05:00' and '2014-06-26 08:00:0 +0' order by pp.SUBMITTIME __SQL__` [download] This allows for the potential introduction of perl variables, where you would NOT escape the $. What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against? -Larry Wall, 1992	[reply] [d/l] [select]
Re^2: Escaping and quoting ????? by AnomalousMonk (Archbishop) on Jul 03, 2014 at 01:07 UTC
`my $sql=<<"__SQL__";` Use of single-quote delimiters would avoid double-quotish interpolation entirely, with no need for `\` (backslash) escaping: `my $sql=<<'__SQL__';` Please see here-docs in Quote and Quote-like Operators.	[reply] [d/l] [select]
Re^2: Escaping and quoting ????? by pgduke65 (Acolyte) on Jul 03, 2014 at 02:42 UTC
I have updated my original post with the revised code that to use a here document as suggested. But the output seems odd to me? I do not understand why the table names are empty?	[reply]
Re^3: Escaping and quoting ????? by thomas895 (Deacon) on Jul 03, 2014 at 03:56 UTC
In that case, `$hist_plan_prolog_1` does seem to be a variable name. In that case, use NetWallah's second example, but remove the `\` before the variable name. ~Thomas~ "Excuse me for butting in, but I'm interrupt-driven..."	[reply]
Re^3: Escaping and quoting ????? by poj (Abbot) on Jul 03, 2014 at 09:22 UTC
"The tool requires that the tables be enclosed in double quotes due to the existence of the $ in the name." Try `"\$hist_nps_1" ns, "\$hist_session_prolog_1" sp, "\$hist_plan_prolog_1" pp, "\$hist_plan_epilog_1" pe` [download] poj	[reply] [d/l]
Re: Escaping and quoting ????? by NetWallah (Canon) on Jul 03, 2014 at 12:59 UTC
Your statement `@Output = qx { /nz/kit/bin/nzsql -d histdb -c "$SQL1" };` [download] causes the shell to re-interpolate the contents of $SQL, because you use double quotes. You could either pass single quotes to the shell, or add another "\\" before each dollar sign in the contents of $SQL. I'd suggest: `@Output = qx { /nz/kit/bin/nzsql -d histdb -c '$SQL1' };` [download] What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against? -Larry Wall, 1992	[reply] [d/l] [select]
Re^2: Escaping and quoting ????? by pgduke65 (Acolyte) on Jul 03, 2014 at 16:29 UTC
Thanks for the clarification on the problem. I had forgotten to change the quotes from doubles to singles for the -c argument. It worked like a charm. Thank you all for the help. Perl Monks are awesome!	[reply]
Re: Escaping and quoting ????? by perlfan (Parson) on Jul 03, 2014 at 11:44 UTC
Use `q{}`. Just don't go `eval`'ing it. my $SQL1=q{SELECT to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME, to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME, to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME, to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME, to_char(pe.ENDTIME, 'YYYY-MM-DD HH:MM:SS') ENDTIME, (DATE_PART('second', pe.ENDTIME - pp.STARTTIME )) Seconds_Executio +n, pp.SESSIONID, pp.PLANID, sp.SESSIONUSERNAME, pp.ESTIMATEDCOST, pp.ESTIMATEDDISK, pp.ESTIMATEDMEM, pp.TOTALSNIPPETS, pe.RESULTROWS, pp.NPSID, pp.NPSINSTANCEID, pp.OPID, pp.LOGENTRYID from $hist_nps_1 ns, $hist_session_prolog_1 sp, $hist_plan_prolog_1 pp, $hist_plan_epilog_1 pe where ns.NPSID = pp.NPSID and ns.NPSID = pe.NPSID and ns.NPSID = sp.NPSID and sp.SESSIONID = pp.SESSIONID and sp.SESSIONID = pe.SESSIONID and pp.OPID = pe.OPID and pp.SUBMITTIME between $StartTime and $EndTime order by pp.SUBMITTIME}; [download]	[reply] [d/l] [select]