fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a
Posted: Fri Nov 13, 2009 12:52 pm
We have a storedproc call using multi_query that executes OK running on our Development Environment - WAMP 5.1.7.3 and XAMPP.
However on the deployed system, we have disparate Apache/MYSQL and PHP directories and configurations with PHP5.2.5 and Mysql5.0.51a we face issues wherein the mysqli/multi_query indicates that the Stored Proc was not able to return the resultset.
The SP does not return any result ( for now) and has been invoked successfully on the MySQL command line.. However we are unable to run the PHP file using php -f </path/to/file.php> method.
The Actual SP has been defined with permissions for the 'root' user invoking this proc.
The PHP Code is enclosed
<?php
$SERVER= 'localhost';
$USER = 'root';
$PWD ='root';
$SCHEMA ='aop_new';
$con = mysql_connect($SERVER, $USER, $PWD);
if (!$con)
die('Could not connect: ' . mysql_error());
mysql_select_db($SCHEMA, $con);
mysql_error();
$assignee_emp_id = '1172'; //$_REQUEST['emp_id'];
$parent_plan_id = "AOP-2010-11-30380";
$new_plan_id = "";
$query ="select p.plan_id from plan p where p.employee_id =$assignee_emp_id";
echo $query;
$current_plan_query_result = mysql_query($query,$con) or die(mysql_error());
$current_plan_count = mysql_num_rows($current_plan_query_result);
if ($current_plan_count == 0)
{
$result ="<div class='report_msg' align='center'>No Rows Exist for the Queried Employee.</div><br/>";
echo $result;
}
else
{
$result ="<div class='report_msg' align='center'>Rows Already Exist for the Queried Employee.</div><br/>";
echo $result;
}
// Prepare the query to call stored procedure
$assign_query = "CALL sp_create_plan_for_reportees('$assignee_emp_id','$parent_plan_id','INR',@new_plan_id)";
echo var_dump($assign_query);
// Instantiate a mysqli interface. This is because, mysql had issues invoking stored procedure
//$assign_mysql = new mysql($SERVER, $USER, $PWD, $SCHEMA);
// Run the query and get the result
//$assign_rs = $assign_mysqli->multi_query( $assign_query) or die (mysql_error());
$result=mysql_query($assign_query,$con) or die (mysql_error());
//echo $assign_query;
// If the assignment was successful, display the message
//if ($assign_rs != NULL)
{
echo $new_plan_id;
$report_msg="<div class='report_msg' align='center'>Assigned Successfully.</div><br/>";
echo $report_msg;
}
/*
$result = mysql_query($query,$con) or die(mysql_error());
$num_rows = mysql_num_rows($result);
//echo $num_rows;
$plan_details .='<table width="931" border="0" cellspacing="0" cellpadding="0"> ';
while ($row = mysql_fetch_array($result)) {
*/
/* $query ="select p.plan_id from plan p where p.employee_id =$assignee_emp_id";
echo $query;
$current_plan_query_result = mysql_query($query,$con) or die(mysql_error());
$current_plan_count = mysql_num_rows($current_plan_query_result);
if ($current_plan_count != 0)
{
$result ="<div class='report_msg' align='center'>Rows Exist for the Queried Employee.</div><br/>";
echo $result;
}
//phpinfo();
//echo (mysqli_get_server_info());
*/
?>
The mysqli extension is enabled in PHP.ini. It appears and we suspect this to be a environment issue wherein a multi_query is able to run from Commandline but not from a PHP script.. Could someone help with an actual method to debug/fix this issue if it is suspected to be a Environment issue
Thanks.
However on the deployed system, we have disparate Apache/MYSQL and PHP directories and configurations with PHP5.2.5 and Mysql5.0.51a we face issues wherein the mysqli/multi_query indicates that the Stored Proc was not able to return the resultset.
The SP does not return any result ( for now) and has been invoked successfully on the MySQL command line.. However we are unable to run the PHP file using php -f </path/to/file.php> method.
The Actual SP has been defined with permissions for the 'root' user invoking this proc.
The PHP Code is enclosed
<?php
$SERVER= 'localhost';
$USER = 'root';
$PWD ='root';
$SCHEMA ='aop_new';
$con = mysql_connect($SERVER, $USER, $PWD);
if (!$con)
die('Could not connect: ' . mysql_error());
mysql_select_db($SCHEMA, $con);
mysql_error();
$assignee_emp_id = '1172'; //$_REQUEST['emp_id'];
$parent_plan_id = "AOP-2010-11-30380";
$new_plan_id = "";
$query ="select p.plan_id from plan p where p.employee_id =$assignee_emp_id";
echo $query;
$current_plan_query_result = mysql_query($query,$con) or die(mysql_error());
$current_plan_count = mysql_num_rows($current_plan_query_result);
if ($current_plan_count == 0)
{
$result ="<div class='report_msg' align='center'>No Rows Exist for the Queried Employee.</div><br/>";
echo $result;
}
else
{
$result ="<div class='report_msg' align='center'>Rows Already Exist for the Queried Employee.</div><br/>";
echo $result;
}
// Prepare the query to call stored procedure
$assign_query = "CALL sp_create_plan_for_reportees('$assignee_emp_id','$parent_plan_id','INR',@new_plan_id)";
echo var_dump($assign_query);
// Instantiate a mysqli interface. This is because, mysql had issues invoking stored procedure
//$assign_mysql = new mysql($SERVER, $USER, $PWD, $SCHEMA);
// Run the query and get the result
//$assign_rs = $assign_mysqli->multi_query( $assign_query) or die (mysql_error());
$result=mysql_query($assign_query,$con) or die (mysql_error());
//echo $assign_query;
// If the assignment was successful, display the message
//if ($assign_rs != NULL)
{
echo $new_plan_id;
$report_msg="<div class='report_msg' align='center'>Assigned Successfully.</div><br/>";
echo $report_msg;
}
/*
$result = mysql_query($query,$con) or die(mysql_error());
$num_rows = mysql_num_rows($result);
//echo $num_rows;
$plan_details .='<table width="931" border="0" cellspacing="0" cellpadding="0"> ';
while ($row = mysql_fetch_array($result)) {
*/
/* $query ="select p.plan_id from plan p where p.employee_id =$assignee_emp_id";
echo $query;
$current_plan_query_result = mysql_query($query,$con) or die(mysql_error());
$current_plan_count = mysql_num_rows($current_plan_query_result);
if ($current_plan_count != 0)
{
$result ="<div class='report_msg' align='center'>Rows Exist for the Queried Employee.</div><br/>";
echo $result;
}
//phpinfo();
//echo (mysqli_get_server_info());
*/
?>
The mysqli extension is enabled in PHP.ini. It appears and we suspect this to be a environment issue wherein a multi_query is able to run from Commandline but not from a PHP script.. Could someone help with an actual method to debug/fix this issue if it is suspected to be a Environment issue
Thanks.