PHP- Error with SQLSRV_FETCH_ASSOC & sqlsrv_fetch_array

Question

This code I am using to download result from sql server query in excel . Connection & query works fine and I have already installed sqlsrv extensions properly. But still I am getting error like these

"Use of undefined constant SQLSRV_FETCH_ASSOC - assumed SQLSRV_FETCH_ASSOC'"
"Warning: sqlsrv_fetch_array() expects parameter 2 to be long"

I think the problem is with SQLSRV_FETCH_ASSOC. Any help would be appreciated.

  function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

  $serverName = "********"; 
$connectionInfo = array( "Database"=>"****", "UID"=>"***", "PWD"=>"*****");
$connect = sqlsrv_connect( $serverName, $connectionInfo);

  // filename for download
  $filename = "website_data_" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

  $flag = false;
  $query="select * from business_partners ORDER BY 1 desc";

  $result = sqlsrv_query($connect, $query) or die('A error occured: ' . sqlsrv_errors());
  while(false !== ($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC))) 
  {
    if(!$flag) {
      // display field/column names as first row
      echo implode("\t", array_keys($row)) . "\r\n";
      $flag = true;
    }**strong text**
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
  }
  exit;

Gerald Schneider · Accepted Answer · 2016-01-21 08:05:22Z

3

I have no explanation why the constant wouldn't be defined while the extension is clearly loaded, but you should be able to circumvent the problem by defining it yourself.

SQLSRV_FETCH_ASSOC has the value 2, so you should just add this somewhere, at best in a file you include always:

if ( !defined( 'SQLSRV_FETCH_ASSOC' ) )
  define( 'SQLSRV_FETCH_ASSOC', 2 );

The if ( !defined() ) clause makes sure that your code doesn't break later, when the actual problem is fixed and the constant suddenly reappears.

Alternatively, if you only use it once in the file and it isn't really worth defining the constant (except for readability) you can just use the actual value as a parameter:

$row = sqlsrv_fetch_array( $result, 2 ); // 2 = SQLSRV_FETCH_ASSOC

edited Jan 21, 2016 at 8:05

answered Jan 21, 2016 at 8:00

Gerald Schneider

17.8k10 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

waleed. Over a year ago

I tried while(false !== ($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_NUMERIC))) it worked .. but i dont need numerical values. Extension I am using php_sqlsrv_55_ts.dll .

Gerald Schneider Over a year ago

Did you get the same undefined constant message for that?

waleed. Over a year ago

no. I dont know whats the issue with SQLSRV_FETCH_ASSOC

Gerald Schneider Over a year ago

Try removing the complete constant, and maybe the adjacent characters and type them again (don't copy and paste them). It is quite possible that it's just an invisible utf8-character that you copied accidentally somewhere

waleed. Over a year ago

I installed fresh "php_sqlsrv_55_ts.dll" and replaced with old one . I dont know how .. but that worked . Anyway Thanks Gerald

Collectives™ on Stack Overflow

PHP- Error with SQLSRV_FETCH_ASSOC & sqlsrv_fetch_array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related