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"; }
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
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);
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
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?
In reply to Escaping and quoting ????? by pgduke65
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |