Bypass maximum number of parameters

Data Abstract
1

I use a huge amount of parameters for certain (maintenance) queries

However I run into a limitation of RO/DA

image

Is there a way to bypass this?

It’s used as an ‘IN’ SQL where clause.

var listWhereExpression = new List<RemObjects.DataAbstract.Expressions.WhereExpression>();
foreach (int pk in fetchPrimkeyList)
listWhereExpression.Add(new RemObjects.DataAbstract.Expressions.ConstantExpression(pk, RemObjects.DataAbstract.Schema.DataType.Integer));
var listExpression = new RemObjects.DataAbstract.Expressions.ListExpression(listWhereExpression.ToArray());
var condition = new RemObjects.DataAbstract.Expressions.BinaryExpression(
new RemObjects.DataAbstract.Expressions.FieldExpression(KeyFieldName), listExpression, RemObjects.DataAbstract.Expressions.BinaryOperator.In);
whereClauseXml = condition.ToXmlNode();

2

Hi,

this is standard limitation of MSSQL.

from Maximum capacity specifications for SQL Server - SQL Server | Microsoft Learn

SQL Server Database Engine object Maximum values for SQL Server (64-bit)
…
Parameters per stored procedure 2,100
Parameters per user-defined function 2,100
3

Hello Evgeny,

  1. It does not look like a stored proc is used to do the fetch
    This is the SQL
    SELECT xxx
    FROM yyy
    WHERE ([prg_primkey] IN (@DAPARAM_1, @DAPARAM_2, …

  2. I don’t want to use a stored procedure
    Any way of telling DA to not use a stored proc?

  3. I have an alternate solution for my custom where clauses
    I use an identifier resolver
    EventHandler resolveIdentifier = (IChannelSender, e) =>…
    that’s hooked to DataAbstractService.UnknownSqlMacroIdentifier

which replaces my own {ExtraWhereClause} variable.
This works without limitation but is inconvenient because:

  • The DA schema GUI application does not allow “Preview Data” or other execution when the identifier is there
  • Easy to forget the identifier in the SQL query
    image
    image746×187 10.6 KB

Is there a way to use a custom where clause, e.g. by changing the SQL in the schema at runtime ({where} becomes {where} and {extrawhereclause}
Or failing that is there a way to add a custom identifier that the DA schema modeler ignores?

4

Hi,

Can you launch MSSQL profiler and see what actual SQL is called and why > 2100 params are used, pls?

Why you can’t use MacroExpression for passing custom macro?

5

I didn’t know about macro expressions yet

I read MacroExpression

but the example there does not apply to my situation (furthermore I fail to see how the macro expression leads to SUBSTRING(…)

Do you have an example macro expression for

WHERE fieldX IN (1,5,7)

6

Hi,

better to use ListExpression :

WhereExpression where =
    new BinaryExpression(
        new FieldExpression("","fieldX"),
        new ListExpression(
            new WhereExpression[]{ 
                new ConstantExpression(1),
                new ConstantExpression(5),
                new ConstantExpression(7) 
            }),
            BinaryOperator.In);

how many items in IN list?

7

I believe this is the method I was already using but that fails due to the argument limit (= 2100 in my case)

         // FVC 12/2024: This solution does not require the {ExtraWhereClause} in the SQL but is limited to ~2100 parameters - see https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/3
          var listWhereExpression = new List<RemObjects.DataAbstract.Expressions.WhereExpression>();
          foreach (int pk in fetchPrimkeyList)
            listWhereExpression.Add(new RemObjects.DataAbstract.Expressions.ConstantExpression(pk, RemObjects.DataAbstract.Schema.DataType.Integer));
          var listExpression = new RemObjects.DataAbstract.Expressions.ListExpression(listWhereExpression.ToArray());
          var condition = new RemObjects.DataAbstract.Expressions.BinaryExpression(
            new RemObjects.DataAbstract.Expressions.FieldExpression(KeyFieldName), listExpression, RemObjects.DataAbstract.Expressions.BinaryOperator.In);
          whereClauseXml = condition.ToXmlNode();
8

Hi,

> 2100?

9

Yes, 7000

10

Is it possible to reduce this list?

you can use combination of BetweenExpression and ListExpression like

  • 20, 22…29, 31…50
(field BETWEEN 20 AND 50) and not (field IN [21,30])

-20…50,67,79

(field BETWEEN 20 AND 50) or (field IN [67,79])
11

Given the data values that would not guarantee that the number of parameters is always less then 2100

12

Hi,

try to use workaround from this testcase:
testcase.zip (17.0 KB)

it passes WhereExpression from client-side, transforms it to plain SQL and replaces custom macro with this plain sql.

this is safe for field IN (array of integers) expression but can be dangerous with string values.