Query nightmare

[Muddy_Funster]

OK, I have been having issues with this query for some time now, I (finaly) have the query working absoloutly fine when run directly in the database, however, when I try and run it through a PHP page I just get a completly empty result set back.  I have done a print_r($row) and there is nothing, absoloutly nothing.  I have ran the SQL that comes from  print_r($sql) in the database and it's completly fine as well.  I was getting an error previously because I had missed the "IS" out before NOT NULL for the PadPrefix, so it is parsing the SQL to some level (I now have no errors showing either). 

 

I am wondering if there is something I am missing that is needed for when the SQL is creating cached tables and/or using a counter?

 

Here's the code, any and all suggestions welcome, Cheers

<?php
@SESSION_START();
require_once 'connect.php';
$bp = $_SESSION['bp'];
if ($_SESSION['id'] < 99){
$tbl_top = '<table><tr><th>SheetID</th></tr>';
$WHERE = " = '$bp'";
}
else{
$tbl_top = '<table><tr><th>SheetID</th><th>Prefix</th></tr>';
$WHERE = "IS NOT NULL";
}
$sql = "DECLARE @badSheets TABLE (ID int, batchMax int) ".
"DECLARE @list TABLE (maxsheet int, lastsheet int) ".
"DECLARE @sheet int ".
"DECLARE @batch int ".       
"DECLARE @endBatch int ".
"declare @batchIndex int ".
"INSERT INTO @list (maxsheet, lastsheet) ". 
"SELECT sheetList.maxsheet, padlist.lastSheet ".
" FROM( ".
" SELECT sheet.padID, MAX(sheet.SheetID) as maxsheet FROM jim.dbo.sheet as sheet ". 
" INNER JOIN jim.dbo.files as files on sheet.sheetID = files.sheetid ".
" where sheet.sheetid > 100000 ".
" group by sheet.padID) ".
"AS sheetList ".
"INNER JOIN( ".
"SELECT pads.padID, pads.padID+(count(pads.padID)-1) as lastSheet ".
"From jim.dbo.sheet as pads ".
"Group By pads.PadID) ". 
"AS padList ".
"ON (sheetList.padID = padList.padID) ".
"WHERE maxsheet != lastSheet ".
"DECLARE bad_batch CURSOR FOR ".
"SELECT DISTINCT maxsheet FROM @list ORDER BY MaxSheet ".
"OPEN bad_batch ".
"FETCH NEXT FROM bad_batch ".
"INTO @batchIndex ".
"WHILE @@FETCH_STATUS = 0 ".
"BEGIN ".
  "SELECT @batch = lastSheet FROM @List where maxsheet= @batchIndex ".
  "SELECT @sheet = maxSheet FROM @List where maxsheet= @batchIndex ".
  "WHILE (@sheet <= @batch) ".
    "BEGIN ".
    "INSERT INTO @badsheets(ID, batchMax) VALUES (@sheet, @batch) ".
    "SET @sheet = (@sheet + 1) ".
    "END ".
  "Fetch Next FROM bad_batch INTO @batchIndex ".
  "END ".
"close bad_batch ".
"deallocate bad_batch ".
"SELECT sheetID, padprefix ". 
"FROM JIM.dbo.sheet AS sheet LEFT OUTER JOIN jim.dbo.pad AS pads ".
   "ON sheet.padID = pads.padID ".
   "WHERE ( ".
"(padprefix $WHERE) ".
"AND (sheet.sheetID > 100000) ".
"AND (sheet.SheetID NOT IN (SELECT SheetID FROM jim.dbo.files)) ". 
"AND (sheet.sheetID NOT IN (SELECT ID FROM @badSheets)) ".
")";
$result = sqlsrv_query($sqlConnect, $sql) or die("error running : $sql <br><br>".print_r( sqlsrv_errors(), true));
echo "<table border=\"1\">";
echo $tbl_top;
WHILE ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
  if ($_SESSION['id'] < 99){
  $tbl_content = "<tr><td>{$row['sheetID']}</td></tr>";
  echo $tbl_content;
  }
  else{
   $tbl_content = "<tr><td>{$row['sheetID']}</td><td>{$row['padPrefix']}</td></tr>";
   echo $tbl_content;
  }
  
  echo $tbl_content;
  }
echo "</table>";
?>

[Muddy_Funster]

I have established it's not a timeout issue on the query either (forced a different query to timeout and got the "Server did not respond within 30 seconds" message.

 

This really has me stumped, even if there isn't a work around I would still like to know what the actual problem is.  It looks like it's firing the query and then not waiting for the result set to be generated...

[Muddy_Funster]

don't know if it helps, but when print_r($result) is run it returns "Resource id #6"

 

This is really starting to wind me up.

 

I have also changed the execution to sqlsrv_execute() and run it through that, which returned a value of 1, signaling that the query ran.  This was after about 0.5 seconds, when I know the actual query takes about 17-20 seconds to run.

 

Please, someone - what am I missing? :'( :'(

[xyph]

A database dump usually helps to debug SQL issues.

 

This seems to be an UPDATE query anyways. Why would you have anything returned beyond TRUE/FALSE?

[Muddy_Funster]

No, it is a select query, it just has to build a couple of temp tables on the fly and run a counter to filter off consecutive values from them before the desired results themselves can be generated with what is left.

 

an example result set when run within the database it's self is:

-------------------------------------------

sheetID

115003

115152

115581

120427

120428

120463

120683

121096

122962

123474

123476

123525

123526

123529

123530

123531

123532

123533

123534

123535

123569

123728

-------------------------------------------

 

As I said, I know the query works, even checking that the SQL that is being parsed is correct, which it is.  It's just not running using PHP.

[xyph]

I don't know much about the MS SQL PHP functions, so I can't help.

 

My only guess is, if it's working in a console and not PHP, it may be too complex. I doubt it would make a difference, but you could try prepare/execute?

 

Otherwise, the parser may interpret it as multiple queries, and silently fail? I don't know the inner workings, just guessing. Perhaps reworking your query and taking out chunks at a time would help you discover the issue.

[Muddy_Funster]

I tried the prepare/execute method to test if the query was working, it returned "1" so as far as it was concerned the execute succeeded.  I unfortunately can't get the result set that I need without doing it this way, so it's basicly an all or nothing deal.

 

Thanks for having a look though, I appreciate your time.

 

Interestingly, if a little off topic, the query runns in MS query within Excel, producing the desired results, but when I select the "return data to excel spreadsheet" It tries to rerun the query only to sit there and fill in cell A1 with the message "Data from Query...".  Probably related, but likely insignificantly so.

[Muddy_Funster]

Just going to have to chalk this one up as "Something that can't be done in PHP" I guess.