missing the piece of code where i am unable to generate the results in pagination and writing file

[gurpreetr6076832]

I have this code for CF where i am trying to fetch the rows of the tables by minimizing the load on server

<cfset var offset = 0>
<cfset listoverloop = "users,roles,adresses,email">

<cfloop list="#loopoverLst#" index="k">
<cfquery name="local.sTotalRecords" attributeCollection="#variables.dataAttr#">
	        		select count(1) as totalRecords 
	        		from #table#
	        	</cfquery>
	        	<cfset local.offsetrequired = local.sTotalRecords.totalRecords gte 1000 ? 'yes' : 'no'> 
	        	<cfset local.rowsTofetch = "1500">
		        <cfquery name="insertData" attributeCollection="#variables.dataAttr#">
		          SELECT 
		          	 * FROM #k# 
ORDER BY 1 DESC 
		          	OFFSET #local.offset# ROWS
						FETCH NEXT #local.rowsTofetch# ROWS ONLY 

<cfsavecontent variable="createTt">
<cfloop query="insertData">
 write my inserts 

<cfset local.offset += local.rowsTofetch>
</cfloop>
</cfsavecontent>


<cfif FileExists("#ExpandPath('#table#.sql')#")>
	        	<cffile action="append" file="#ExpandPath('#table#.sql')#" output="#createTt#">
	        <cfelse>
	        	<cffile action="write" file="#ExpandPath('#table#.sql')#" output="#createTt#">
	        </cfif>

but it is not doing any pagination for the records, because i am writing the records in the files

[BKBK]

It is not doing any pagination for the records because you haven't used the boolean condition local.offsetrequired anywhere.

[gurpreetr6076832]

But loca.offsetrequired is a condition to check if the records are more than 1000 and if yes, then do the pagination else just query the table adn write the results, 

The code is basically t create the files of every table with 15500 records in it and if they are more, create more files with same tablename and an incremental value

[BKBK]

Sorry, I don't understand. How, and where in the code, is pagination expected to occur?

What does the above code do (that you don't expect it to)? What do you want it to do?

[gurpreetr6076832]

I had retracted the code and make it simple first to see if i get the data in the file system, if yes and without load on server, then i will go ahead

My new code

<cfset var maxRowsPerIteration = 5000>
		<cfset var rowsOffset = 0>
		<cfloop list="#arguments.tableList#" index="t">
			<cfset sname=  t & ".json">

			<cfset k = sname>
		    <!--- create empty file to append lines later --->
		    <cfset fileWrite(k, "")>
		   
		    <!--- infinite loop will be terminated as soon the query no longer returns any rows --->
		    <cfset sleep(500)>
		    <cflock timeout="100" type="exclusive">
		    <cfloop condition="true">
		        <!--- fetch a slice of the full table --->
		        <cfquery name="qry">
		            select * from #t# 
		            order by 1 desc 
		            OFFSET #rowsOffset# ROWS FETCH NEXT #maxRowsPerIteration# ROWS ONLY
		        </cfquery>
		        <cfset rowJSON = serializeJSON(qry,'struct')>	
		        <cfif not qry.recordCount>
		            <cfbreak>
		        </cfif>
		        <cfset rowsOffset += maxRowsPerIteration>
		        <cfset fileAppend(k, rowJSON, "UTF-8")>	
		    </cfloop>
			</cflock>
		    
		</cfloop>

with the above, it only writes to one table json and other one goes wmpty, i think if i can make this work, other one i asked as the question will be same as cuh

[BKBK]

I still wonder what's going on. But I hope the following simplification helps.

<cfset var maxRowsPerIteration = 5000>
<cfset var rowsOffset = 0>
<cfloop list="#arguments.tableList#" index="tableName">
	<cfset sname=tableName & ".json">

	<!--- create empty file to append lines later --->
	<cfset filePath=expandPath(sname)>
	<cfset fileWrite(filePath, "")>
	
	<!--- infinite loop will be terminated as soon the query no longer returns any rows --->
	<cfset isQueryEmpty=false>

	<cfloop condition="not isQueryEmpty">
		<!--- fetch a slice of the full table --->
		<cfquery name="qry" datasource="myDSN">
			select * from #tableName# 
			order by 1 desc 
			OFFSET #rowsOffset# ROWS FETCH NEXT #maxRowsPerIteration# ROWS ONLY
		</cfquery>

		<cfset isQueryEmpty=qry.recordCount GT 0 ? false : true>
		
		<cfif not isQueryEmpty>
                   <cfset rowJSON = serializeJSON(qry)>	
			<cfset rowsOffset += maxRowsPerIteration>
			<cfset fileAppend(filePath, rowJSON, "UTF-8")>
		</cfif>			
	</cfloop>
</cfloop>

[gurpreetr6076832]

problem remains the same, i choose 3 tables and it created the json file for all 3 but only populated one of the files and the last one selected is only populated

[gurpreetr6076832]

did some tests, it sems if the records are less than 5000, it does not execute the code for those tables having less than 5000 records and does not write the data in the files

[BKBK]

Shouldn't the rowsOffset and maxRowsPerIteration initialisation code be inside the top loop? That is, is this perhaps what you're aiming for:

<cfloop list="#arguments.tableList#" index="tableName">
    <cfset var maxRowsPerIteration = 5000>
    <cfset var rowsOffset = 0>
    ...