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!!