Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Toad, Oracle, perl cgi issue

Posted on 2010-11-30
13
Medium Priority
?
713 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 77

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:Justin Mathews
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 16

Expert Comment

by:Justin Mathews
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:Justin Mathews
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
 
LVL 77

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 77

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:
Justin Mathews earned 750 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 750 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
This video shows how to recover a database from a user managed backup
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.
Suggested Courses

618 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