Solved

Toad, Oracle, perl cgi issue

Posted on 2010-11-30
13
681 Views
Last Modified: 2013-12-25
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

$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;

Open in new window


Prior to adding the date and time section, the db was populated perfectly.

Any thoughts on this would be greatly appreciated.

Regards,

seeker7806
0
Comment
Question by:seeker7806
  • 5
  • 4
  • 4
13 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34241162
I'm not a perl person but it is likely a date/time issue.  You really shouldn't do implicit date/time conversions.

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
0
 
LVL 16

Expert Comment

by:jmatix
ID: 34241297
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'))/;

Open in new window

0
 

Author Comment

by:seeker7806
ID: 34241545
Thanks jmatix:

I made the change but I received the following error message:

syntax error at /opt/httpd/root-wwwin/pcgi-bin/tech/performance-culture/ecards/addMsgToCard.cgi line 129, near "qq/insert into CDO_ECARD(REC_ID,USERID,TO_RECIP,FROM_SENDER,CC_MANAGER,CARD,MESSAGE,IMAGE,DATEANDTIME) values(?,?,?,?,?,?,?,?,to_date(?,'yyyy/mm"
Bad name after ss' at /opt/httpd/root-wwwin/pcgi-bin/tech/performance-culture/ecards/addMsgToCard.cgi line 129.
0
 
LVL 16

Expert Comment

by:jmatix
ID: 34241676
Oops. Forgot to escape slash (/). Try this instead:

$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'))#;
0
 

Author Comment

by:seeker7806
ID: 34241884
Thanks jmatix:

Still getting the following error:

syntax error at /opt/httpd/root-wwwin/pcgi-bin/tech/performance-culture/ecards/addMsgToCard.cgi line 129, near "qq/insert into CDO_ECARD(REC_ID,USERID,TO_RECIP,FROM_SENDER,CC_MANAGER,CARD,MESSAGE,IMAGE,DATEANDTIME) values(?,?,?,?,?,?,?,?,to_date(?, 'yyyy/mm"
Bad name after ss' at /opt/httpd/root-wwwin/pcgi-bin/tech/performance-culture/ecards/addMsgToCard.cgi line 129.

Regards,
seeker7806
0
 
LVL 16

Expert Comment

by:jmatix
ID: 34241931
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,FROM_SENDER,CC_MANAGER,CARD,MESSAGE,IMAGE,DATEANDTIME) values(?,?,?,?,?,?,?,?,to_date(?, 'yyyy/mm/dd:hh24:mi:ss'))#;
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34241986
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(?,'MMDDYYYYHH24MISS')

Just get Perl to sit out the date in a different format.
0
 

Author Comment

by:seeker7806
ID: 34242115
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
0
 

Author Comment

by:seeker7806
ID: 34243739
Hi Guys:

The perl/cgi is still not writing to the db.

Any suggestions on how to proceed?

Thanks for your help.

Regards,
seeker7806
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34243785
If you take out the date columns does it go back to working?
0
 
LVL 16

Accepted Solution

by:
jmatix earned 250 total points
ID: 34244005
When you changed the format, did you change the data also accordingly without /s and :s?

$dateandtime = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month+1, $dayOfMonth, $hour, $minute, $second);
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 34247803
Are you sure you didn't change anything else?

I've been researching perl/Oracle/DBI and I don't see where you are binding the parameters.

http://www.dba-oracle.com/t_dbi_interface1.htm
http://www.dba-oracle.com/t_dbi_perl_bind_calls.htm
0
 

Author Closing Comment

by:seeker7806
ID: 34381752
Thanks guys. I may have to revisit this later.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now