fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
vikram_u
Forum Newbie
Posts: 5
Joined: Fri Nov 13, 2009 11:59 am

fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a

Post by vikram_u »

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.
Attachments
PHP debug log... mysqlcommand line indicates a successful run
PHP debug log... mysqlcommand line indicates a successful run
mysqli.png (19.63 KiB) Viewed 733 times
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
vikram_u
Forum Newbie
Posts: 5
Joined: Fri Nov 13, 2009 11:59 am

Re: fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a

Post by vikram_u »

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>' ;
}
vikram_u
Forum Newbie
Posts: 5
Joined: Fri Nov 13, 2009 11:59 am

Re: fail to return resultset storedproc on PHP5.2.5/MySQL5.0.51a

Post by vikram_u »

Thanks Guys.... just used CALL Stored proc using the mysql extension.. mysqli was disabled
Post Reply