I have a PHP script on a remote machine that pulls from a Microsoft SQL Server database and inserts the data into a MySQL database. I have a cron job set up to run the script hourly so the MySQL database is up-to-date with the SQL Server database.
My problem is that any time I run the script on the remote machine, the results for the query to the SQL Server are incomplete and vary when given the same parameters. Doesn't matter if I run it from the commandline or with a cronjob, it doesn't retrieve the correct number of rows.
But if I run the query on a different environment - in SQL Server Management Studio, or in a local test script - the results are consistently correct.
My first thought was that it was an environment configuration issue, but I found that using the init_set() function to set max_execution_time to 0, max_input_time to 0 and memory_limit to -1 (as per the php.ini directives documentation) had no effect on the results of the query.
My second thought was that it may be an issue with the firewall or network settings on the remote machine, but there is no firewall installed directly on the machine and while the network settings are extremely restrictive on inbound connections the outbound connections are completely open.
I have seen some similar issues online - (1), (2), (3), (4) - but the solutions either don't work or aren't applicable due to the specifics of the query I'm running vs what they are running.
I'm not sure what else the issue could be.
The local test script is as follows:
<?php
$connection_info = array(
"UID"=>"UserID",
"PWD"=>"password",
"Database"=>"database_name"
);
$serverName = 'database_host';
try{
$conn = sqlsrv_connect($serverName, $connection_info);
} catch(Exception $e){
die("Not able to connect.");
}
echo "Connected!\n";
$sql = "
SELECT
SP.SaleInvoiceID,
SP.AssociationNumber,
SP.Priority,
S.Abbreviation,
GP.GlobalProductID,
SI.OrderEntryID,
SI.OriginalSaleInvoiceID,
SI.AccountsReceivableID,
SI.EmployeeID3,
SI.InvoiceIDByStore AS [invoice],
(C.Customer_Name + ' ' + C.First_Name + ' ' + C.Last_Name) AS [customer],
C.Id_Number as [customer_id],
GP.ProductIdentifier AS [product_sku],
RGP.ManufacturerPartNumber AS [model],
CAST( L2.FieldText AS varchar ) AS [manufacturer],
CAST( L1.FieldText AS varchar ) AS [description],
CAST( L3.FieldText AS varchar ) AS [category],
SP.Quantity AS Qty,
SP.SerialNumber,
SP.ContractNumber,
SP.MobileNumber,
GP.CategoryNumber,
SP.UnitPrice,
SP.UnitCost,
SP.ListPrice,
SI.SplitRate1,
SI.SplitRate2,
SI.EmployeeID1,
SI.EmployeeID2,
E1.Employee_Name AS [Employee1],
E2.Employee_Name AS [Employee2],
SI.DateCreated,
SI.Comments
FROM [database_name].[dbo].[SaleInvoices] SI
INNER JOIN [database_name].[dbo].[SaleInvoicesAndProducts] SP
ON SI.SaleInvoiceID = SP.SaleInvoiceID
INNER JOIN [database_name].[dbo].[iQclerk_GlobalProducts] GP
ON SP.GlobalProductID = GP.GlobalProductID
INNER JOIN [database_name].[dbo].[LanguageTranslations] L1
ON GP.ProductNameID = L1.ReferenceID
INNER JOIN [database_name].[dbo].[Stores] S
ON S.StoreID = SI.StoreID1
INNER JOIN [database_name].[dbo].[Customer_Information] C
ON SI.CustomerID1 = C.Id_Number
INNER JOIN [database_name].[dbo].[Employees] E1
ON E1.Id_Number = SI.EmployeeID1
INNER JOIN [database_name].[dbo].[Categories] CAT
ON CAT.CategoryNumber = GP.CategoryNumber
INNER JOIN [database_name].[dbo].[LanguageTranslations] L3
ON CAT.CategoryPathID = L3.ReferenceID
LEFT OUTER JOIN [database_name].[dbo].[Employees] E2
ON E2.Id_Number = SI.EmployeeID2
LEFT OUTER JOIN [database_name].[dbo].[RegularGlobalProducts] RGP
ON RGP.GlobalProductID = GP.GlobalProductID
LEFT OUTER JOIN [database_name].[dbo].[Manufacturers] M
ON M.ManufacturerID = RGP.ManufacturerID
LEFT OUTER JOIN [database_name].[dbo].[LanguageTranslations] L2
ON M.ManufacturerNameID = L2.ReferenceID
WHERE SI.DateCreated
BETWEEN cast( '2024-03-04 00:00:00' AS DateTime )
AND cast( '2024-05-09 23:59:59' AS DateTime )
AND L3.LanguageCode = 'en-us'
";
$result = sqlsrv_query($conn, $sql, array(), array("Scrollable" => 'static'));
print("Data package received, etrieving data from package...\n");
$result_rows = array();
$i = 1;
while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC ) ){
print("Records retrieved: ".$i."\r");
$i++;
array_push($result_rows, $row);
}
print("\n");
print(sizeof($result_rows));
The data flow is more separated on the remote machine:
<?php
class SQLSRV{
protected $dbConn;
public function open_db($server, $user, $password, $database, $throwError=false)
{
global $_REGISTRY;
ini_set("max_execution_time","0");
ini_set("max_input_time","0");
ini_set("memory_limit","-1" );
$errorMsg = "SQLSRV_ERROR: Cannot connect to SQL server database!\n";
$serverName = $server;
$connectionInfo = array(
'UID' => $user,
'PWD' => $password,
'DATABASE' => $database
);
$this->dbConn = sqlsrv_connect($serverName, $connectionInfo);
if (!$this->dbConn) {
$_REGISTRY['STATE'] = 'FATAL';
$_REGISTRY['ERROR_MESSAGE'] = 'DATABASE_CONNNECTION_ERROR';
if($throwError) throw new \ErrorException( $errorMsg, 10001, 0,__file__, __line__ );
else return $errorMsg;
}
return true;
}
public function query( $sql, $throwError=true )
{
if( !$sql ) return null;
if(!$throwError) {
sqlsrv_configure("WarningReturnAsErrors", 0);
}
$errorMsg = "SQLSRV_ERROR: No database connection";
if(!$this->dbConn )
{
if( $throwError) throw new \ErrorException( $errorMsg, 10001, 0,__file__, __line__ );
else return $errorMsg;
}
$query = false;
try {
$query = sqlsrv_query( $this->dbConn, $sql, array(), array("Scrollable" => 'static') );
} catch( Exception $e ) {}
$queryError = 'SQLSRV_ERROR: ' . sqlsrv_errors() . "\n\n<pre>$sql</pre>\n";
if(!$throwError) {
sqlsrv_configure("WarningReturnAsErrors", 1);
}
if (!$query) {
if($throwError) throw new \Exception( $queryError );
else return $queryError;
} else {
return $query;
}
}
public function fetchData( $sql, $throwError=true )
{
$res = $this->query( $sql, $throwError );
if($res) {
$queryError = 'SQLSRV_ERROR: ' . sqlsrv_errors() . "\n\n$sql\n";
file_put_contents('/var/tmp/sqlsrv.log', "fetchData Res: $queryError\n", FILE_APPEND );
try{
if(sqlsrv_num_rows($res)) {
$ar = array();
while( $row = sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC ) )
{
array_push($ar, $row);
}
return $ar;
}
} catch( \Exception $e ) { return false;}
} else return false;
}
}
$sql_srv_obj = new SQLSRV();
$sql_srv_obj->open_db('database_host', 'username', 'password', 'database_name');
$sql_query = "
SELECT
SP.SaleInvoiceID,
SP.AssociationNumber,
SP.Priority,
S.Abbreviation,
GP.GlobalProductID,
SI.OrderEntryID,
SI.OriginalSaleInvoiceID,
SI.AccountsReceivableID,
SI.EmployeeID3,
SI.InvoiceIDByStore AS [invoice],
(C.Customer_Name + ' ' + C.First_Name + ' ' + C.Last_Name) AS [customer],
C.Id_Number as [customer_id],
GP.ProductIdentifier AS [product_sku],
RGP.ManufacturerPartNumber AS [model],
CAST( L2.FieldText AS varchar ) AS [manufacturer],
CAST( L1.FieldText AS varchar ) AS [description],
CAST( L3.FieldText AS varchar ) AS [category],
SP.Quantity AS Qty,
SP.SerialNumber,
SP.ContractNumber,
SP.MobileNumber,
GP.CategoryNumber,
SP.UnitPrice,
SP.UnitCost,
SP.ListPrice,
SI.SplitRate1,
SI.SplitRate2,
SI.EmployeeID1,
SI.EmployeeID2,
E1.Employee_Name AS [Employee1],
E2.Employee_Name AS [Employee2],
SI.DateCreated,
SI.Comments
FROM [database_name].[dbo].[SaleInvoices] SI
INNER JOIN [database_name].[dbo].[SaleInvoicesAndProducts] SP
ON SI.SaleInvoiceID = SP.SaleInvoiceID
INNER JOIN [database_name].[dbo].[iQclerk_GlobalProducts] GP
ON SP.GlobalProductID = GP.GlobalProductID
INNER JOIN [database_name].[dbo].[LanguageTranslations] L1
ON GP.ProductNameID = L1.ReferenceID
INNER JOIN [database_name].[dbo].[Stores] S
ON S.StoreID = SI.StoreID1
INNER JOIN [database_name].[dbo].[Customer_Information] C
ON SI.CustomerID1 = C.Id_Number
INNER JOIN [database_name].[dbo].[Employees] E1
ON E1.Id_Number = SI.EmployeeID1
INNER JOIN [database_name].[dbo].[Categories] CAT
ON CAT.CategoryNumber = GP.CategoryNumber
INNER JOIN [database_name].[dbo].[LanguageTranslations] L3
ON CAT.CategoryPathID = L3.ReferenceID
LEFT OUTER JOIN [database_name].[dbo].[Employees] E2
ON E2.Id_Number = SI.EmployeeID2
LEFT OUTER JOIN [database_name].[dbo].[RegularGlobalProducts] RGP
ON RGP.GlobalProductID = GP.GlobalProductID
LEFT OUTER JOIN [database_name].[dbo].[Manufacturers] M
ON M.ManufacturerID = RGP.ManufacturerID
LEFT OUTER JOIN [database_name].[dbo].[LanguageTranslations] L2
ON M.ManufacturerNameID = L2.ReferenceID
WHERE SI.DateCreated
BETWEEN cast( '2024-03-04 00:00:00' AS DateTime )
AND cast( '2024-05-09 23:59:59' AS DateTime )
AND L3.LanguageCode = 'en-us'
";
$data = $sql_srv_obj->fetchData($sql_query);
The login information is the same across all the query methods.
The problem was with the way I defined the cursor type:
//Old format
$result = sqlsrv_query($conn, $sql, array(), array("Scrollable" => 'static'));
//New new format
$result = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
While the old format worked on my local machine, it was not a valid value to pass into the sqlsrv_query() function. SQLSRV_CURSOR_STATIC is a constant that is defined in the SQLSRV package.