Re^3: CGI-SQL Query Issue

Put this sql in a file called query.sql and put it in the cgi-bin.

 
set linesize 100
set verify off
select date_time_out,order_id,error_message,customer_id 
from ECS_SCHEMA.cob_mcom_log
where end_date_time 
   between to_date(&1,'YYYYMMDD') 
   and     to_date(&2,'YYYYMMDD') 
and error_code <> 0;
exit;

together with this script

#!/usr/bin/perl
use strict;
use warnings;
use CGI;
use Time::Piece;

my $sqlfile  = '/var/www/cgi-bin/query.sql';

my $q = new CGI;
my $today = Time::Piece->new->ymd('');
my $startdate = $q->param('startdate') || $today;
my $enddate   = $q->param('enddate') || $today;

my $msg;
if ( ($startdate =~ /^20\d{6}$/) 
  && ($enddate   =~ /^20\d{6}$/) ){
  my $connstr = 'USER/PASWORD@DB';
  $msg = qx"sqlplus -s $connstr \@$sqlfile $startdate $enddate";
} else {
  $msg = "ERROR : Dates must be 20YYMMDD";
}

print $q->header,$q->start_html;
print qq!
<h3>Dates</h3>
<form>
Start date : <input type="text" maxlength="8" name="startdate" value="
+$startdate"/> yyyymmdd <br/>
End date   : <input type="text" maxlength="8" name="enddate"  value="$
+enddate"/> yyyymmdd</br>
<input type="submit"/>
</form>
</hr>
<h3>Result for $startdate to $enddate</h3>
<pre> $msg </pre>!;
print $q->end_html;
[download]

Open script with browser, enter dates and press submit

update yy-mm-dd corrected to yyyymmdd

poj

Comment on Re^3: CGI-SQL Query Issue Select or Download Code

Replies are listed 'Best First'.
Re^4: CGI-SQL Query Issue by Anonymous Monk on Mar 18, 2016 at 10:59 UTC
Thanks for your reply, but the code seems to not work in my environment. Here is what is get : `name of the script is monk.pl... URL- "http://el2310/cgi-bin/monk.pl"` [download] when I press enter, I get below error message: `Internal Server Error The server encountered an internal error or misconfiguration and was u +nable to complete your request. Please contact the server administrator, root@localhost and inform the +m of the time the error occurred, and anything you might have done th +at may have caused the error. More information about this error may be available in the server error + log.` [download] -------------------------------------------------------------------------------- Apache/2.2.15 (Red Hat) Server at el2310 Port 80 *********** when I try to run it from command line, I get below error: [ccacp@el2310 cgi-bin]$ perl monk.pl Can't locate Time/Piece.pm in @INC (@INC contains: /comptel/ccacp/elin +k/common/6.3/perl/lib/5.8.9/x86_64-linux-thread-multi /comptel/ccacp/ +elink/common/6.3/perl/lib/5.8.9 /comptel/ccacp/elink/common/6.3/perl/ +lib /comptel/ccacp/elink/common/6.3/perl/lib/site_perl/5.8.9/x86_64-l +inux-thread-multi /comptel/ccacp/elink/common/6.3/perl/lib/site_perl/ +5.8.9 /comptel/ccacp/elink/common/6.3/perl/lib/site_perl /comptel/cca +cp/elink/common/6.3/comptel_utilities/ctl_info/lib /comptel/ccacp/eli +nk/common/current/perl/lib/5.8.9/x86_64-linux-thread-multi /comptel/c +cacp/elink/common/current/perl/lib/5.8.9 /comptel/ccacp/elink/common/ +current/perl/lib/site_perl/5.8.9/x86_64-linux-thread-multi /comptel/c +cacp/elink/common/current/perl/lib/site_perl/5.8.9 /comptel/ccacp/eli +nk/common/current/perl/lib/site_perl /comptel/ccacp/elink/EventLink/b +ase/bin /temp/cpt2sve/comptel_common/tmp_cpt2sve/install/LINUXintel/p +erl/lib/5.8.9/x86_64-linux-thread-multi /temp/cpt2sve/comptel_common/ +tmp_cpt2sve/install/LINUXintel/perl/lib/5.8.9 /temp/cpt2sve/comptel_c +ommon/tmp_cpt2sve/install/LINUXintel/perl/lib/site_perl/5.8.9/x86_64- +linux-thread-multi /temp/cpt2sve/comptel_common/tmp_cpt2sve/install/L +INUXintel/perl/lib/site_perl/5.8.9 .) at monk.pl line 5. BEGIN failed--compilation aborted at monk.pl line 5. [download] ********************** Please suggest.	[reply] [d/l] [select]
Re^5: CGI-SQL Query Issue by Corion (Patriarch) on Mar 18, 2016 at 11:23 UTC
You're using an outdated version of Perl that has been unsupported for over five years. Either get your sysadmin to install the Time::Piece module, which also likely is available as a RedHat package, or install it yourself. Preferrably, you upgrade to a more recent version of Perl 5, like 5.22.	[reply]
Re^6: CGI-SQL Query Issue by Anonymous Monk on Mar 18, 2016 at 11:26 UTC
I removed the "Time/Piece.pm" part from the code & tried to rerun it. This time the script worked, but when I enter the dates I just get the output as "Result for 20160303 to 20160320", I entered START DATE as 20160303 & END DATE as 20160320... Dates Start date : yyyy-mm-dd End date : yyyy-mm-dd Result for 20160303 to 20160320 The sql query result is not getting populated in the web page output. Could you suggest?	[reply]
Re^7: CGI-SQL Query Issue by Corion (Patriarch) on Mar 18, 2016 at 11:28 UTC
Re^4: CGI-SQL Query Issue by Anonymous Monk on Mar 18, 2016 at 11:10 UTC
I removed the "Time/Piece.pm" part from the code & tried to rerun it. This time the query worked, but when I enter the dates, I just get the output as "Result for 20160303 to 20160320", I entered START DATE as 20160303 & END DATE as 20160320... Dates Start date : yyyy-mm-dd End date : yyyy-mm-dd Result for 20160303 to 20160320 The sql query result is not getting populated in the web page outout. Could you suggest?	[reply]
Re^5: CGI-SQL Query Issue by poj (Abbot) on Mar 18, 2016 at 11:33 UTC
Does it work if you put the dates in the SQL ? `between to_date('20160303','YYYYMMDD') and to_date('20160320','YYYYMMDD')` [download] poj	[reply] [d/l]
Re^6: CGI-SQL Query Issue by Anonymous Monk on Mar 18, 2016 at 12:15 UTC
NO, same result as before. That's the whole problem, SQL query is not working.	[reply]
Re^7: CGI-SQL Query Issue by poj (Abbot) on Mar 18, 2016 at 12:50 UTC
Re^8: CGI-SQL Query Issue by Anonymous Monk on Mar 18, 2016 at 15:22 UTC
Some notes below your chosen depth have not been shown here