Page 1 of 1

Oracle-PHP connectivity Issue or Is it?

Posted: Thu Sep 15, 2011 4:35 pm
by saranyaharikrish712
Hello,

I am a newbie to this forum.

Thank you in advance to any suggestions you folks might have to offer.

Do let me know too, if you d need any more info.

I have an multiple-choice assessment tool, which is intended to collect responses from php form and send them back to an oracle database. The data storage is taken care of by stored procedures. I have a DAOFunctions.php class which contains all the functions which are used to communicate with the database and the stored procedures.

I ve used the following codes

---------------------------------------------------------------------------------------------
include 'DataConnection.php';
if (!$connection) {
//echo "Couldn't make a connection!";
exit;
}
else{
$sql1 = "BEGIN SCOTT.RETRIEVEQUESTIONINFO(:Question,:Model,:Topic,:StudentResponse,:InfoIndex, :ReturnValue); END;";
$sql_statement1 = OCIParse($connection,$sql1);
oci_bind_by_name($sql_statement1, ':Question', $Question);
oci_bind_by_name($sql_statement1, ':Model', $Model);
oci_bind_by_name($sql_statement1, ':Topic', $Topic);
oci_bind_by_name($sql_statement1, ':StudentResponse', $StudentResponse);
oci_bind_by_name($sql_statement1, ':InfoIndex', $InfoIndex);
oci_bind_by_name($sql_statement1, ':ReturnValue', $ReturnValue);
oci_execute($sql_statement1);
oci_commit($connection);
oci_free_statement($sql_statement1);
oci_close($connection);
}
return $ReturnValue;

------------------------------------------------------------------------------------------------------

In the above code $StudentResponse is a String (The corresponding variable in the database is of type VARCHAR2(500)), while the rest of the variables are integers(The corresponding variables in the database are NUMERIC), and $ReturnValue captures the out parameter of the Stored Procedure. In the Database, The $StudentResponse takes the value of a radiobutton selected on the forms and matches with the value stored in the database to evaluate a correct or a wrong choice.

I have a two oracle accounts, one as an admin, and the other as a reader account. The php pages access the database through the reader account.

PROBLEM :
This assessment is taken simultaneously by about 15-25 people , (Every time a question is answered, the database is accessed through the reader account and the response stored, and if successful, then the connection is closed) and there are about 30+ questions and for most of the time, this code works fine! But there are instances where even though the $StudentResponse obtained is a match to what is stored on the database, the stored procedure fails to evaluate. When I execute the proc manually

For example :
variable r number;
Exec retrieveQuestionInfo(3,2,1,'This is the right answer',2,:r);

It seems to match and retrieve the value as expected.

This works for say 15 out of the 20 who take the test, and fails in some cases. This is important since our research is based on what we obtain as a response. I m baffled as to how the same choice of a value as chosen from the radiobuttons on the forms throws an exception for some but match perfectly for some others. I use POST to obtain the values from the form. Also, php pages use the same reader account to execute stored procs in the database. I m wondering if this is an ORACLE issue or a PHP issue. Kindly let me know what I am missing here.

Thank you again, and for the patience to read and think about this long post!! :)

Re: Oracle-PHP connectivity Issue or Is it?

Posted: Thu Sep 15, 2011 6:08 pm
by Eric!
What exactly does this mean: "the stored procedure fails to evaluate." Are you getting garbage data or real errors? What are the errors? And what are the details behind this error: "... the forms throws an exception"?

Re: Oracle-PHP connectivity Issue or Is it?

Posted: Thu Sep 15, 2011 7:03 pm
by saranyaharikrish712
Hi Eric !

Yes, the stored procedure does give out Garbage Data! I have an if-else clause in the procedure to evaluate the match between the response obtained and that which is stored in the database. It returns the default value when the responses do not match (Which ideally should not occur, since there will be a match)!

Thank you

Re: Oracle-PHP connectivity Issue or Is it?

Posted: Sat Sep 17, 2011 1:24 pm
by jonesi
Hi saranyaharikrish712,

Please correct me if I am wrong on any of the following points:

1) You are passing data to an Oracle stored procedure that has 5 input parameters and one output parameter.
2) Sometimes when you call the procedure the value returned by the output parameter matches an expected result.
3) Sometimes when you call the procedure the value returned by the output parameter does not match the expected result. The error!
4) No Oracle exception 'ORA-xxxx' is raised.

This suggests to me a data or logical error in the database.

I suggest creating a logging / trace table in the database. Use a sequence as the key and include a date / timestamp column. Create a stored procedure to insert trace messages into this table. Instrument your RETRIEVEQUESTIONINFO procedure with trace messages, be sure to log input parameters at the start, any NO DATA FOUND conditions and the return result. Run the procedure for scenarios which you know cause erroneous output. View the data in the trace table to debug and solve to problem.

I hope this makes sense. Apologies if this sounds like teaching grandmar to suck eggs!