By daneelcm on

Hello:

Somebody know how to get a db_select() "WITH (NOLOCK)", otherwise how do I make a Tablesort with a pager from a db_query()?

Thanks for your answers.

PD: I'm using SQLSRV database driver.

⋅ Categories: Drupal 7.x

Comments

jaypan’s picture

jaypan
Location Victoria, BC
commented

I don't know what you mean by the nolock, but you cannot do a tablesort and pagerquery using db_query() in d7.

Contact me to contract me for D7 -> D10/11 migrations.

daneelcm’s picture

daneelcm commented

It's an option that you use in select clauses (using T-SQL in SQL Server) to specify that you want to get the data without wait if a table is locked for update or other operation; see http://msdn.microsoft.com/en-en/library/ms187373.aspx.
I think i have a way to doit using the pager_default_initialize() function. When i finish the task will comment here about.

BlackyWhoElse’s picture

BlackyWhoElse commented

Why would some one get informations from a locked database table?

daneelcm’s picture

daneelcm commented

I use Drupal in the ERP database of my Company to make some actions via web and publish info for employees, so when the ERP is doing certain actions in certain tables he lock up that table and when I call with reports purposes, the select clause have to wait for the ERP to release the table.

So I have to use WITH (NOLOCK) to get the info without wait...

BlackyWhoElse’s picture

BlackyWhoElse commented

ah okay that makes sense

BlackyWhoElse’s picture

BlackyWhoElse commented

Sorry for double posting

BlackyWhoElse’s picture

BlackyWhoElse commented

http://api.drupal.org/api/drupal/includes!lock.inc/7

daneelcm’s picture

daneelcm commented

This won't help because its an internal implementations of database lock for drupal use. If you see the code it work only over the drupal table "semaphore". I refer to an implementation of a lock table that do the SQL Server motor (i dont know if other motor do something similar), otherwise i have a solution that will post below.

chetan-singhal’s picture

chetan-singhal commented

Hi all
You can use
db_query_range($query, $start, $limit);

other wise you can use pdo db_select

$query = db_select('comment', 'c')->distinct('c.cid')->orderBy('c.created', 'DESC')->extend('PagerDefault');;
  $query->fields('c', array('cid', 'uid', 'nid', 'subject', 'created', 'name', 'status', 'thread'));
  $query->limit($limit);
  $result = $query->execute()->fetchAll();

chetan

daneelcm’s picture

daneelcm commented

Solution (for me):
The db_select() function (for SQLSRV) don't allow to pass T-SQL query/table Hints so I have to use db_query_range() and do the "Tablesort" and "PagerDefault" manually; see code example below.

<?php
  $header = array(
    'product' => array('data' => t('Product'), 'field' => 'prod_code', 'sort' => 'asc'),
    'description' => array('data' => t('Description'), 'field' => 'description'),
  );
  
  $sort = isset($_GET['sort']) ? $_GET['sort'] : 'asc';
  $order = isset($_GET['order']) ? $_GET['order'] : t('Product');
  foreach ($header as $value) {
    if ($value['data'] == $order) {
      $order = $value['field'];
    }
  }

  $page = pager_find_page();
  $num_per_page = variable_get('my_module_limit', 20);
  $offset = $num_per_page * $page;
  $total = db_query("SELECT COUNT(*) FROM my_prod_table WITH (NOLOCK)")->fetchField();
  pager_default_initialize($total, $num_per_page);
  
  $rs = db_query_range("SELECT prod_code, description FROM my_prod_table WITH (NOLOCK) ORDER BY $order $sort", $offset, $num_per_page);

  $row = array();
  foreach ($rs as $item) {
    $row[$item->prod_code] = array(
      'product' => $item->prod_code,
      'description' => $item->description,
    );
  }
  
  $form['my_tableselect_sortable'] = array(
    '#type' => 'tableselect',
    '#header' => $header,
    '#options' => $row,
  );

  $form['pager'] = array('#markup' => theme('pager'));
?>