Re: CGI-SQL Query Issue

The SQL itself is bad. Double-quotes are used for identifiers, single-quotes are used for data. Sqlplus should have barfed with a very clear error message:

ERROR at line 1:
ORA-00904: "yyyymmdd": invalid identifier
[download]

So, instead of "yyyymmdd", use 'yyyymmdd'. Furthermore, '88888888' is confusing, as without significant change, the highest date in year 8888 would be 12/31.

But, that's not the best way to do it anyway. In general, you should avoid placing a function on a column in a where clause, both because it negates the use of (a non-function based) index, and because the function must be executed for every value. That a lot of wasted CPU time. Instead, place the functions on the values, so that the functions run only once:

end_date_time BETWEEN TO_DATE('00010101', 'YYYYMMDD') AND TO_DATE('88881231', 'YYYYMMDD')

Fwiw, unless you specifically want to exclude all dates BCE, the between itself is redundant, and can be expressed:

end_date_time <= TO_DATE('88881231', 'YYYYMMDD')

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

Replies are listed 'Best First'.
Re^2: CGI-SQL Query Issue by Anonymous Monk on Mar 17, 2016 at 15:00 UTC
sql query in the code was just for test purpose. Below is the sql I expect to run(using the STARTDATE & ENDDATE values user provides) select date_time_out,order_id,error_message,customer_id from ECS_SCHEMA.cob_mcom_log where to_char(end_date_time,'yyyymmdd') between $STARTDATE and $ENDDATE and error_code<>0;	[reply]
Re^3: CGI-SQL Query Issue by poj (Abbot) on Mar 17, 2016 at 18:55 UTC
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	[reply] [d/l] [select]
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
Re^6: CGI-SQL Query Issue by Anonymous Monk on Mar 18, 2016 at 11:26 UTC
Some notes below your chosen depth have not been shown here
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
Re^6: CGI-SQL Query Issue by Anonymous Monk on Mar 18, 2016 at 12:15 UTC
Some notes below your chosen depth have not been shown here
Re^3: CGI-SQL Query Issue by chacham (Prior) on Mar 17, 2016 at 15:38 UTC
That uses dynamic sql, which is generally considered bad. Please do not use dynamic sql. Instead, convert $STARTDATE and $ENDDATE to placeholders. Then execute the query by passing the values. If you want to use sqlplus, "var" will set a local variable and :variable can be used as the placeholder. It still stands that to_char() on the column is not optional and should instead be moved to enclose the variables. That would also, likely, produce clearer error messages on any passed data.	[reply]