I am migrating a site from PHP 5.6.40 to PHP 7.2.15 and some of the stored procedures (SP) are returning empty statements. Some SP run fine other do not. The SP run fine from Management Studio.
protected function __query() {
$args = func_get_args();
$query = array_shift($args);
foreach($args as &$arg) {
$arg = $this->__ms_sql_escape($arg);
}
$query = vsprintf($query, $args);
$res1 = sqlsrv_query($this->_db, $query );
if(true) { // make it easy to switch in and out debugging
echo "<br>jjnj-dba-10<br>";
echo "Query: " . $query . "<br>";
echo "jjnj-dba-20 dump follows: <br>";
var_dump($res1);
echo "<br>jjnj-dba-30 post dump: <br>";
print_r(sqlsrv_errors());
echo "<br>jjnj-dba-40 post errors: <br>";
}
return $this->__toArray($res1);
} // End query
protected function __toArray($resultIn) {
if($resultIn) {
$results = array();
while($row = sqlsrv_fetch_array($resultIn, SQLSRV_FETCH_ASSOC)) {
$results[] = $row;
}
sqlsrv_free_stmt ( $resultIn );
return $results;
} else {
return(false);
}
} // End to array
Calling the SP
class RisingStars extends DbAdaptor { // DbAdaptor contains the above functions
....
function getFeaturedRisingStar() {
$data = $this->__query('exec getRisingStarFeatured ' );
var_dump($data);
return $data[0];
}
The SP
USE [dbName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getRisingStarFeatured]
AS
SET NOCOUNT ON
SELECT -- [getRisingStarFeatured]
CONVERT(VARCHAR(8), ra.race_date, 1) AS race_date
,ra.track_id
...
,cu.name as currency_name
FROM ( SELECT
q2.id
...
,q2.created_by
FROM (
(SELECT q1.id
,q1.race_id
...
,q1.created_by
FROM
(SELECT r.id
,r.race_id
...
,r.created_by
FROM dbo.rising_star r
LEFT OUTER JOIN dbo.horse h on r.horse_id = h.id
WHERE horse_id = (SELECT rs.horse_id from rising_star rs where id = (SELECT MAX(id) from rising_star )
)
) q1
LEFT OUTER JOIN dbo.horse h1 on q1.dam_id = h1.id
LEFT OUTER JOIN dbo.horse h1a on q1.sire_id = h1a.id) q2
LEFT OUTER JOIN dbo.horse h2 on q2.dam_dam_id = h2.id )) q3
LEFT OUTER JOIN dbo.horse h3 on q3.dam_dam_dam_id = h3.id
LEFT OUTER JOIN dbo.horse h4 ON q3.dam_sire_id = h4.id
LEFT OUTER JOIN dbo.horse h5 on q3.dam_dam_sire_id = h5.id
LEFT OUTER JOIN dbo.horse h6 on h3.sire_id = h6.id
LEFT OUTER JOIN dbo.files f on q3.pedigree_file_id = f.id
JOIN dbo.race ra on q3.race_id = ra.id
JOIN dbo.track t on ra.track_id = t.id
JOIN dbo.country c on t.country_id = c.id
LEFT OUTER JOIN dbo.currency cu on c.currency_id = cu.id
JOIN dbo.race_type rt ON ra.type_id = rt.id
JOIN dbo.horse_gender g on q3.gender_id = g.id
ORDER BY ra.race_date DESC
The output:
jjnj-dba-10
Query: exec getRisingStarFeatured
jjnj-dba-20 dump follows:
resource(736) of type (SQL Server Statement)
jjnj-dba-30 post dump:
jjnj-dba-40 post errors:
jjnj-dba-300 in to array
array(0) { }
So It appears I am getting an empty statement and no errors are returned from SQL Server. Other SPs on the site use the same calls to DbAdaptor and work just fine.
Changing the query to
$res1 = sqlsrv_query($this->_db, $query, array(), array( "Scrollable" => SQLSRV_CURSOR_STATIC ) );
yields:
jjnj-dba-10
Query: exec getRisingStarFeatured
jjnj-dba-20 dump follows:
bool(false)
jjnj-dba-30 post dump:
Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000
[1] => 16954 [code] => 16954
[2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Executing SQL directly; no cursor. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Executing SQL directly; no cursor. )
[1] => Array ( [0] => 01S02 [SQLSTATE] => 01S02 [1] => 0 [code] => 0
[2] => [Microsoft][ODBC Driver 17 for SQL Server]Cursor type changed [message] => [Microsoft][ODBC Driver 17 for SQL Server]Cursor type changed ) )
jjnj-dba-40 post errors:
bool(false)
Some additional information that may be helpful:
SQL Server 10.50.1600 - 875261 - DB\875261 - admin