Server-Side Processing with MS SQL Database

djackiem

Hi! I frequently use Datatables with CodeIgniter and bring my data in via an ajax call from a Microsoft SQL database. It works great and I love it. However, I'm currently working on a project with a rather large MS SQL database and I'm thinking it would be best to use the Datatables server-side processing functionality.

I looked at the server-side processing example, but I don't see where to put my query. The example shows a table and columns, but my particular query is a bit more complicated -- it spans two different databases and has subselects.

I've also looked at the Ignited Datatables plug-in, but it doesn't appear to support MS SQL, and I need my query to be a regular SQL query rather than an active record style query.

Does anyone have any suggestions on how to deal with this situation?

Thanks.

numberone

Can you show the query ?, so we can help

Ignited Datatables for Codeigniter is using ci's activerecords so mssql driver should work as well. Only native php version is supporting just mysql now.

Can't tell anything without seeing your code atm.

fbas

to use server side processing, set bServer: true and set your url in sAjaxSource. datatables passes parameters to your server side. you craft your sql queries in the server side script.

The default parameters sent to the server are described in: http://www.datatables.net/usage/server-side (you can add custom parameters with fnServerData() ... see below)

as in the server side example (server side php code on http://www.datatables.net/release-datatables/examples/server_side/server_side.html) you will create a query from the sColumns passed into it, and take into account filtering based on sSearch, sSearch_{int}, iSort_{int}, etc

if you would prefer, you can take the reins and overwrite the fnServerData() callback to add parameters to the AJAX call, including sending a client-side created query and passing it as a single parameter, if you wish. obviously you need to write your server side code to accept those parameters. see fnServerData on http://www.datatables.net/ref

Feel free to ask more questions.

ps. it's worth noting that you can set your columns with sName which are effectively your database column names on the server side (and use sTitle for the client-side display header for the column). for multiple tables, you could specify a table name as part of the column using "sName": "table1.colname" so that your sColumns already have the info you need when you put the columns into your SELECT clause.

djackiem

@fbas: Thank you for your suggestions. I will try it out later on this evening and let you know how it goes!

@numberone: Here is my query. It queries two databases on the same server. I have a feeling that I can't squeeze this sort of query into the active records style query (but I may be wrong).

[code]
Select E_Sublease.SubleaseId, CityId, empoffices.OfficeName AS CityName, SubleaseStreetAddress, SubleaseCity, StateAbbr, PremisesSF, E_Sublease.DateModified, E_Sublease.DateCreated, primaryBroker.LastName, primaryBroker.FirstName
FROM E_Sublease
left outer join [EMP]..Offices AS empoffices ON empoffices.OfficeID = E_Sublease.CityId
left outer join DIC_StateProvince on DIC_StateProvince.StateId = E_Sublease.SubleaseStateId
left outer join
(Select SubleaseId, empemployees.LastName AS LastName, empemployees.FirstName AS FirstName
FROM E_Brokers
left outer join [EMP]..Employees AS empemployees ON empemployees.EmployeeID = E_Brokers.BrokerId
WHERE IsPrimaryBroker = 1) primaryBroker
ON E_Sublease.SubleaseId = primaryBroker.SubleaseId
WHERE E_Sublease.IsObsolete = 0 AND PostOnWebsite = 1
ORDER BY CityName ASC, SubleaseStreetAddress ASC
[/code]
'

numberone

Can you try the code below with Ignited Datatables for Codeigniter? Multiple tables and subqueries should run well. The only thing im not sure is connecting the second database. It may give errors.

[code]
$this->load->library("Datatables");
$this->datatables
->select('E_Sublease.SubleaseId as SubleaseId, CityId, empoffices.OfficeName AS CityName, SubleaseStreetAddress, SubleaseCity, StateAbbr, PremisesSF, E_Sublease.DateModified as DateModified, E_Sublease.DateCreated as DateCreated, primaryBroker.LastName as LastName, primaryBroker.FirstName as FirstName')
->from('E_Sublease')
->join('[EMP]..Offices AS empoffices', 'empoffices.OfficeID = E_Sublease.CityId', 'left outer' )
->join('DIC_StateProvince', 'DIC_StateProvince.StateId = E_Sublease.SubleaseStateId', 'left outer' )
->join('(Select SubleaseId, empemployees.LastName AS LastName, empemployees.FirstName AS FirstName
FROM E_Brokers
left outer join [EMP]..Employees AS empemployees ON empemployees.EmployeeID = E_Brokers.BrokerId
WHERE IsPrimaryBroker = 1) AS primaryBroker', 'E_Sublease.SubleaseId = primaryBroker.SubleaseId', 'left outer' )
->where('E_Sublease.IsObsolete', '0')
->where('PostOnWebsite', '1');
echo $this->datatables->generate();
[/code]

Regards,
Yusuf

azzalea

i already done the datatables server side processing example(mssql) but manage to do for one table only..what if i watn to joint my query?..dont get the idea..please help..