PHP query functions sqlsrv_query and odbc_execute not working

General PHP Help
#1

I’m using Windows Server 2012 to host PHP 5.5 applications hitting SQL Server 2008 database server. I am able to connect to the database and pull data using the sqlsrv_query and/or odbc_execute functions on stored procedures when the procedures do not require any parameters. But, when I add a parameter, I do not get a result set. I do not get errors, but I do not get any data, etither. Any idea what is going?

SOME CODE:

	$connectionInfo = array("Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass );
	$conn = sqlsrv_connect($myServer, $connectionInfo);
	if(! $conn ) {
 		echo "Connection could not be established.<br />";
 		die( print_r( sqlsrv_errors(), true));
	}
	$sql = "EXEC uspGetRouteInfo @argSignRef = ?";
	if($Bid_Id <=  0){
		$params = array($Bid_Id);
		$result = sqlsrv_query( $conn, $sql, array(intval($Bid_Id)));
		if( !$result ) {
			die( print_r("<br>ERROR:  " . sqlsrv_errors(), true));
		}
	}
	else{
		$params = array(array($Bid_Id, SQLSRV_PARAM_IN));
		$result = sqlsrv_query( $conn, $sql, $params);

                                        if( !$result ) {
			        die( print_r("<br>ERROR:  " . sqlsrv_errors(), true));
		}
                          }
	$dataTableBody = "";
	$dataTable="<div style='padding-left:48px;'><table style='padding:10px;width:52%;'><tr style='background-color:AliceBlue;font-weight:700; color:Navy; font-size:12.52px; font-family: Arial, Helvetica, sans-serif'><td style='padding:4px'>Route No</td><td style='padding:4px'>Route Name</td><td style='padding:4px'>Route Origin</td><td style='padding:4px'>Route Destination</td></tr>";

	$x=0;
	global $route_Direction;
	$route_Direction= array();
	echo("<br>NUMBER FIELDS: " . sqlsrv_num_fields($result));
	
	    $next_result = sqlsrv_next_result($result);
		if ($next_result)
		{
			echo"<br>Got Something";
			$data = array();
			while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
				echo"<br>Got Something MORE!";
				$data[] = $row;
			}
			sqlsrv_free_stmt($result);
			print_r("<br>" . $data);
		}
		else if (is_null($next_result))
		{
			echo 'No More Results';
		}
		else
		{
			echo 'SQL Error';
			print_r(sqlsrv_errors());
		}

	//while(@sqlsrv_fetch_array($result))
	while(@sqlsrv_fetch($result))
	{
		
		echo"<br>Got MORE!";
		$route_Direction[$x]["Route Id"] = $x;
		$route_Direction[$x]["Route No"] = trim(sqlsrv_get_field($result, 0));// "RouteNbr"
		$route_Direction[$x]["Route Name"] = sqlsrv_get_field($result, 1); 	// "RouteName"
		$route_Direction[$x]["Origin"] = sqlsrv_get_field($result, 2); 		// "Direction0"
		$route_Direction[$x]["Destination"] = sqlsrv_get_field($result, 3); 	// "Direction1"
		$dataTableBody .= "<tr><td style='padding:4px'>" . $route_Direction[$x]["Route No"]. "</td>"
		. "<td style='padding:4px'>" . $route_Direction[$x]["Route Name"] . "</td>"
		. "<td style='padding:4px'>" . $route_Direction[$x]["Origin"] . "</td>"
		. "<td style='padding:4px'>" . $route_Direction[$x]["Destination"] . "</td></tr>";
		$x++;
	}
	echo("<br>" . $x);
	sqlsrv_free_stmt($result);
	if($keepOpen <> true){sqlsrv_close($conn);}	
	
	//print_r ($route_Direction,0);
	$dataTable = $dataTable . $dataTableBody . "</table></div>";
	$dataType= "ROUTE";
	return $dataTable;
#2

HERE IS CLEANER CODE (Most error evaluation code removed):

	$connectionInfo = array("Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass );
	$conn = sqlsrv_connect($myServer, $connectionInfo);
	if(! $conn ) {
 		echo "Connection could not be established.<br />";
 		die( print_r( sqlsrv_errors(), true));
	}
	if($Bid_Id ===  0){
		echo("<br>GO 1");
		$params = array(&$Bid_Id);
		//$result = sqlsrv_query( $conn, $sql, array(intval($Bid_Id)));
		$result = sqlsrv_query($conn, "{call uspGetRouteInfo()}");
		//$result = sqlsrv_query($conn, "{call uspGetSignUps()}");
		if($result === false) {
			die( print_r("<br>ERROR:  " . sqlsrv_errors(), true));
		}
	}
	else{
		$sql = "EXEC uspGetRouteInfo @argSignRef = ?";
		echo("<br>GO 2 on Bid_ID: " . $Bid_Id);
		$params = array(array(intval($Bid_Id), SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT));
		$result = sqlsrv_query( $conn, $sql, $params);
		$errors=sqlsrv_errors();
		$err = "";
		if($result === false) {
			die( print_r("<br>ERROR:  " . sqlsrv_errors(), true));
		}
	}
	echo("<br>NUMBER ROWS: " . sqlsrv_num_rows($result));
	$dataTableBody = "";
	$dataTable="<div style='padding-left:48px;'><table style='padding:10px;width:52%;'><tr style='background-color:AliceBlue;font-weight:700; color:Navy; font-size:12.52px; font-family: Arial, Helvetica, sans-serif'><td style='padding:4px'>Route No</td><td style='padding:4px'>Route Name</td><td style='padding:4px'>Route Origin</td><td style='padding:4px'>Route Destination</td></tr>";

	$x=0;
	global $route_Direction;
	$route_Direction= array();
	//while(@sqlsrv_fetch_array($result))
	while(@sqlsrv_fetch($result))
	{
		
		echo"<br>Got MORE!";
		$route_Direction[$x]["Route Id"] = $x;
		$route_Direction[$x]["Route No"] = trim(sqlsrv_get_field($result, 0));// "RouteNbr"
		$route_Direction[$x]["Route Name"] = sqlsrv_get_field($result, 1); 	// "RouteName"
		$route_Direction[$x]["Origin"] = sqlsrv_get_field($result, 2); 		// "Direction0"
		$route_Direction[$x]["Destination"] = sqlsrv_get_field($result, 3); 	// "Direction1"
		$dataTableBody .= "<tr><td style='padding:4px'>" . $route_Direction[$x]["Route No"]. "</td>"
		. "<td style='padding:4px'>" . $route_Direction[$x]["Route Name"] . "</td>"
		. "<td style='padding:4px'>" . $route_Direction[$x]["Origin"] . "</td>"
		. "<td style='padding:4px'>" . $route_Direction[$x]["Destination"] . "</td></tr>";
		$x++;
	}
	echo("<br>" . $x);
	sqlsrv_free_stmt($result);
	if($keepOpen <> true){sqlsrv_close($conn);}	
	
	//print_r ($route_Direction,0);
	$dataTable = $dataTable . $dataTableBody . "</table></div>";
	$dataType= "ROUTE";
	return $dataTable;
Sponsor our Newsletter | Privacy Policy | Terms of Service