This one has got us REALLY baffled. Standard logic is not applying. Scenario: Solaris 8, Oracle 8.1.7, Perl 5.6.1, DBI/DBD Oracle (versions escape me at the moment, but their fairly up to date.)
My program (or suite of them) is an in house issue tracking system. Along with the tables for tracking the issues are tables for users and groups. The forms are simple HTML with some SSI (to make maintenance simpler). Different locations include different HTML/JavaScript files to dispatch the issues to different groups/administrators. The main form and CGI that process it are identical. In my DB library I have a sub called "GetGroup()", pass it an ID and a hash reference, it reads the database for the record with ID specified and puts the fields/values in to the hash ref. Simple enough. I use it dozens of times. One obvious place is in the group editor, to display or edit the group parameters, you obviously have to read the group information from the group table. This works, the SQL statement looks like this: bits::db::GetGroup(): select ID,Name,Status,Members,Email,Feedback,FeedbackURL, ReplyTo,Hours,UsesStatus,PriorityDue,Dispatcher,DispatcherAcct, to_char(DateUpdated, 'MM/DD/YYYY HH12:MI:SS PM'),CcGroup from BITS_ADMIN.BITS_GROUP where ID=1062 The code is pretty straight forward with a couple debug statements to see output in the Apache error log file (as seen above.) Now for the kicker, if I'm opening a form (doesn't matter which of multiple types of forms) for one location I get the message: [Thu Oct 31 14:11:25 2002] mk.misc.pl: DBD::Oracle::db prepare failed: ORA-00921: unexpected end of SQL command (DBD ERROR: OCIStmtExecute/Describe) at /usr/local/lib/perl5/site_perl/5.6.1/bits/db.pm line 293. One would think there is a problem with the code, but the statement it prints out is the exact same SQL statement that works above. I can even copy/paste it in to SQLPlus (with the addition of a ; terminator) and it works fine. So why does it say "unexpected end of SQL command?!" Perhaps it is the data? Since I can submit the same forms using the same CGIs (and different JavaScript to dispatch it to group 1046 instead of 1062)... that's not it. If it had been the data, they why does the same call to GetGroup() work when I use the group editor? Same SQL statement echoed to the Apache error log file. In a nutshell, I already have the database handle from an DBI->connect that all programs must call when they start. I then construct the SQL statement, print it out, print out a line of three "-" (as a way to make sure my prepare method is about to get called, then do the prepare, execute, then print a line of three "+". When I use GetGroup() from the group editor, everything works fine. Output resembles the following: bits::db::GetGroup(): select ID,Name,Status,Members,Email,Feedback,FeedbackURL, ReplyTo,Hours,UsesStatus,PriorityDue,Dispatcher,DispatcherAcct, to_char(DateUpdated, 'MM/DD/YYYY HH12:MI:SS PM'),CcGroup from BITS_ADMIN.BITS_GROUP where ID=1062 --- +++ When I try to submit a form for this certain location, I get: bits::db::GetGroup(): select ID,Name,Status,Members,Email,Feedback,FeedbackURL, ReplyTo,Hours,UsesStatus,PriorityDue,Dispatcher,DispatcherAcct, to_char(DateUpdated, 'MM/DD/YYYY HH12:MI:SS PM'),CcGroup from BITS_ADMIN.BITS_GROUP where ID=1062 --- [Thu Oct 31 14:11:25 2002] mk.misc.pl: DBD::Oracle::db prepare failed: ORA-00921: unexpected end of SQL command (DBD ERROR: OCIStmtExecute/Describe) at /usr/local/lib/perl5/site_perl/5.6.1/bits/db.pm line 293. OK, so maybe it's my code... Not likely. The same CGI is used, the same library is used, the same GetGroup() code is used in db.pm regardless of which location you use. More information... I cannot reproduce this on the development system despite the fact that I've imported the data from production twice. I am using CVS to manage the code tree and have pulled the exact same release from the repository to production and the development system and still cannot reproduce it on the development system nor fix it on the production system. My "matrix of testing" has only one piece that is flawed and it seems to have no common element with any other piece. It's not the code, it's not the data, and I can't reproduce it on another system. So, Why am I being told this is an incomplete SQL statement when it is not? Why is the same code behaving differently when the same arguments are passed in and the same output is being generated in the error log? Is it something in the way I'm doing the basic DBI->connect(), dbh->prepare(), sth->execute() sequence? It has been a long time since I've had a really challenging problem! --Chuck
