I am getting my data from sql server and i am having trouble when i am trying to send back a proper json format, going to use in datatables

{"draw": <cfoutput>#val(draw)#</cfoutput>,
"recordsTotal": <cfoutput>#recordsTotal#</cfoutput>,
"recordsFiltered": <cfoutput>#qFiltered.recordCount#</cfoutput>,
"data": [
<cfif qFiltered.recordcount gt 0>
<cfoutput query="qFiltered" startrow="#val(start+1)#" maxrows="#val(length)#">
    <cfif currentRow gt (start+1)>,</cfif>
        #Replace(SerializeJSON(qFiltered,'struct'),'\','','all')#,
</cfoutput> 
<cfelse>
    ""
</cfif>
]
}

someehow the code is ending in invalid nested data, not sure what i am doing wrong here

Is this a string? You should really create your structs and arrays as structs and arrays, then use

serializeJSON()

to convert to JSON.

Another week. Another person building JSON strings by hand. 😕 So... first things first.. change your approach to build the data structure *using actual data structure*s, and then come back with code and an clear explanation of what you expect and what you are getting instead.

If you really want to create it by hand, use the CHR(ASCII NUMBER) for each and every character in your code, so your eyes will bleed. Or use taffy. The taffy could would be roughly something like -- Update... <cfset draw = val(draw)> <cfset recordsTotal = val(recordsTotal)> <cfset recordsFiltered = val(qFiltered.recordCount)> <cfset data = []> <cfif qFiltered.recordCount gt 0> <cfloop query="qFiltered" startrow="#val(start+1)#" maxrows="#val(length)#"> <cfset arrayAppend(data, SerializeJSON(qFiltered, 'struct'))> </cfloop> <cfelse> <cfset data = []> </cfif> <cfset taffy = createObject("component", "path.to.Taffy")> <cfset response = taffy.getResponse()> <cfset response.setData(data)> <cfoutput> { "draw": #draw#, "recordsTotal": #recordsTotal#, "recordsFiltered": #recordsFiltered#, "data": #response.getResponseJSON()# } </cfoutput>

As always, it would also be helpful to know what engine and version you are running. As already mentioned, definitely start from a structure, it is far too easy to cause the string building to fail. Also, what you are putting into recordsFiltered indicates that the query already holds the correct rows. therefore the startrow and maxrows on the loop wouldn't have been necessary. this gist should work for lucee5 or 2021 https://trycf.com/gist/11fa48f2cdc186516ef1dfaf4fbdede6/lucee5?theme=monokai

thanks

@Simone when you have an issue with a function... read the docs. The docs for

isDefined

answers your question: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-in-k/isdefined.html

what I tend to do here, is create an array with my columns that matches the order of columns that you are presenting in the datatable on the front end. datatables is sending you potentially multiple items for sorting, potentially as many as you have columns, along with a direction, since it allows multiple column sort, and you are trying to translate that to what you need to put in the query

columns = ["your", "column", "names", "in", "order"];
order_clause = "";

// parse the stuff datatables passed to us
for ( sortColumn=0; sortColumn lt arrayLen(columns); sortColumn++  ) {
	if ( structKeyExists(form, "order[#sortColumn#][column]") && isValid("integer", form["order[#sortColumn#][column]"]) ) {
		order_clause = listAppend(order_clause, columns[ form["order[#sortColumn#][column]"]+1 ] );
		if ( structKeyExists(form, "order[#sortColumn#][dir]") && listFindNoCase("asc,desc", form["order[#sortColumn#][dir]"]) ) {
			order_clause = listAppend(order_clause, form["order[#sortColumn#][dir]"], " " )
		}
	}
}
if( !len( order_clause ) ){
	order_clause = columns[1] & " asc";
}

order_clause = " order by " & order_clause;

@Matt Jones what you wrote is nice, but using stored procedure, have to pass only numeric value and only asc/desc

Add the returnType="array" then serialize that variable for an array of structures I didn't test for backwards compatibility and wrote this with CF2021. I did my best for CFTag variation and tested a simple implementation works in both. Hope this helps! Let me know if you have any questions.

<cfscript>
	// Get Your Data from CFQuery
	// I am using a memory query in this example but it can me replaced with a real query
	// The memory query was created from a JSON response that when deserialized returns an array of structured
	// This example is just doing the opposite logic
	
	
	// Get The DataSet
	dataSet = QueryExecute(
		"SELECT * FROM RandomMemoryQuery",
		{ 
			// Only set for 
			DBType = "query", 	 
			// Change the return type from query to array
			returnType = "array" 
		}
	)
	
	// Data Manipulation Logic. For example add a unique key value pair using CreateUUID()
	
	// Serialize the data
	ResfulQueryData = SerializeJSON( dataSet ) 
	
</cfscript>

<cfquery name="dataSet" DBType="query" returnType="array">
    SELECT * FROM RandomMemoryQuery
</cfquery>

<cfoutput>
    <!--- Data Manipulation Logic --->
    <cfset ResfulQueryData = SerializeJSON( dataSet ) />
</cfoutput>

this is my try https://pastebin.com/0TqHePK4 its working as it displays first 10 records, but its not forming any pagination, there are around 7000 records

what do you mean by 'its not forming any pagination'? are you talking about the sp isn't returning page 2/3 or whatever when you ask it to? I can't really help with the sp much. are you talking about stuff related to DataTable() interface? like the paging controls don't appear? It doesn't look like your config is wrong. are you talking about you click to see page 2 but still see page 1? if so, I can probably help, Inspect your network graph, look specifically at the fetch/xhr and check response for "recordsTotal: , recordsFiltered: , draw:" recordsTotal should be full table count recordsFiltered should be amount from the search, but if nothing being searched, they are likely same. draw will start as 1. then if you do anysort/search/paging click, re-inspect it If your 'draw' has not changed, then you are getting DataTables not feeling that it has updated data, what draw should contain will match what you see in the request from DataTables

you are right, something is wrong, i think it shows me 1. draw - 1 2. recordsFiltered - 10 3. recordsTotal - 6956 the filtered is nothing as of now, no search

If you are using data tables (jslib), query all the data, cache the query and finally render the cached data to the datatables . cachedwithin , its the attribute you should be setting when firing off your initial data query. Now if the data is being rendered in Datatables.js just add the lib and html markup around a simple cfloop and the data will be displayed with pagenation inside datatables.

If anyone uses visual studio code. Can add this to your key bindings to get a data table quickly { "key": "ctrl+alt+t", "command": "type", "args": { "text": "<cfset dtName = 'dataTable' />\r\n<table class=\"table table-bordered table-responsive table-striped mg-b-0 mg-b-0\" id=\"#dtName#\">\r\n\t<thead class=\"bg-gray-800 tx-white\">\r\n\t\t<tr>\r\n\t\t\t<th></th>\r\n\t\t</tr>\r\n\t</thead>\r\n\t<tbody>\r\n\t\t<tr>\r\n\t\t\t<td></td>\r\n\t\t</tr>\r\n\t</tbody>\r\n</table>\r\n\t<cfinclude template=\"/framework/includes/dtFooter.cfm\" />" }, "when" : "editorTextFocus" } (Needs to be updated a bit to fit your environment)

Thanks @mbh i will update if needed, but can we check n the issue i am having right now where it is showing showing pagination at all, we can debug that one

if its datatables js, the number of header columns must match output,

the data is displayed properly with headers, no issues in that, i think its something with length, because it just showing 10 records instead of all 7000 records

"lengthChange": 1000, "pageLength": 1000

he would need it to be 10000,

I have ran into that before, and the selector on the top left was still only able to max out at 100 ( I didnt chase further but I was able to get it to default to higher then 10 records )

set the default to ALL,

its not in JS

can you dump a gist of the page you are trying to render

<cfparam name="form.length" DEFAULT="10" TYPE="integer" />

I dont' see anyplace where you alter this, but seems like it might be related?

its passed in the paramter to the stored proc

Yes passed, but where is it modified? You are getting 10 records, but expecting more, yes?

right

So, where do you ever update

form.length

?

its happening in Stored proc

form.length

is passed as

@pageSize

, right?

the pagination has to be in sql server

RIght, but you are passing how many records to retrieve from the client and as far as I can tell, you are only ever telling it to retrieve 10 records

Why ajax bind this to 10 records, to call a stored procedure, instead, query all the records, dump all the records to a table, wrap the table in datatables, BOOM, done. Or you can even go more basic and just run a vanilla call for your records via javascript,,,, <cfparam name="section" default=""> <cfparam name="draw" default="1" type="integer"> <cfparam name="form.start" default="1" type="integer"> <cfparam name="form.length" default="10" type="integer"> <cfparam name="form.search" default="" type="string"> <cfif form.SEARCH neq '' AND len(form["search[value]"]) gt 0> <cfset form.SEARCH = form["search[value]"]> </cfif> <!--- Data set ---> <cfswitch expression="#section#"> <cfcase value="users"> <cfquery name="qData"> SELECT * FROM users </cfquery> </cfcase> </cfswitch> <!--- Apply searching ---> <cfif form.SEARCH neq ''> <cfset qFiltered = qData.search(form.SEARCH)> <cfset totalRecords = qFiltered.recordCount> </cfif> <!--- Apply pagination ---> <cfset startRow = (form.start - 1) * form.length + 1> <cfset endRow = startRow + form.length - 1> <cfset qPaged = qFiltered ? qFiltered.slice(startRow, endRow) : qData.slice(startRow, endRow)> <!--- Output the table ---> <table> <thead> <!--- Table headers here ---> <tr> <th>Column 1</th> <th>Column 2</th> <th>Column 3</th> <!--- ... ---> </tr> </thead> <tbody> <!--- Table data here ---> <cfoutput query="qPaged"> <tr> <td>#column1#</td> <td>#column2#</td> <td>#column3#</td> <!--- ... ---> </tr> </cfoutput> </tbody> </table> <!--- Total data set length ---> <cfif qFiltered> <cfset qCount = totalRecords> <cfelse> <cfset qCount = qData.recordCount> </cfif> <!--- Calculate number of pages ---> <cfset totalPages = ceil(qCount / form.length)> <!--- Output simple pager ---> <div> <cfloop from="1" to="#totalPages#" index="page"> <a href="dtAjaxData.cfm?section=users&start=#page#" class="pager">#page#</a> </cfloop> </div> JS Code $(".pager").on("click", function(e) { e.preventDefault(); var start = $(this).attr("href").split("=")[1]; $(".table").DataTable().page(start - 1).draw(false); });

@Evil Ware why we can't do the Stored proc way, i am just trying to figure out what is happening in my code,

for paging, by default, 10 records is appropriate, you should be sending back the number of records that the dropdown shows on the control above the table. Datatables will send that to your endpoint as length, if it sends -1, then you requested all rows.

Stored procedures are for factory formatting data sets in a regular, orderly fashion. It should always be a send order for pizza, get pizza precisely ordered and delivered. CFstoredProc has no cachedwithin attribute, so you are making your cf and your db server do extra work it doesnt need to do. So instead, do a query, or doview, or do a query of a view, but all will be better suited than your procedure. Second, you are trying to cross dom script an ajax bind with datatables, and that gets ultra messy. So before you invent the next greatest thing start out basic, get all data with a query, if that works then wrap it with datatables, see the documentation here: https://datatables.net/examples/basic_init/zero_configuration.html

@Evil Ware i am not decrusting anything, a legend in cf told me once that this is the right way of doing it, queries are often slow so always prefer stored procs, and i have seen this usually works, but in this case, definately a point i am missing which is just returning only 10 records instead of pagination proper

I am not saying they are wrong,, but 10 records, at a time with a procedure is like ordering each grain of sand, to fill a sandbox, and paying the delivery charge for each grain.

10 records is proper, as long as when you ask for the next page, you get the correct next 10 records

It's only returning 10 records because you only ever pass 10 as the

pageSize

parameter. Nowhere in the code that you have shared does that value, stemming from

form.length

, ever change.

datatables sends length to the endpoint

Matt, look at his gist, his procedure is NOT a complex data model, its a freaking table, and your suggestion is , in a word, inefficient.

it really depends on the data, in almost every application we use it, when we exceed ~ 2000 rows, we end up switching to server side data, and it works much better.

what i work on exceeds far more than that, its where on the fly views come in great, but none the less... This would be something code wise that would work, <cfquery name="qData" cachedWithin="#CreateTimeSpan(0, 0, 20, 0)#"> SELECT id, name, email, department FROM users </cfquery> <cfquery name="qCount" dbtype="query"> SELECT COUNT(*) AS recordCount FROM qData </cfquery> <cfparam name="page" default=""> <cfset sortOrder = ""> <cfif structKeyExists(url, "page") AND isNumeric(url.page)> <cfset page = url.page> <cfset sortOrder = " ORDER BY " & qData.getColumnNames()[page] & " ASC"> </cfif> <cfquery name="qPaged" dbtype="query"> SELECT * FROM qData <cfif sortOrder neq ""> <cfset sortOrder = sortOrder & ", id ASC"> <!-- Add fallback sorting if page parameter is invalid or not provided --> </cfif> <cfset sortOrder = sortOrder & " OFFSET " & ((page - 1) * form.length) & " ROWS FETCH NEXT " & form.length & " ROWS ONLY"> #sortOrder# </cfquery> <cfoutput> <table> <thead> <tr> <th>ID</th> <th>Name</th> <th>Email</th> <th>Department</th> </tr> </thead> <tbody> <cfoutput query="qPaged"> <tr> <td>#id#</td> <td>#name#</td> <td>#email#</td> <td>#department#</td> </tr> </cfoutput> </tbody> </table> </cfoutput>

if you have a small data set, decorating the table is the much easier/cleaner choice. but, we regularly hit were the datatables initialization on larger tables just dies or is unacceptably slow. I go incrementally though. small sets i render html and tell datatables to do its thing, when that starts to slow, i will switch to pass the full set as json, when that starts to be a problem, switch to server side. this definitely is the most effort, but works on any set we have.

EhCache and cfcacheDir are you friends. You can for example in a MASS , i have large datasets of data, dump the data to cached files, then read the files as requested.. Really gets into more actual reporting than just a simple table with 20K data objects

bottleneck we see which prompts our switch isn't from cf/lucee side, it's at the browser. additionally, on a paging table, i rarely have a use case that justifies sending all records back anyway.

I went off his example, as for the switch, most of the time rarely do users actually all data instantly. They want summaries, and the details come later. So you create a report with a summary then run the details off the cached data.

It seems he is looking for a fix in the code as he pointed out And I read all the comments and what I think is the length he is sending is somewhat wrong I have not clear answer but something is quite related with length

datatables sends: "length" based on what was selected in the page size dropdown (10 is the default) "start" based on what page and page size (0 is default for offset to first record) these would be used in the code (assuming sql server) offset [start] rows fetch next [length] rows only

i think we are all going in different directions

Is there more code than what you have posted here that calls the stored procedure? Otherwise,

form. Length

, which is what is being passed as

pageSize

to your sp, will always be 10.

that is the whole code i have

Exactly.

hmm, let me try that

Is there any way you can see what's being passed to your SP? Even if you select it in your query?

why do you think you should be getting more than 10 rows? is the code in the pastebin your exact code? if so, what does the 1 here indicate? getUsers(form.SEARCH,1,LENGTH,form.searchColumn,form.searchOrder) Also, the initial start from datatables should be 0 and not 1, and you shouldn't require any page math for the offset (literally the start from datatables will work in sql server for your @recordfrom). If you are doing server side paging, you should only get whatever count of records that are indicated by your @pagesize/length. 'OFFSET @RecordFrom ROWS FETCH NEXT @PageSize ROWS ONLY' the 'fetch next @PageSize rows only' tells sql server to only give you 10 rows, when your @PageSize is 10, offset says how far to shift from the beginning of the results. 0 means start with the first record, 10 would be the offset/start for page2 if the @pagesize was 10.

i managed to fix it, it has nothing related to sp or javascript, it was something with length which i was doubtful since first time

Did you read the "for more information" link?

i did chcked but its not connected because i have the same order and same number of rows and columns and defined the columnDefs

Honestly, I'm not familiar with DataTables, but how is it supposed to know what to request for

parameter 'activities'

?

had it working