|
Lori713 has asked for the wisdom of the Perl Monks concerning the following question:
I'm trying to analyze an error message I receive when running a certain option (type of report) from main.pl. The error message states:
[Mon Dec 15 10:18:23 2003] nc_rpt_summary.pl: ct_result(ct_dynamic(CS_
+PREPARE)) returned -205 at /usr/local/lib/perl5.8.0/site_perl/sun4-so
+laris/DBD/Sybase.pm line 124. [Mon Dec 15 10:18:23 2003] nc_rpt_summa
+ry.pl: DBD::Sybase::db prepare failed: Server message number=10734 se
+verity=16 state=1 line=2 server=FSPROD02procedure=DBD1text=Cannot run
+ this command because Java services are not enabled. A user with Syst
+em Administrator (SA) role must reconfigure the system to enable Java
+.
However, this error message does not appear if I run the other two options. The thing that is unique about the option that errors is that I'm trying to use a substring-type function within the SQL to make it call the correct data. (Lines 42-45 in summary.pl is my best guess).
So, I thought by running the summary.pl on the command line in Unix, I'd get more info on exactly what it doesn't like (not sure what all that Java stuff is about). Usually I find running my scripts from the command line more informative and helpful in leading me to the path of debugment.
So, my question is: Can I run the summary.pl program from the command line, passing in the CGI variables it's looking for so it knows which template to get, what the placeholder values are, etc.?
main.pl
#!/usr/local/bin/perl5_8
#Campus Main Menu
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
+#remove for PRD
use HTML::Template;
my $CGI = CGI->new;
#clear buffers and set up web page (required)
$|=1;
print $CGI->header;
#get the main menu, and pass $CGI variables to template(s) as needed
my $template = HTML::Template->new( filename => 'nc_mainmenu.
+tmpl',
associate => $CGI,
loop_context_vars => 1,
global_vars => 1,
);
############################### Begin Section ########################
+###############
#Set up and generate drop down list for report dates variable ($rpt_as
+ofdt)
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
my $mo = $mon + 1;
my $dy = $mday;
my $yr = $year + 1900;
#Set array to look up month text and last day of month (add dummy to i
+ndex item 0)
my @lkup_mon_text = ( 'DUMMY000','JAN','FEB','MAR','APR','MAY',
'JUN','JUL','AUG','SEP','OCT','NOV','DEC');
my @lkup_mon_long = ( 'DUMMY000','January','February','March','April',
+'May', 'June',
'July','August','September','October','November'
+,'December');
my @lkup_last_day = ( 'DUMMY000','31','28','31','30','31',
'30','31','31','30','31','30','31');
#loop through to generate rolling drop down list of months ('25' is ar
+bitrary)
my ( $i, $next_mmyyyy, @rollingmonth, @dt_loop );
for ( $i=1; $i<=25; $i++ )
{ $next_mmyyyy = "Month Ending $lkup_mon_long[$mo] $yr";
push(@rollingmonth, $next_mmyyyy);
#pad months 1-9 with a leading zero
if ( length($mo) == 1 )
{ $mo = "0$mo";
}
#set up hash for TMPL_LOOP params
my %dt_data;
$dt_data{dt_value} = "$yr~$mo~$dy~$lkup_mon_long[$mo] $dy, $yr
+";
$dt_data{dt_display} = "$next_mmyyyy";
push(@dt_loop, \%dt_data);
#determine if rolling back to previous calendar year
$mo = $mo - 1;
if ( $mo eq 0 )
{ $mo = 12;
$yr = $yr - 1;
}
$dy = $lkup_last_day[$mo];
#determine if February and a Leap Year; if so, last day = 29
if ( ( $mo eq 2 ) && ( $yr % 4 eq 0 ) )
{ $dy = 29;
}
}
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
############################### Begin Section ########################
+###############
#Control table used to generate report radio buttons, rpt titles...
#template vars: key->Rpt no. (rpt_no); value->Rpt Descr (rpt_descr)
my %rpt_tbl = ( '1' => 'Fiscal Year-to-Date Financial Information'
,'2' => 'QTD/PTD Financial Information'
,'3' => 'Cash Balance'
,'4' => 'Financial Status'
,'5' => 'Financial Summary Status'
,'6' => 'Summary by Phase'
,'7' => 'Summary by Segment'
);
my (@radio_loop, $key);
foreach $key ( sort keys %rpt_tbl )
{ push(@radio_loop, {rpt_no => $key, rpt_descr => $rpt_tb
+l{$key}});
}
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
#pass the date loop info to the template, along with other params, and
+ print web page
$template->param(dt_select=>\@dt_loop);
$template->param(radio =>\@radio_loop);
$template->param(title_bar=>"Main Menu");
print $template->output();
summary.pl
#!/usr/local/bin/perl5_8
#Main Summary Report program. This .pl processes the data from nc_mai
+n.pl and
#generates a Summary report based on the radio button selected on the
+Main Menu.
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
+#remove for PRD
use HTML::Template;
use DBI;
use My::pswd;
my $CGI = CGI->new;
$|=1;
print $CGI->header;
my $err;
my $server = $My::pswd::server;
my $userid = $My::pswd::userid;
my $passwd = $My::pswd::passwd;
############################### Begin Section ########################
+###############
#Set up variables
my $rpt_id = $CGI->param('rpt_id');
my @rpt_id = split (/_/, $rpt_id); #i.e., 1_summary
my $rpt_no = $rpt_id[0];
my $rpt_lvl = $rpt_id[1];
my $rpt_lvlU = ucfirst($rpt_lvl);
my $rpt_name = $CGI->param('rpt_name');
my $proj_id = $CGI->param('proj_id');
my $mycheck = $CGI->param('mycheck');
my ($rpt_type, $proj_select, $sql_select);
if ( substr($proj_id, 0, 1) == "0" )
{ $rpt_type = "PROJECT_REF";
$proj_select = "PROJECT_REF = ";
$sql_select = $rpt_type;
}
elsif ( $mycheck eq "yes" )
{ $rpt_type = "PROJECT_ID";
$proj_select = "PROJECT_ID = ";
$sql_select = $rpt_type;
}
else { $proj_id = "'" . substr($proj_id, 0, 6) . "%" . "'";
$rpt_type = "PROJECT_ID";
$proj_select = "PROJECT_ID LIKE ";
$sql_select = "SUBSTRING(PROJECT_ID,1,6)";
}
my $rpt_dates = $CGI->param('rpt_dates');
my @rpt_dates = split (/~/, $rpt_dates); #yyyymmdd~Month En
+ding mmm yyyy
my $sql_asofyr = $rpt_dates[0];
my $sql_asofmo = $rpt_dates[1];
my $sql_asofdy = $rpt_dates[2];
my $rpt_asofdt = $rpt_dates[3];
my $rpt_tmpl = "nc_rpt" . "$rpt_id" . ".tmpl";
my $tmpl_main = HTML::Template->new( filename => $rpt_tmpl,
associate => $CGI,
global_vars => 1,
);
#Calculate fiscal year based on $rpt_asofdt
my $attr_fyr;
if ( $sql_asofmo <= '06' )
{ $attr_fyr = $sql_asofyr;
}
else { $attr_fyr = $sql_asofyr + 1
}
$tmpl_main->param(attr_fyr => $attr_fyr);
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
############################### Begin Section ########################
+###############
#Control table used to generate case rows, labels, etc. (separated by
+a ~ character)
#key: last two digits: '00' => white row; '01' => green row; '99' => s
+ub/total row
#value1: the first section indicates the number of account ranges for
+ that row
#value2: the second section contains the Description
#value3: the third and subsequent sections are the account ranges (gr
+ouped by two)
my %ctrl_tbl = ( '010-00' => '1~Cash~11100~11100'
,'020-99' => '1~Total Assets~10000~19999'
,'030-00' => '1~Accounts Payable~22010~22010'
,'040-99' => '1~Total Liabilities~20000~29999~'
,'050-99' => '1~Fund Equity~30000~39999'
,'060-99' => '1~Total Revenue~40000~49999'
,'070-00' => '1~EPA Non-Teaching Salaries~51000~51199'
,'080-01' => '1~SPA Employee Salaries~51200~51299'
,'090-00' => '1~EPA Teaching Salaries~51300~51399'
,'100-01' => '1~Temporary Wages~51400~51499'
,'110-00' => '1~Other Personnel Costs~51500~51799'
,'120-01' => '1~Staff Benefits~51800~51899'
,'130-99' => '1~Total Personnel Costs~51000~51899'
,'140-00' => '1~Contracted Services~51900~51999'
,'150-01' => '1~Supplies and Materials~52000~52999'
,'160-00' => '2~Travel - Domestic~53100~53129~53140~53
+199'
,'170-01' => '1~Travel - Foreign~53130~53139'
,'180-00' => '2~Current Services~53000~53099~53200~539
+99'
,'190-01' => '1~Fixed Charges~54000~54999'
,'200-00' => '1~Capital Outlays~55000~55999'
,'210-01' => '1~Student Aid~56000~56979'
,'220-00' => '1~Subcontractors~56980~56989'
,'230-01' => '3~Other Charges~56990~58949~58951~58959~
+58961~59999'
,'240-00' => '1~Budget Pool~58950~58950'
,'250-01' => '1~F&A~56960~56960'
,'260-99' => '1~Total Non-Personnel Costs~51900~59999'
,'270-99' => '1~Total Costs~50000~59999'
,'280-99' => '1~Net Total~40000~59999'
,'290-00' => '1~FYTD Change in Accts Rec~11300~11399'
,'300-00' => '1~FYTD Change in Accts Pay~22010~22010'
,'888-99' => '1~Uncategorized Accounts~acct2~acct2'
,'999-99' => '1~Uncategorized Accounts~acct1~acct1'
);
my (@ctrl_split, $case_when, $exp_descr, $when_count, $p, $i, $key);
foreach $key ( sort ( keys %ctrl_tbl ) )
{ @ctrl_split = split (/~/, $ctrl_tbl{$key});
$when_count = $ctrl_split[0];
$exp_descr = $ctrl_split[1];
$case_when = " $case_when \n WHEN ACCOUNT BETWEEN '$ct
+rl_split[2]'
AND '$ctrl_split[3]' ";
$i = 0;
while ( $when_count - $i > 1 )
{ $p = ( $i*2)+4;
$case_when = " $case_when \n OR ACCOUNT BETWEEN
'$ctrl_split[$p]' AND '$ctrl_spli
+t[$p+1]' ";
$i++;
}
$case_when = " $case_when \n THEN '$key' ";
}
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
############################### Begin Section ########################
+###############
#Connect to database, fetch attributes, put in @loop_attr array to pas
+s to template
my $dbh = DBI->connect("dbi:Sybase:server=" . $server, $userid, $passw
+d) ||
(&dberror("connect",$err=$DBI::errstr));
#keep fields in SELECT in this order so @loop_attr sets up template va
+rs correctly
my $sql_attr = "
SELECT HDR.$sql_select, PROJECT_REF, DEPTID, DESCR, GR_AWARD_NBR, PRO
+GRAM_CODE,
CONVERT(CHAR(10),START_DT,101) + ' - ' +
CONVERT(CHAR(10),END_DT,101) 'projpd',
RTRIM(CONVERT(CHAR(10),GR_BUDG_BEG,101)) + ' - ' +
RTRIM(CONVERT(CHAR(10),GR_BUDG_END,101)) 'budgpd',
FUND_CODE, PERCENTAGE_ALLOC, APPROPRIATION_NBR, EFF_STATUS,
GR_FIS_MGR, GR_EQUIP_IND, MANAGER_NAME, GR_PI_NAME
FROM FS702RPT.dbo.PS_NC_PROJ_ATTR_VW HDR
WHERE HDR.EFFDT = (SELECT MAX(EFFDT) FROM FS702RPT.dbo.PS_NC_PROJ_AT
+TR_VW
WHERE HDR.SETID = SETID AND HDR.$rpt_type = $rpt_
+type)
AND HDR.$proj_select ?
";
my $sth_attr= $dbh->prepare($sql_attr) || (&dberror("prep-1",$err=$DBI
+::errstr));
$sth_attr->execute($proj_id) || (&dberror("slct-1",$err=$DBI
+::errstr));
my (@loop_attr, $attr_exists, %row_attr);
do { while ( $attr_exists = $sth_attr->fetch )
{ $row_attr{proj_id} = $attr_exists->[0];
$row_attr{proj_ref} = $attr_exists->[1];
$row_attr{attr_dept} = $attr_exists->[2];
$row_attr{attr_descr} = $attr_exists->[3];
$row_attr{attr_award} = $attr_exists->[4];
$row_attr{attr_prog} = $attr_exists->[5];
$row_attr{attr_projpd} = $attr_exists->[6];
$row_attr{attr_budgpd} = $attr_exists->[7];
$row_attr{attr_fund} = $attr_exists->[8];
$row_attr{attr_rate} = $attr_exists->[9];
$row_attr{attr_subclass} = $attr_exists->[10];
$row_attr{attr_status} = $attr_exists->[11];
$row_attr{attr_spec} = $attr_exists->[12];
$row_attr{attr_equip} = $attr_exists->[13];
$row_attr{attr_resp} = $attr_exists->[14];
$row_attr{attr_pi} = $attr_exists->[15];
push(@loop_attr, \%row_attr);
}
};
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
############################### Begin Section ########################
+###############
#Unique SQL criteria for various columns
my $sql_cfte = "
ROUND ( SUM (
CASE
WHEN STATISTICS_CODE = 'FTE'
AND SCENARIO IN ('CN24','CN26','CY21','CY23')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-
+1,2))) )
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'cfte'
";
my $sql_cbud = "
ROUND ( SUM (
CASE
WHEN CURRENCY_CD = 'USD' AND SCENARIO IN ('CGLOAD','CG20','C
+G21','CI21','CN24','CN26','CY21','CY23','FN21')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-
+1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'cbud'
";
my $sql_cmo = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD = CONVERT(INT, ( SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)
+)) )
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'cmo'
";
my $sql_fytd = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-
+1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'fytd'
";
my $sql_pre = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-
+1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN PRE_ENCUM_AMOUNT
ELSE 0
END),2) 'pre'
";
my $sql_enc = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-
+1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN ENCUMBERED_AMOUNT
ELSE 0
END),2) 'enc'
";
my $sql_ffte = "
ROUND ( SUM (
CASE
WHEN STATISTICS_CODE = 'FTE' AND SCENARIO IN ('CN24','CN26','FY
+27','FY29')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-
+1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'ffte'
";
my $sql_fbud = "
ROUND ( SUM (
CASE
WHEN CURRENCY_CD = 'USD'
AND SCENARIO IN ('CN24','CN26','FY27','FY29')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111
+2010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-
+1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'fbud'
";
#Main SQL statement for all Budget columns
my $sql_bud = "
SELECT RTRIM($sql_select) 'PROJECT',
CASE
$case_when
ELSE '999'
END 'exp_catbud',
$sql_cfte,
$sql_cbud,
$sql_ffte,
$sql_fbud
FROM FS702RPT.dbo.PS_NC_RP_LEDG_BUDG
WHERE BUSINESS_UNIT = 'NCSU1'
AND LEDGER = 'BD_ACTUALS'
AND FISCAL_YEAR = DATEPART (YEAR,DATEADD(MONTH,6,'$rpt_asofdt'))
AND (ACCOUNTING_PERIOD <= CONVERT(INT, ( SUBSTRING ('07080910111201
+0203040506',
( DATEPART (MONTH, '$rpt_aso
+fdt')*2)-1,2))))
AND $proj_select ?
GROUP BY RTRIM($sql_select),
CASE
$case_when
ELSE '999'
END
";
#Main SQL statement for all Actuals columns
my $sql_act = "
SELECT RTRIM($sql_select) 'PROJECT',
CASE
$case_when
ELSE '999'
END 'exp_catact',
$sql_cmo,
$sql_fytd,
$sql_pre,
$sql_enc,
SUM(POSTED_TOTAL_AMT), SUM(PRE_ENCUM_AMOUNT), SUM(ENCUMBERED_AMOUNT)
FROM FS702RPT.dbo.PS_NC_RP_LEDGER
WHERE BUSINESS_UNIT = 'NCSU1'
AND LEDGER = 'ACTUALS'
AND CURRENCY_CD = 'USD'
AND FISCAL_YEAR = DATEPART (YEAR,DATEADD(MONTH,6,'$rpt_asofdt'))
AND ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('07080910111201020
+3040506',
(DATEPART (MONTH, '$rpt_asof
+dt')*2)-1,2)))
AND $proj_select ?
GROUP BY RTRIM($sql_select),
CASE
$case_when
ELSE '999'
END
";
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
############################### Begin Section ########################
+###############
#Generate Budget cell data
my $sth_act= $dbh->prepare($sql_bud) || (&dberror("prep-1",$err=$DBI::
+errstr));
$sth_act->execute($proj_id) || (&dberror("sel-1",$err=$DBI::e
+rrstr));
my @loop_data = ();
my ($bud_exists, $act_exists, %row_bud, %row_act, %col_acct, %col_desc
+r, %col_cfte,
%col_cbud, %col_cmo, %col_fytd, %col_pre, %col_enc, %col_ffte, %co
+l_fbud);
do { while ( $bud_exists = $sth_act->fetch )
{ $row_bud{col_acct} = $bud_exists->[1];
$row_bud{col_cfte} = $bud_exists->[2];
$row_bud{col_cbud} = $bud_exists->[3];
$row_bud{col_ffte} = $bud_exists->[4];
$row_bud{col_fbud} = $bud_exists->[5];
push(@loop_data, \%row_bud);
$col_cfte{$bud_exists->[1]} = $bud_exists->[2];
$col_cbud{$bud_exists->[1]} = $bud_exists->[3];
$col_ffte{$bud_exists->[1]} = $bud_exists->[4];
$col_fbud{$bud_exists->[1]} = $bud_exists->[5];
}
};
#Generate Actuals cell data
$sth_act= $dbh->prepare($sql_act) || (&dberror("prepare-1",$err=$DB
+I::errstr));
$sth_act->execute($proj_id) || (&dberror("select-1",$err=$DBI::errs
+tr));
do { while ( $act_exists = $sth_act->fetch )
{ $row_act{col_acct} = $act_exists->[1];
$row_act{col_cmo} = $act_exists->[2];
$row_act{col_fytd} = $act_exists->[3];
$row_act{col_pre} = $act_exists->[4];
$row_act{col_enc} = $act_exists->[5];
push(@loop_data, \%row_act);
$col_cmo{$act_exists->[1]} = $act_exists->[2];
$col_fytd{$act_exists->[1]} = $act_exists->[3];
$col_pre{$act_exists->[1]} = $act_exists->[4];
$col_enc{$act_exists->[1]} = $act_exists->[5];
}
};
$dbh->disconnect || (&dberror("disconnect",$err=$DBI::errstr));
############################### Begin Section ########################
+###############
#Generate and sort cell data for report
@loop_data = (); #ask
+ Bill why empty
my ($acct_lbl, $acct_count, $ptr2, $i2, @loop_data2);
foreach $key ( sort(keys %ctrl_tbl) )
{ my %row_act; #ask Bill why re-initialized insi
+de this foreach
@ctrl_split = split (/~/, $ctrl_tbl{$key});
$acct_count = $ctrl_split[0];
$acct_lbl = "$ctrl_split[2]-$ctrl_split[3]";
$i2 = 0;
while ( $acct_count - $i2 > 1 )
{ $ptr2 = ($i2*2)+4;
$acct_lbl = "$acct_lbl, $ctrl_split[$ptr2]-$ctr
+l_split[$ptr2+1]";
$i2++;
}
$row_act{col_acct} = $acct_lbl;
$row_act{col_descr} = $ctrl_split[1];
if ( $col_cfte{$key} ne "" )
{ $row_act{col_cfte} = $col_cfte{$key};
$row_act{col_cbud} = $col_cbud{$key};
$row_act{col_ffte} = $col_ffte{$key};
$row_act{col_fbud} = $col_fbud{$key};
}
else { $row_act{col_cfte} = 0;
$row_act{col_cbud} = 0;
$row_act{col_ffte} = 0;
$row_act{col_fbud} = 0;
}
if ( $col_cmo{$key} ne "")
{ $row_act{col_cmo} = $col_cmo{$key};
$row_act{col_fytd} = $col_fytd{$key};
$row_act{col_pre} = $col_pre{$key};
$row_act{col_enc} = $col_enc{$key};
}
else { $row_act{col_cmo} = 0;
$row_act{col_fytd} = 0;
$row_act{col_pre} = 0;
$row_act{col_enc} = 0;
}
$row_act{col_bba} = ( $row_act{col_cbud}
- $row_act{col_fytd}
+ $row_act{col_pre}
+ $row_act{col_enc} );
foreach my $k ( qw(col_cfte col_cbud col_cmo col_fytd c
+ol_pre col_enc col_bba col_ffte col_fbud) )
{ $row_act{$k} = &commify($row_act{$k});
+}
push(@loop_data, \%row_act);
}
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
############################### Begin Section ########################
+###############
#Pass parameters and variable values from @loop arrays to template; pr
+int report
$tmpl_main->param(title_bar => "Report $rpt_no - $rpt_lvlU");
$tmpl_main->param(hdr_asofdt => "$rpt_asofdt");
$tmpl_main->param(passattrs => \@loop_attr);
$tmpl_main->param(passacts => \@loop_data);
print $tmpl_main->output();
print "mycheck value = $mycheck<br>\n";
print "rpt_type value = $rpt_type<p>\n";
print "proj_id value = $proj_id<p>\n";
print "sql_attr value =<p> $sql_attr\n<p>";
print "sql_bud value =<p> $sql_bud\n<p>";
print "sql_act value =<p> $sql_act\n<p>";
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
############################### Begin Section ########################
+###############
#Subroutines below
sub dberror #Generates error messages when login fails
{
my $err_call= $_[0];
my $err_txt= $_[1];
print ("<p>A Database Error has occured<p>");
if ( $err_call eq "connect" )
{ if ( $err_txt =~ "Login failed\." )
{ print "An invalid login was submitted to the databas
+e server.";
print "Please re-submit your login credentials and r
+etry.<p>";
}
else { print "<p>Error from $err_call= $err_txt<p>";
}
}
if ( $err_txt =~ "Data is unavailable; try again later." )
{ print "The database is currently offline. Please try again
+ later.";
}
else { print "<p>Error from $err_call<p>";
print "Text of error is: $err_txt<p>";
print "Please contact the Help Desk at (919) 513-1178 for a
+ssistance<p>";
}
exit;
}
sub commify #Formats numbers (puts in commas)
{
local $_ = sprintf "%.2f", shift @_;
1 while $_ =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
return $_;
}
#+++++++++++++++++++++++++++++++ End Section +++++++++++++++++++++++++
++++++++++++++++
Thanks for any guidance you can provide. Please, feel free to dumb it down for me as I'm still relatively new to getting all this <bleep> to work together.
Thanks!
janitored by ybiC: Replaced <pre> tags around error message with <code> to avoid lateral scrolling for monks with less-than-large displays |
Re: How do I run .pl script from Unix command line, and pass CGI variables to it?
by derby (Abbot) on Dec 15, 2003 at 16:18 UTC
|
Not that I have an answer, but I would tackle this by placing
debug statements in your cgi right before each execute. If you
print, to stderr, the sql statement and the bind values, you can
then inspect the apache (or whatever web server) error log for
the actual sql that is causing problems. Once you've isolated the
actual sql, check it out via isql or sqsh to see if it really is a problem
on the database server end (the error statement makes me think
the database server is having problems - but who knows, you could
be sending it faulty sql).
On another note, check out the DBI and DBD documentation for
a slightly better approach on executing sql. I prefer setting the
RaiseError flag and then wrapping the actual executes in evals.
That way the driver will not spill out error messages that will cause
your webserver to barf - but you will have enough info to know
what to do:
my $dbh = DBI->connect(
"dbi:Sybase:database=xxx", "user", "pass",
{ RaiseError => 1 } );
eval {
my $sth = $dbh->prepare( $sql );
$sth->execute();
...
};
if( $@ ) {
print STDERR "An error occurred: $@\n";
} else {
print "All is right\n";
}
-derby
| [reply]
[d/l] |
|
|
Thanks for the pointers. I'm especially interested in ways to test my SQL statements. SQL (along with Perl, HTML, Unix stuff, etc.) is brand new to me. Right now, I have the SQL printing out at the bottom of the web page, just so I can look at what it's really generating. It's ugly, but helps a little bit. ;-)
| [reply] |
Re: How do I run .pl script from Unix command line, and pass CGI variables to it?
by Abigail-II (Bishop) on Dec 15, 2003 at 15:46 UTC
|
Did you try running it from the command line? If you did,
did anything get printed?
Abigail | [reply] |
|
|
When I run summary.pl from the command line, I get the following message:
/local/www/htdocs/scripts/campus>nc_rpt_summary.pl
Content-Type: text/html; charset=ISO-8859-1
Software error:
HTML::Template->new() : Cannot open included file nc_rpt.tmpl : file not found.
at /usr/local/lib/perl5.8.0/site_perl/HTML/Template.pm line 1580
HTML::Template::_init_template
('HTML::Template=HASH(0x28a914)') called
at /usr/local/lib/perl5.8.0/site_perl/HTML/Template.pm line 1177
HTML::Template::_init
('HTML::Template=HASH(0x28a914)') called
at /usr/local/lib/perl5.8.0/site_perl/HTML/Template.pm line 1071
HTML::Template::new
('HTML::Template','filename','nc_rpt.tmpl',
'associate','CGI=HASH(0x1800ec)',
'global_vars',1) called
at .//nc_rpt_summary.pl line 56
For help, please send mail to this site's webmaster, giving this error message
and the time and date of the error.
Mon Dec 15 09:51:20 2003 nc_rpt_summary.pl: HTML::Template->new() :
Cannot open included file nc_rpt.tmpl : file not found. at /usr/local/lib/perl5.8.0/site_perl/HTML/Template.pm line 1580
Mon Dec 15 09:51:20 2003 nc_rpt_summary.pl: HTML::Template::_init_template('HTML::Template=HASH(0x28a914)') called
at /usr/local/lib/perl5.8.0/site_perl/HTML/Template.pm line 1177
Mon Dec 15 09:51:20 2003 nc_rpt_summary.pl: HTML::Template::_init('HTML::Template=HASH(0x28a914)') called at /usr/local/lib/perl5.8.0/site_perl/HTML/Template.pm line 1071
Mon Dec 15 09:51:20 2003 nc_rpt_summary.pl: HTML::Template::new('HTML::Template','filename','nc_rpt.tmpl','associate','CGI=HASH(0x1800ec)','global_vars',1) called at .//nc_rpt_summary.pl line 56
Lori
update: added some breaks so the lines don't run across the page
| [reply] |
|
|
I'd say, fix your environment first so that you can run
your program from the command line. Now you're getting all
kinds of file not found errors unrelated to your original
problem.
Abigail
| [reply] |
|
|
|
|
| |
|
Your template paths are relative:
my $template =
HTML::Template->new( filename => 'nc_mainmenu.tmpl',
...
So either change those to be absolute (/path/to/nc_mainmenu.tmpl)
or copy the templates to the directory you're executing the offline
script from or check the webserver configuration to see if HTML_TEMPLATE_ROOT is set to something - if so, set your offline
environment to have that same variable.
-derby
| [reply]
[d/l] |
|
|
Re: How do I run .pl script from Unix command line, and pass CGI variables to it?
by kutsu (Priest) on Dec 15, 2003 at 15:57 UTC
|
| [reply] |
|
|
