Table with sqlsrv driver is not showing headers or pagination

Question

I just wanted to practice doing the table "header sorting" and pagination to display a Drupal 6 table using a simple MSSQL query. At the moment, the table headers are not showing and there is no pagination, but the data in the query is being displayed. Can anyone see anything wrong with the code?

/* open database connection */
include "mssqlconnection.inc.php";

/* set up the table headers */
$headers = array(
  '',
  array('image_id' => t('image_id'), 'field' => 'image_id', 'sort'=>'asc'),
  array('geo_post' => t('geo_post'), 'field' => 'geo_post'),
  array('geo_sample' => t('geo_sample'), 'field' => 'geo_sample'),
  array('gps_lat' => t('geo_lat'), 'field' => 'geo_lat'),
  array('gps_long' => t('gps_long'), 'field' => 'gps_long'),
  ''
);


/* Set up and execute the query. */
$sql = sprintf ("SELECT TOP 100 image_id,
  geo_post,
  geo_sample,
  gps_lat,
  gps_long,
  FROM images"
);

/* add the order by clause */
$stmt .= tablesort_sql($headers);

$stmt = sqlsrv_query($conn, $sql);
if ( $stmt === false) {
  echo "Error in query preparation/execution.\n";
  die( print_r( sqlsrv_errors(), true));
}

/* collecting results */

while ($doc = sqlsrv_fetch_object($stmt)) {
  $rows[] = array(
    sprintf($imgPublish . '   ' . $imgDelete, $doc->nid),
    $doc->image_id,
    $doc->geo_post,
    $doc->geo_sample,
    doc->gps_lat,
    $doc->gps_long
  );
}

$table = theme('table', $headers, $rows);
$table .= theme('pager', array(), 10, 0);

return $table;    

/* Close the connection. */
include "closemssqlconnection.inc.php";

Answer 1

theme_pager() only works in combination with pager_query() (Or the PagerDefault extender in Drupal 7). And that function is obviously not going to work with sqlsrv().

For Drupal 7, there is the http://drupal.org/project/sqlsrv project, which is properly integrated into the new database abstraction layer. I suggest you have a look at the following answer to see how you might be able to use that in Drupal 6: MSSql or SQL Server Wrapper for custom module? See also How do you make sortable tables with a pager with data from a custom table? for how to create paginated queries with Drupal 7/the DBTNG project.