seeker7806
asked on
Toad, Oracle, perl cgi issue
I have an ecard page which was sending data to an oracle db. I access the oracle db via Toad.
It seems that after I added a date and time function to the ecard page, the db was not 1) inputting any more data in the database even though no error messages were received and 2) not inputting the data and time data.
Here is my ecard perl cgi:
addMsgToCard.cgi
Prior to adding the date and time section, the db was populated perfectly.
Any thoughts on this would be greatly appreciated.
Regards,
seeker7806
It seems that after I added a date and time function to the ecard page, the db was not 1) inputting any more data in the database even though no error messages were received and 2) not inputting the data and time data.
Here is my ecard perl cgi:
addMsgToCard.cgi
$from_sender = $q->param('from');
$to_recip = $q->param('to');
$cc_manager = $q->param('cc_mgr');
$bcc = "xxxx\@xxx.com";
$card = $q->param('card');
$message = $q->param('message');
#$cc_mgr = "$cc_manager\@xxxx.com";
@months = qw(Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec);
@weekDays = qw(Sun Mon Tue Wed Thur Fri Sat Sun);
($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime();
$year = 1900 + $yearOffset;
$dateandtime = "$hour:$minute:$second, $weekDays[$dayOfWeek] $months[$month] $dayOfMonth, $year";
.....
($host, $db, $dbUid, $dbPwd) = &dbInfo::setDbInfo();
$errStr = "";
$dbh = DBI->connect("DBI:Oracle:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=1521))(CONNECT_DATA=(SID=$db)))", "$dbUid", "$dbPwd");
$sql = qq/insert into CDO_ECARD(REC_ID,USERID,TO_RECIP,FROM_SENDER,CC_MANAGER,CARD,MESSAGE,IMAGE,DATEANDTIME) values(?,?,?,?,?,?,?,?,?)/;
$sth = $dbh->prepare($sql) or $errStr .="SQL Prepare Error: $DBI::errstr<br />";
$sth->execute($recId,$userId,$to_recip,$from_sender,$cc_manager,$card,$message,$imgFile,$dateandtime) or $errStr .= "Execute Error: $DBI::errstr<br />";
$sth->finish or $errStr .= "Finish Error: $DBI::errstr<br />";
$dbh->commit or $errStr .= "Commit Error: $DBI::errstr<br />";
$dbh->disconnect;
Prior to adding the date and time section, the db was populated perfectly.
Any thoughts on this would be greatly appreciated.
Regards,
seeker7806
You are giving 12hr time format, but passing hour in 24hr format. Change it to:
$dateandtime = sprintf("%04d/%02d/%02d:%02d:%02d:%02d", $year, $month+1, $dayOfMonth, $hour, $minute, $second);
.
.
.
$sql = qq/insert into CDO_ECARD(REC_ID,USERID,TO_RECIP,FROM_SENDER,CC_MANAGER,CARD,MESSAGE,IMAGE,DATEANDTIME) values(?,?,?,?,?,?,?,?,to_date(?, 'yyyy/mm/dd:hh24:mi:ss'))/;
ASKER
Thanks jmatix:
I made the change but I received the following error message:
syntax error at /opt/httpd/root-wwwin/pcgi -bin/tech/ performanc e-culture/ ecards/add MsgToCard. cgi line 129, near "qq/insert into CDO_ECARD(REC_ID,USERID,TO _RECIP,FRO M_SENDER,C C_MANAGER, CARD,MESSA GE,IMAGE,D ATEANDTIME ) values(?,?,?,?,?,?,?,?,to_ date(?,'yy yy/mm"
Bad name after ss' at /opt/httpd/root-wwwin/pcgi -bin/tech/ performanc e-culture/ ecards/add MsgToCard. cgi line 129.
I made the change but I received the following error message:
syntax error at /opt/httpd/root-wwwin/pcgi
Bad name after ss' at /opt/httpd/root-wwwin/pcgi
Oops. Forgot to escape slash (/). Try this instead:
$sql = qq#insert into CDO_ECARD(REC_ID,USERID,TO _RECIP,FRO M_SENDER,C C_MANAGER, CARD,MESSA GE,IMAGE,D ATEANDTIME ) values(?,?,?,?,?,?,?,?,to_ date(?, 'yyyy/mm/dd:hh24:mi:ss'))# ;
$sql = qq#insert into CDO_ECARD(REC_ID,USERID,TO
ASKER
Thanks jmatix:
Still getting the following error:
syntax error at /opt/httpd/root-wwwin/pcgi -bin/tech/ performanc e-culture/ ecards/add MsgToCard. cgi line 129, near "qq/insert into CDO_ECARD(REC_ID,USERID,TO _RECIP,FRO M_SENDER,C C_MANAGER, CARD,MESSA GE,IMAGE,D ATEANDTIME ) values(?,?,?,?,?,?,?,?,to_ date(?, 'yyyy/mm"
Bad name after ss' at /opt/httpd/root-wwwin/pcgi -bin/tech/ performanc e-culture/ ecards/add MsgToCard. cgi line 129.
Regards,
seeker7806
Still getting the following error:
syntax error at /opt/httpd/root-wwwin/pcgi
Bad name after ss' at /opt/httpd/root-wwwin/pcgi
Regards,
seeker7806
The error message still shows qq/ / instead of qq# #. Did you replace the code with:
$sql = qq#insert into CDO_ECARD(REC_ID,USERID,TO _RECIP,FRO M_SENDER,C C_MANAGER, CARD,MESSA GE,IMAGE,D ATEANDTIME ) values(?,?,?,?,?,?,?,?,to_ date(?, 'yyyy/mm/dd:hh24:mi:ss'))# ;
$sql = qq#insert into CDO_ECARD(REC_ID,USERID,TO
If the '/' is causing a problem, just change the incoming date format and mask it differently. You can remove all special characters is you wish:
to_date(?,'MMDDYYYYHH24MIS S')
Just get Perl to sit out the date in a different format.
to_date(?,'MMDDYYYYHH24MIS
Just get Perl to sit out the date in a different format.
ASKER
Thanks slightwv:
I made the change and there is no longer a bad name error. We will see if it wrote to the db.
Regards,
seeker7806
I made the change and there is no longer a bad name error. We will see if it wrote to the db.
Regards,
seeker7806
ASKER
Hi Guys:
The perl/cgi is still not writing to the db.
Any suggestions on how to proceed?
Thanks for your help.
Regards,
seeker7806
The perl/cgi is still not writing to the db.
Any suggestions on how to proceed?
Thanks for your help.
Regards,
seeker7806
If you take out the date columns does it go back to working?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. I may have to revisit this later.
Try adding a to_date call with the appropriate Oracle date format mask:
values(?,?,?,?,?,?,?,?, to_date(?,'MM/DD/YYYY'))/
You can change the mask (the MM/DD/YYYY) part to whatever you want to match the string you are passing in.
http://www.oradev.com/oracle_date_format.jsp