Using $COUNT with ODATA

Data Abstract
1

Hello,

I try to use $COUNT with odata like this
http://localhost:7019/Api/Products/$COUNT and seems to be ok.

But if I try this to an large datatable for proper calculation of paginations, I get

<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<code/>
<message xml:lang="en-US">Out of memory</message>
</error>

Second execution of the same request return error:

<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<code/>
<message xml:lang="en-US">Assertion failure (C:\wGit\DataLightXE\Lib\RemObjectsDataAbstract\Source\uDASpiderMonkeyScriptProvider.pas, line 2439)</message>
</error>

It is like load all data in memory and then count records.

Wich is the corect way to get count of an ODATA interogation?

Best regards,
Tiberiu Stoicescu

2

Hi,

what statement type are you use? SQL or AutoSQL?

as I can see, for AutoSQL we generate request like

SELECT
    COUNT(*) as ColumnALL
FROM
   Customers

for SQL type we calculate this value with first/next.

3

I use SQL. If the dataset is one-way, calculating value by value with first/next I think is ok.
If the dataset is in memory, for large data sets, it will not cope,
For large data sets, I mainly use the SQL version because filters / stored procedures can be used for efficient filtering.

I don’t know how it works on other databases, but for FirebirdSQL in the case of SQL, an option would be:

SELECT
     COUNT(*) as ColumnALL
FROM (
     {All SQL content with joins, parameters and dynamic sql as is edited}
  )
4

Hi,

it won’t work in custom SQLs that contain ORDER BY clause:

1769×448 15.9 KB

I can suggest to use AutoSQL mode for such tables.

Note: you can apply predefined filters via Dynamic Where property:

1771×1334 118 KB