Solved

Toad, Oracle, perl cgi issue

Posted on 2010-11-30
13
705 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: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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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
 
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:
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 77

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Six Sigma Control Plans

707 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