Posted by daneelcm on November 27, 2012 at 5:18pm
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.
Comments
I don't know what you mean by
I don't know what you mean by the nolock, but you cannot do a tablesort and pagerquery using db_query() in d7.
Jaypan We build websites
It's an option that you use
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.
Why would some one get
Why would some one get informations from a locked database table?
Until the server crashes you're doing it quite right
I use Drupal in the ERP
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...
ah okay that makes sense
ah okay that makes sense
Until the server crashes you're doing it quite right
Why would someone get
Sorry for double posting
Until the server crashes you're doing it quite right
Maybe this will help you ;)
http://api.drupal.org/api/drupal/includes!lock.inc/7
Until the server crashes you're doing it quite right
This won't help because its
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.
you can use db_query_range
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
Solution (for me): The
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'));
?>