Solved

Toad, Oracle, perl cgi issue

Posted on 2010-11-30
13
687 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
This article will show, step by step, how to integrate R code into a R Sweave document
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

864 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

24 Experts available now in Live!

Get 1:1 Help Now