Solved

Toad, Oracle, perl cgi issue

Posted on 2010-11-30
13
689 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
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 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 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

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.

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.…
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

815 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

9 Experts available now in Live!

Get 1:1 Help Now