Tip: Using paging table data with MSSQL

[rbartholomay]

Perhaps someone have the same problem like me:

I try to read a complete external MSSQL-Database Table. The Table is really big so i decide to read this via forPage like this (sample code):

      const countResult = await Database.connection('my_specialconnection')
        .query()
        .count('* as total')
        .from('dbo.skills')
        .first()

        const chunkSize = 5000

        for (let index = 0; index < Math.ceil(countResult.total / chunkSize); index++) {
          const chunkData = await Database.connection('my_specialconnection')
            .query()
            .from('dbo.skills')
            .forPage(index + 1, chunkSize)

          // [do something with the data]
        }

In my case i get an error: "Invalid usage of the option NEXT in the FETCH statement" It seems that MSSQL always need a default sort order! When i add .orderBy('id') all runs fine.

        for (let index = 0; index < Math.ceil(countResult.total / chunkSize); index++) {
          const chunkData = await Database.connection('my_specialconnection')
            .query()
            .from('dbo.skills')
            .orderBy('id')
            .forPage(index + 1, chunkSize)

          // [do something with the data]
        }

Perhaps some find this useful...

Have a nice day!

René