Loading Large Dataset using datatables with AJAX

OK, so we manage to retrieve records from Active Directory from SQL Server using Linked Server, and the process is now automated as detailed in a previous article.

Now let’s take that data and display them in a simple listing with page pagination and search/filter capability. To achieve this we will make use of a JavaScript library called datatables. And to ensure great loading time (i.e. no page freeze for large dataset of 5000 or more retrieved records), we will make of the AJAX and deferRenders options in datatables.

Retrieving records from the database

Create a new ColdFusion component called staff.cfc and add the following code for retrieving staff records from a database table. The records from this table were populated from an Active Directory query via SQL Server. A sample partial code snippet to illustrate


<cffunction name="getStaff" access="remote" returnformat="json">
   <cfscript>
      var qStaff = "";
      var sStaff = {"data" : []};
      var jsStaff ="";
      var rowNum = 0;
   </cfscript>

   <cfquery name="qStaff" datasource="<your-datasource-name>">
      SELECT givenName,sn,mail
      FROM staff
   </cfquery>

   <cfscript>
      if (qGet.RecordCount gt 0){
         for (staff in qStaff){
            rowNum++;
            sStaff.data[rowNum] = {};                       
            StructInsert(sStaff.data[rowNum],"givenName",staff.givenName);
            StructInsert(sStaff.data[rowNum],"sn",staff.sn);
            StructInsert(sStaff.data[rowNum],"mail",staff.mail);  
         } //for
         jsStaff = serializeJSON(sStaff);
      }  //if
   </cfscript>

   <cfreturn jsStaff>
</cffunction>

Important notes

  • This function (method) must have this attribute:  returnformat=json
  • Convert the returned query records into a struct in the format required by datatables, i.e. {“data” : []}
  • Finally serialise this struct into JSON before returning it using ColdFusion function serializeJSON

Displaying the records on the front-end

On the front-end, let’s use datatables with its deferRender and AJAX options to load and display the records from the database table. Here’s a partial sample code to illustrate.


<table id="staff" class="table table-striped table-bordered table-hover" width="100%">
   <thead>
      <tr>
         <td>First Name</td>
         <td>Last Name</td>
         <td>Email</td>         
      </tr>
   </thead>
</table>

<script>
   $(document).ready(function() {
      $('#staff').dataTable({
         'deferRender': true,
         'ajax': {
            'url': 'staff.cfc?method=getStaff'
         },
         'columns': [            
            { 'data': 'givenName' },
            { 'data': 'sn' },
            { 'data': 'mail' }
         ]
      }); //dataTable
   });
</script>

Important notes

  • For the table, we only need to define the table header when using the AJAX option
  • We use datables’ columns option to match the table columns to the ajax returned record. Order is important.
  • For the datatables option set deferRender=true to defer rending for large dataset and hence improve loading time
  • Datatables’ ajax option is use to retrieve records from the database
  • The datatables’ twin options of ajax and deferRender is crucial for improving loading time for large data set on the front-end
Advertisements

Automate Import of Active Directory Records into SQL Server

Previously we detailed how to query Active Directory with the help of a linked server in SQL Server. Now let’s take it a step further by automating the process of retrieving the records from Active Directory and storing them in a SQL Server database using a job schedule.

To automate the import and refresh of Active Directory records into a SQL Server database, do the following:

  • Setup a linked server for connection to Active Directory
  • Create a store procedure to import records from Active Directory
  • Create a SQL Server Agent job to execute the store procedure

1. Setup a linked server for connection to Active Directory

If you have not already setup a linked server in SQL Server to connect to Active Directory, then refer to Query Active Directory in SQL Server with Linked Server for detailed steps.

2. Create a store procedure to import records from Active Directory

Now let’s create a T-SQL store procedure that will import the records from Active Directory. It must do the following:

  • Check to see if the required tables exist and create them if not.
  • Remove all data from the temp AD table if exists before importing
  • Query Active Directory and insert the returned records into a temp table
  • Refresh the current live table with the newly imported data
  • If there are issues, rollback the transaction

To create the store procedure, follow these steps:

  • In Microsoft SQL Server Management Studio, click on the database where the new store procedure will reside, then click New Query
  • In the new query window, enter the T-SQL script found below.
  • Save this new store procedure as usp_<your-store-procedure-name>
  • Confirm that the store procedure was created by:
    • Expanding Programmability > Stored Procedures
-- store procedure t-sql script, save as usp_<your-store-procedure-name>
USE <your-sp-database-name>
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.usp_<your-store-procedure-name>

AS
SET NOCOUNT ON;

IF OBJECT_ID('<your-ad-table-name>temp', 'U') IS NULL
BEGIN
  CREATE TABLE dbo.<your-ad-table-name>temp
  (
    SAMAccountName nvarchar(800) NULL,
    givenName nvarchar(800) NULL,
    sn nvarchar(800) NULL,
    mail nvarchar(800) NULL,
    telephoneNumber nvarchar(800) NULL
  );
END

IF OBJECT_ID('<your-ad-table-name>', 'U') IS NULL
BEGIN
  CREATE TABLE dbo.<your-ad-table-name>
  (
    SAMAccountName nvarchar(800) NULL,
    givenName nvarchar(800) NULL,
    sn nvarchar(800) NULL,
    mail nvarchar(800) NULL,
    telephoneNumber nvarchar(800) NULL
  );
END

BEGIN TRY
  BEGIN TRANSACTION
    IF EXISTS (SELECT SAMAccountName from dbo.<your-ad-table-name>temp)
      DELETE FROM dbo.<your-ad-table-name>temp;

    INSERT INTO dbo.<your-ad-table-name>temp (SAMAccountName,givenName,sn,mail,telephoneNumber)
    SELECT * FROM OpenQuery (
      ADSI,
      'SELECT telephoneNumber,mail,sn,givenName,SAMAccountName
       FROM ''LDAP://your.domain.com/OU=<your-ou>,DC=your,DC=domain,DC=com''
       WHERE objectClass = ''user''
       AND NOT objectClass = ''computer''
      ') AS tblADS
      ORDER BY SAMAccountName

    EXEC sp_rename '<your-ad-table-name>', '<your-ad-table-name>trans'
    EXEC sp_rename '<your-ad-table-name>temp', '<your-ad-table-name>'
    EXEC sp_rename '<your-ad-table-name>trans', '<your-ad-table-name>temp'

  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  ROLLBACK
END CATCH;

  

3. Create a SQL Server Agent job to execute the store procedure

Finally let’s create a SQL Server Agent job to automate the above steps. Here’s what you need to do within Microsoft SQL Server Management Studio:

  • Right click Jobs under SQL Server Agent, and select New Job…
  • Under the General page, enter the following:
    • Name
    • Owner
  • On the Steps page, click New… and enter or select the following:
    • Step name: Import Records
    • Type: Transact-SQL script (TSQL)
    • Database: master
    • Command:
      USE <your-store-procedure-database-name>
      GO
      EXEC dbo.usp_<your-store-procedure-name>
      GO
    • Click OK
  • On the Schedules page, click New… and enter or select the following:
    • Name: <Your schedule name>
    • Frequency: <Your frequency>
    • Daily Frequency : <Your daily frequency>
    • Duration: <Your duration>
    • Click OK
  • Confirm the AD table was populated with records from Active Directory after job has ran successfully as per your schedule.

Query Active Directory in SQL Server with Linked Server

Here’s a quick tip on how to query Active Directory in SQL Server with Linked Server using these three simple steps:

  • Ensure MS SQL Server Windows Service runs under correct account
  • Set up the Linked Server to Active Directory
  • Query Active Directory

1. Ensure MS SQL Server Windows Service runs under correct account

Launch Windows Services and confirm that MS SQL Server Windows service MSSQLSERVER is running under the Local System or an Active Directory domain account.

2. Set up the Linked Server to Active Directory

To create the linked server, do the following:

  • Launch SQL Server Management Studio (SSMS) and connect to the database from where you wish to query Active Directory
  • Expand Server Objects
  • Right click Linked Servers and select New Linked Server…
    • On the General page, enter the following:
    • Linked server: ADSI
    • Select Other data source
      • Provider: OLE DB Provider for Microsoft Directory Services
      • Product name: Active Directory Services
      • Data source: adsdatasource
      • Provider string: ADsDSOObject
  • On the Security page, enter the following
    • For a login not defined in the list above, connections will, select
      • Be made using the login’s current security context (if Active Directory server is in the same domain as the database server)
      • Be made using this security context (if Active Directory server is in a different domain to the database server), and fill in:
        • Remote login (this is the account name)
        • With password (this is the account logon password)
  • On the Server Options, use the default values
  • Click OK to create the new linked server

3, Query Active Directory

In SSMS, with the target database selected, create a new query, and use the following query to retrieve only user accounts data from Active Directory:


SELECT * FROM OpenQuery (
ADSI,
'SELECT mail, mobile, telephoneNumber, title, sn, givenName, SAMAccountName
FROM ''LDAP://OU=<your-users-ou>,DC=<your>,DC=<company>,DC=com''
WHERE objectClass = ''user''
AND NOT objectClass = ''computer''
'
) AS tblADS
ORDER BY SAMAccountName

Important note:

  • If Active Directory and database servers are in different domains, then you will need to specify the domain the LDAP string in the above query, i.e.
    FROM ”LDAP://my.domain.com/OU=<your-users-ou>,DC=<your>,DC=<domain>,DC=com”

 

How to Reorganize and Rebuild Database Indexes Using T-SQL

If you’re working with MS SQL Server 2008 databases, here are some handy tips on how to reorganize and rebuild indexes that are fragmented using T-SQL.

1. Determine Index Fragmentation

Firstly let’s determine the degree of fragmentation. We can either determine the fragmentation for all tables or a specific table in a database.

1a Determine fragmentation for all tables in a database


USE AdventureWorks2012;
GO

SELECT
  OBJECT_NAME(ips.OBJECT_ID)
  ,i.NAME ,ips.index_id ,index_type_desc
  ,avg_fragmentation_in_percent
  ,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

1b Determine fragmentation for a specific table in a database


USE AdventureWorks2012;
GO

SELECT 
  a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'),
OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
GO

The index’s fragmentation determines whether to use reorganize or rebuild:

  • Between 5% to 30%, use reorganize
  • Greater than 30%, use rebuild

2. Reorganize a Fragmented Index

If the fragmentation is between 5 and 30 percentage, then let’s reorganize the index. We can reorganize all indexes or just a specific index within a table.

2a Reorganize all indexes in a table


-- Reorganize all indexes on the HumanResources.Employee table.
USE AdventureWorks2012;
GO

ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ;
GO

2b Reorganize a single index in a table


-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode
-- index on the HumanResources.Employee table.
USE AdventureWorks2012;
GO

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
ON HumanResources.Employee
REORGANIZE
GO

3 Rebuild a Fragmented Index

If the fragmentation is over 30 percentage, then let’s rebuild the index. We can rebuild all indexes or just a specific index within a table.

3a Rebuild all indexes in a table


USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO

3b Rebuild a single index in a table


USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON dbo.Employee
REBUILD;
GO

4 Potential Issues

A potential issue you may encounter is related to page level locking. This will prevent a reorganize operation on an index if page level locking is disabled. You will see this error:

The index “<index-name>” (partition 1) on table “<table-name>” cannot be reorganized because page level locking is disabled.

To get around the above error, temporary enable page level locking, perform the database index reorganize, and then disable page level locking.


-- Enable row and page level locks
USE [database-name]
GO

ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_PAGE_LOCKS = ON )
ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_ROW_LOCKS = ON )

--Disable row and page level locks
USE [database-name]
GO

ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_PAGE_LOCKS = OFF )
ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_ROW_LOCKS = OFF )

If you want to check the index setting for a particular table (i.e. to see whether page level lock is enabled or disabled), use this query:


SELECT
  Indx.name, Indx.type_desc, Indx.is_disabled,
  Indx.allow_page_locks,Indx .allow_row_locks
FROM sys.indexes Indx
LEFT OUTER JOIN sys.objects OBJ ON OBJ.object_id=Indx.Object_id
WHERE OBJ.name ='Product'

Related Selects with jQuery AJAX Post in ColdBox

Let’s create a related select form elements using jQuery AJAX post in ColdBox in this quick tip. That is, the available drop down values that are shown in the second select element is dependent on the user selection of the first select element.

We will be working with  the following technologies:

  • jQuery 1.x
  • ColdBox 4.2.x
  • ColdFusion 10

We will assume the following data model and relationship (simplified for this article):

  • Models
    • facility (id,name)
    • department (id,name)
  • Relationship
    • a facility has many departments

Let’s create the methods that we will need in the handler, and save it as handlers\reports.cfc.


component {
  property name="reportService" inject="model";
  property name="facilityService" inject="model";
  property name="departmentService" inject="model";

  public void function submit(event){
    var rc = event.getCollection();
    
    //retrieve list of facilities
    rc.facilities = facilityService.list(asQuery=false);
  }//submit

  public void function getAllDepartments(event) {
    //this method retrieves the data that will be return in JSON format to the front end
    //must be an array before converting to JSON format

    var rc = event.getCollection();
    var cr = "";
    var departments = [];

    //retrieve list of departments based on selected facility
    cr = departmentService.newCriteria();
    cr.createAlias('facilities','f');
    cr.add(cr.restrictions.isEq("f.facilityID",javacast("int",rc.id)));
    rc.departments = cr.list(asQuery=false);

    if( !isNull(rc.departments) && ArrayLen(rc.departments) ){
      for (var i=1; i lte ArrayLen(rc.departments); i++){
        departments[i] = {};
        departments[i].id = rc.departments[i].getDepartmentID();
        departments[i].name = rc.departments[i].getName();
      }//for
    }//if

    event.renderData(type="JSON",data=departments,nolayout=true);
  }//getAllDepartments

}//component

Please note that values must be stored in an array before calling the renderData method with type of JSON.

In the report model’s view where the facility and department select drop downs are used,  add the following code for views/reports/submit.cfm:


<form id="frmReport" class="form-horizontal" method="post" action="#event.buildLink('reports.save')#">
  <div class="form-group required">
    <label for="facility" class="col-md-4 control-label">Facility</label>
    <div class="col-md-3">
      <select name="facility" id="facility" class="form-control">
        <option value="">Please select...</option>
        <cfloop array="#rc.facilities#" index="facility">
        <option value="#facility.getFacilityID()#">#facility.getName()#</option>
        </cfloop>
      </select>
    </div>
  </div>

  <div class="form-group required">
    <label for="department" class="col-md-4 control-label">Department</label>
    <div class="col-md-3">
      <select name="department" class="form-control" id="department">
      <option value="" selected>Please select...</option>
      </select>
    </div>
  </div>
</form>

Finally, at the bottom of submit.cfm, add the JavaScript that will populate second select drop down list base on user selection from the facility drop down (first select).


<script>
$(document).ready(function() {
   $('#facility').change(function(){
      var $selectedFacilityID = $(this).val();
      if($selectedFacilityID.length > 0){
         $.ajax({
            url:'<cfoutput>#event.buildLink("reports.getDepartmentsList")#</cfoutput>/id/' +
$selectedFacilityID,
            type: 'post',
            dataType: 'json',
            success: function(data){
               //id: data[i].ID
               //name: data[i].NAME
               //clear all options to start with
               $('#department').find('option').not(':first').remove();
               if (data.length > 0){            
                 //populate the departments drop down with returned values
                 for (i=0; i < data.length; i++){
                    $('#department').append('<option value="' + data[i].ID + '">' + data[i].NAME + '</option>');
                 } //for
               }//if
            },
            error : function(xhr,textStatus,errorThrown){
               console.log(xhr);
               console.log(textStatus);
               console.log(errorThrown);
            }
         }); //ajax post - getDepartmentsList
      } //if $selectedFacilityID.length
   }); //facility
}
</script>

We call the getDepartmentsList method using jQuery AJAX post and passing in the selected facility’s id to return its departments.

data is a variable used to store the  data from a successful AJAX post. This variable name can be changed. For example, sometimes it is called results. Use do notation to access the columns, and a for loop to loop through the returned records.

Resolving Transaction Log for Database SQLMonitorData is Full Error

If you log into Red Gate SQL Monitor and see the following error:

 

Then here’s what you need to do to resolve the issue. First, let see what’s going on with the transaction log by performing this query via SQL Server Management Studio (SSMS):


select log_reuse_wait_desc, * 
from sys.databases 
where name ='SQLMonitorData'

When you run the above query, you see an output like this (in the results pane below):

 

This means that a transaction log backup is required. To back up the transaction log, do the following (while still in SSMS):

  • Create a new maintenance plan for this database to backup the transaction log for database SQLMonitorData
  • In the maintenance plan, select only the transaction log for SQLMonitorData database to backup
  • Execute the maintenance plan to initiate the transaction log backup

That’s all there is to it. After a successful transaction log backup for the SQLMonitorData database, the above error should no longer show up, and you will be able to see the SQL Monitor dashboard.

A Better Multi-Select with Chosen jQuery Plugin in ColdBox

Let’s improve the user experience for selecting multiple items in a select drop down list by using the chosen jQuery plugin. In this guide we will use the example of selecting multiple suppliers for a product in a ColdBox 4.2.x web application.

Follow these steps to setup the plugin, the entities, and finally making use of the plugin with the select element:

  • Download the chosen jQuery Plugin
  • The entities used
  • Create a new method to retrieve list of ID for select drop down
  • In edit handlers, retrieve list of values for select drop down
  • Import the chosen jQuery plugin scripts and styles
  • Define the select drop down list
  • Use the chosen jQuery plugin

Download the chosen jQuery Plugin

Head over to https://harvesthq.github.io/chosen/ to download the chosen jQuery plugin and save it to a location of your choice on the web server.

The Entities Used

Let’s define the entities used in this guide.

Product Entity


component persistent="true" table="products" {
   property name="productID" fieldtype="id" generator="identity" setter="false";
   property name="name" sqltype="nvarchar" length="150";

   property name="suppliers" fieldtype="many-to-many" cfc="models.suppliers.Supplier" singularname="supplier" fkcolumn="productID" inversejoincolumn="supplierID" linktable="products_suppliers";

}

Supplier Entity


component persistent="true" table="suppliers" {
   property name="supplierID" fieldtype="id" generator="identity" setter="false";
   property name="name" sqltype="nvarchar" length="50";
}

Create new method to retrieve list of ID for select drop down

In the product entity (e.g. models\products.cfc), create a new method to retrieve list of ID for the select drop down list. In this example, we are retrieving the list of suppliers ID for a product. A product has many suppliers. In models\products\Product.cfc add the following code:


public string function getSuppliersIDList() {
   var suppliers = "";
   for ( var i=1; i<=arrayLen(getSuppliers()); ++i ) {
      suppliers = listAppend(suppliers,getSuppliers()[i].getSupplierID());
   }
   return suppliers;
}

Retrieve list of values for select drop down

In the products edit handler (handlers\Products.cfc), we need to retrieve the list of suppliers for populating the select drop down:


component {
   property name="productService" inject="model";
   property name="supplierService" inject="model";

   public void function edit(event){
      var rc = event.getCollection();
      event.paramValue("id","");
      rc.product = productService.get( rc.id );
      rc.suppliers = supplierService.list(sortOrder="name",asQuery=false);
   } //edit
} //component

Import the chosen jQuery Plugin scripts and styles

In the default Main layout (e.g. layouts\Main.cfm), add the following to import the chosen jQuery plugin scripts and styles:


<cfoutput>
   #html.doctype()#
   <html lang="en">
   <head>
   <meta http-equiv="X-UA-Compatible" content="IE=Edge">
   <meta charset="utf-8">
   <title>#getSetting('appName')#</title>
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <link href="/path/to/bootstrap.min.css" rel="stylesheet" type="text/css"/ >
   <link rel="stylesheet" href="/path/to/chosen.css" type="text/css"/ >
   </head>
   <body>
      <script src="/path/to/jquery.min.js"></script>
      <script src="/path/to/bootstrap.min.js"></script>
      <script src="/path/to/chosen.jquery.js" type="text/javascript"></script>
   </body>
   </html>
</cfoutput>

Define the select drop down

Edit views\products\edit.cfm and add the following:


<div class="form-group">
   <label for="suppliers" class="col-md-2 control-label">Suppliers &nbsp;</label>
   <div class="col-md-4">
      <select data-placeholder="Click or type to choose suppliers ..."
         name="suppliers" class="chosen-select form-control" multiple="multiple">
         <option value="">&nbsp;</option>
         <cfloop array="#rc.suppliers#" index="supplier">
            <option value="#supplier.getSupplierID()#">#supplier.getName()#</option>
         </cfloop>
      </select>
   </div>
</div>

Ensure the following is added on the select form element:

  • data-placeholder
  • class=”chosen=selelct”
  • multiple=”multiple”

Use the chosen jQuery Plugin

In the same edit view, i.e. views\products\edit.cfm, towards the bottom of the file before the closing body tag, add:


<script>
   <cfoutput>
   //retrieve selected ID for pre-population
   var #toScript(rc.product.getSuppliersIDList(),'lSuppliersID')#;
   var aSuppliersID = lSuppliersID.split(',');
   </cfoutput>

   $(document).ready(function() {
      //use chosenJS for multi select
      $('.chosen-select').chosen({width:'100%'});

      if (lSuppliersID != ''){
         //pre-populate selected supplier for edits
         $('.chosen-select').val(aSuppliersID).trigger('chosen:updated');
      }
   });
</script>

In lines 3-5, we retrieve the list of supplier ID for pre-populating a form when editing (i.e. the suppliers were already selected), and then on line 14 we must trigger an update to add the selected value to the multi-select box. On line 10 we activate the chosen jQuery plugin

You should see something like these if the chosen jQuery plugin is working as expected: