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.
fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a
Moderator: General Moderators
fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a
- Attachments
-
- PHP debug log... mysqlcommand line indicates a successful run
- mysqli.png (19.63 KiB) Viewed 734 times
Re: fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a
There are 10 types of people in this world, those who understand binary and those who don't
Re: fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a
Thanks VladSun...
For now we are using mysql_query to call the StoredProc'e. seems to work on WAMP 5.1.7.3 and on the deployed system as well..
It appears that after anebling mysqli extensions we still have PHP 'eating' away the argument list. As said before commandline CALL's work on both environments.
// Get the employee ID of the assignee
$assignee_emp_id = $_REQUEST['employee_id'];
//echo $assignee_emp_id;
//echo $parent_plan_id;
$plan_currency = $_REQUEST['selected_currency'];
//echo $plan_currency;
// Prepare the query to call stored procedure
$assign_query = "CALL sp_create_plan_for_reportees('$assignee_emp_id','$parent_plan_id','$plan_currency',@new_plan_id)";
// Instantiate a mysqli interface. This is because, mysql had issues invoking stored procedure
//$assign_mysqli = new mysqli($SERVER, $USER, $PWD, $SCHEMA);
// Run the query and get the result
$assign_rs = mysql_query( $assign_query, $con );
//echo $assign_query;
// If the assignment was successful, display the message
if ($assign_rs)
{
echo $new_plan_id;
$report_msg="<div class='report_msg' align='center'>Assigned Successfully.</div><br/>";
echo $report_msg;
request_unset('assign');
//unset $_REQUEST['assign'];
echo '<script language="javascript">javascript:history.go(0)</script>' ;
}
For now we are using mysql_query to call the StoredProc'e. seems to work on WAMP 5.1.7.3 and on the deployed system as well..
It appears that after anebling mysqli extensions we still have PHP 'eating' away the argument list. As said before commandline CALL's work on both environments.
// Get the employee ID of the assignee
$assignee_emp_id = $_REQUEST['employee_id'];
//echo $assignee_emp_id;
//echo $parent_plan_id;
$plan_currency = $_REQUEST['selected_currency'];
//echo $plan_currency;
// Prepare the query to call stored procedure
$assign_query = "CALL sp_create_plan_for_reportees('$assignee_emp_id','$parent_plan_id','$plan_currency',@new_plan_id)";
// Instantiate a mysqli interface. This is because, mysql had issues invoking stored procedure
//$assign_mysqli = new mysqli($SERVER, $USER, $PWD, $SCHEMA);
// Run the query and get the result
$assign_rs = mysql_query( $assign_query, $con );
//echo $assign_query;
// If the assignment was successful, display the message
if ($assign_rs)
{
echo $new_plan_id;
$report_msg="<div class='report_msg' align='center'>Assigned Successfully.</div><br/>";
echo $report_msg;
request_unset('assign');
//unset $_REQUEST['assign'];
echo '<script language="javascript">javascript:history.go(0)</script>' ;
}
Re: fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a
Thanks Guys.... just used CALL Stored proc using the mysql extension.. mysqli was disabled