From magazine topic to project implementation
Relevant service and technical pages for this post
Video-Botschaft
FireDAC: Parameter arrays for IN clauses – safe expansion logic, per-DB limits and debugging of the final SQL
Warum IN (:ids) mit FireDAC im Betrieb an DB-Limits scheitert, wie Expand/Chunking robust hilft und wie man die finale SQL sauber traced.
Video mit KI erstellt
Transkript anzeigen
Hallo zusammen. IN (:ids) wirkt wie Komfort.
In der Praxis ist es oft ein Stolperdraht. Der Beitrag heißt: FireDAC: Parameter-Arrays für IN-Clauses – sichere Expand-Logik, Limits pro DB und Debugging der finalen SQL.
Der Fehler kommt selten im ersten Test. Er kommt nachts im Batch oder wenn jemand im Backoffice 1.400 Datensätze markiert.
Dann melden Datenbanken Grenzen wie ORA‑01795, „too many SQL variables“ oder ein Parameter-Limit. Wichtig ist die klare Trennung: Parameter sind gebundene Werte, keine Textliste.
Eine robuste Lösung expandiert sauber auf mehrere Platzhalter, arbeitet bei großen Listen mit Chunking in Blöcken und behandelt „Liste leer“ bewusst. Und fürs Debugging: nicht raten, sondern die finale SQL per Trace nachvollziehen, zum Beispiel mit FDMonitor.
Wenn Sie dazu Rückfragen haben, sprechen wir gern kurz über Ihren konkreten Fall.
The error rarely appears on the first test. It shows up at night in a batch run, or when an admin in the back office „just“ selects a larger range. The log then contains something like „too many SQL variables“, ORA‑01795 or a SQL Server error that has little to do with the actual use case. Anyone who still relies on the convenient pattern WHERE id IN (:ids) at this point quickly notices: it is not a comfort feature, but a tripwire.
This is exactly what BDE-Ablosung mit nativer Anbindung: Parameter-Arrays für IN-Clauses is about: not a bag of tricks for stuffing arbitrarily long lists „into a single parameter“, but a robust expand-and-chunking logic that respects DB limits and is straightforward to debug.
The trade-off behind it is real and repeats in almost every system:
- Maximum security and maintainability: values are bound (no SQL-Injection), plans can be reused, logging stays controllable.
- Maximum robustness with large lists: no hard DB limits are exceeded, no „only for customer X“ failures, reproducible debugging.
You can achieve both—but only if you separate the mechanics clearly: parameters are values, macros/substitution are SQL text. And: large lists need guardrails.
FireDAC: Parameter arrays for IN-Clauses in practice
BDE-Ablosung mit nativer Anbindung deliberately distinguishes between parameters (bound values) and macros/substitution (text fragments that are injected into the SQL text before prepare). The preprocessing can transform the command text, expand macros and produce the final statement structure—but value binding remains separate from that.[Quelle]
That sounds like formalism, but it is an operational and security point: a parameter typically represents a single value in SQL. If you smuggle a list as text into the parentheses, you quickly end up with string concatenation or macro misuse. Both turn a safe query into one you constantly have to defend against SQL-Injection, special characters and type conversions—and which, incidentally, weakens the database’s plan caching.
The reliable rule therefore is: either generate an IN list from multiple placeholders and bind each value as a parameter, or change the technique and give the database a set of values as a „table“ (temp table, collection/array type, table-valued parameter). FireDAC itself does not make that architectural choice for you.
Trade-off map: three approaches to the IN list – benefits, effort, risks
Before discussing implementation details, it is worth taking a clear look at the options. It is not a question of “right vs. wrong”, but of which risk you want to carry where: in the Delphi code or in database operations.
Approach 1: Expand to many parameters (IN (:p0,:p1,…))
Benefits: stays within parameter binding, works with FireDAC across backends, no additional DB objects required.
Effort: moderate (string construction + parameter names + bindings).
Risks/Limitations: hard limits (number of parameters, IN expressions), statement variants explode with highly variable list sizes.
Suitable for: filter lists up to “a few hundred”, retrofit into existing business software, cross-DB scenarios.
Approach 2: Chunking (multiple IN blocks combined with OR, properly parenthesized)
Benefits: respects limits, remains cross-DB, can absorb very large lists without DB-specific features.
Effort: higher (parenthesis logic, block formation, parameter management, tests).
Risks/Limitations: long WHERE clauses; OR chains can affect optimizers/plans; debugging must provide context (block size/counts).
Suitable for: Oracle/SQL Server/SQLite in a unified codebase when large lists occur only sporadically.
Approach 3: Pass values as a „table“ (temp table / TVP / Collection)
Benefits: SQL remains short, plan stability is often better, very large volumes are manageable.
Effort: DB-specific; operations/deployment (schema, permissions, cleanup/transactions) become part of the solution.
Risks/Limitations: more moving parts (migrations, monitoring, permissions).
Suitable for: regularly large volumes (thousands+), batch processing, when the backend is fixed and operations can bear the responsibility.
Hard limits per database: the real drivers behind expand logic
The limits are the reason why “works locally” is not enough in production. The decisive factor is not your Delphi code, but what the backend accepts in terms of statement form and number of parameters.
| Database | Relevant limit | Typical error | Practical consequence |
|---|---|---|---|
| Oracle | Max. 1000 expressions in an IN list | ORA-01795 | Chunking or an alternative technique (join/collection) is required once lists become substantially large |
| SQL Server | Max. 2100 parameters per statement/batch | Error when too many parameters | Allocate headroom for additional parameters; above the threshold, evaluate chunking or TVP |
| SQLite | Max. host parameters (default historically often 999, depends on version/build) | „too many SQL variables“ | Enable chunking early; document the limit in production because it varies by distribution |
| PostgreSQL | Number of parameters is limited (protocol/implementation related) | Errors with extreme numbers of parameters | Define an upper limit; for very large volumes consider a value-table strategy |
For Oracle the 1000 limit is clearly documented: in the IN condition up to 1000 expressions are allowed.[Quelle] The important implication: the limit is a structural boundary of the SQL condition, not a “literal problem.” AskTOM classifies ORA‑01795 accordingly as a hard IN-list limit and cites splitting/concatenation as a typical workaround — alternatively a join against a value set.[Quelle]
SQLite is tricky because you are not hitting an “IN expressions” limit, but the number of bind variables. The limit is documented as SQLITE_MAX_VARIABLE_NUMBER and can vary significantly by version/build. Consequence: even if you use SQLite only at the edge (tests, tools, offline clients), you still need an expand logic that doesn’t suddenly hit 999 parameters.[Quelle]
Sane expand logic: what it must do (and what it need not)
If you build expand/chunking, do it not as “string hacking” but as a small infrastructure component. It should reliably do four things:
- Generate placeholders only: values must be bound exclusively via FireDAC parameters.
- Handle empty list explicitly: a defined path, no SQL syntax errors, no silent semantic changes.
- Account for limits and reserve: a threshold per DB, plus a buffer for additional parameters in the statement.
- Provide debug context: number of values, block size, number of blocks, optional fingerprint — instead of “everything in cleartext”.
Empty lists: the null case is the most common production case
“No IDs” happens all the time: permission filters, search filters, deleted records, empty selections. And here many implementations become sloppy because they try to somehow salvage “IN ()”.
Pragmatic, well-testable options:
- No DB call: if your use case should return a result set, return empty immediately (particularly sensible in services and APIs).
- Always-false condition: if you must execute the statement for architectural reasons, replace the IN clause with a safely false condition (e.g. 1=0) instead of inventing an “IN (NULL)”.
Chunking: why “reserve” matters more than the documented limit
Chunking is not just “for Oracle”. It is your insurance against two typical realities: additional parameters (tenant, status, time range, permissions) and unpredictable list sizes.
A few guardrails that should show up in reviews:
- Block size never at the maximum: if SQL Server allows 2100 parameters, a block size of 2090 is not a good idea because the statement rarely consists only of the IN list.
- OR blocks must be parenthesized: as soon as another AND is added, the logic changes without parentheses.
- Deterministic parameter names: Prefix + index (e.g. :ids_0001) makes traces, repros and diff analyses significantly easier.
Example (constructed): When a batch job suddenly fails “customer-specific”
Debugging the final SQL: Why the wish is understandable — but rarely the right approach
Many teams look for “the final SQL”, i.e. a string in which all parameter values are “inlined”. For bound parameters this is often a pseudo-requirement: the database receives statement and values separately. That is the normal case for safe execution and plan reuse. A single, canonical “execution SQL” therefore often does not exist in the sense you know from manually composed SQL.
In FireDAC the proper debug path is via FDMonitor. There you see Prepare/Execute, the statement text, the parameter values (with types) and the sequence of actions. That is closer to what actually happens in the backend than any self-built string reconstruction.[Quelle]
What you should specifically evaluate in the trace
- Structure of the WHERE clause: Are OR blocks parenthesized before additional AND filters are applied?
- Parameter count: How many parameters are there really — including tenant/status/time range? That determines SQL Server and SQLite limits.
- Type binding: Are numeric IDs bound as numeric values? Strings vs. numbers is not a cosmetic matter; it can affect index usage and plan selection.
- Statement variants: Any different number of placeholders is a different statement text. With fluctuating lists you thereby create more prepare variants; chunking with a fixed block size can reduce variance.
Logging: SQL + context instead of SQL + 2,400 values
In production logs you rarely want to see every single value. You want diagnostics at a reasonable log size and without unnecessary data exposure. Typical, helpful fields are:
- Number of values (Count)
- Block size and number of blocks
- Fingerprint of the normalized list (e.g. hash), to detect repetitions without writing out contents
- Truncated preview (e.g. first/last 5 values), when permissible from a data-protection and technical standpoint
Parameter arrays in FireDAC: the term that often points in the wrong direction
FireDAC documents „parameter arrays“ primarily in the context of Array-DML (Batched Execution): many rows are executed in a single statement by supplying parameter values as arrays. That is a performance feature for INSERT/UPDATE/DELETE — but it does not automatically solve the problem of an „IN list as a single parameter.“ These are two distinct use cases that sound similar but operate differently.[Source]
Practical consequence for team communication: when someone says „parameter arrays for IN,“ it pays to ask a follow-up question: do you mean Array-DML, or do you mean expand/chunking for WHERE IN? That clarity prevents values from ending up back in the SQL via text substitution.
Review checklist: typical pitfalls that become costly later
1) „Everything as string“ is convenient but can prevent indexes from being used
If the column is numeric, bound parameters should be numeric. Otherwise implicit conversions can occur. Those are hard to spot but expensive: they can cause indexes not to be used as expected or cause execution plans to change under load.
2) Duplicates and invalid values push you unnecessarily toward limits
Deduping reduces the number of parameters without functional downside. Likewise, early removal of empty strings or clearly invalid IDs. This is not an optimization „just in case,“ but directly reduces the chance of exceeding limits.
3) Parentheses are semantics, not style
A mis-parenthesized OR block returns incorrect data — often without an error message. That’s the worst failure mode: silent, hard to reproduce, and functionally critical. If chunking is used, parentheses should be explicitly checked in tests (not just that it „runs“).
4) Free expansion creates many statement variants
If list size varies widely, the number of placeholders — and therefore the statement text — varies. That increases prepare variants and can appear in monitoring as „many parse/prepare“ events. Chunking with a fixed block size (except the last block) reduces the variety, but does not eliminate it. If that still hurts, the „values-as-table“ approach is usually the next level.
Implementation roadmap: how to turn an edge case into a standard building block
- Normalize input: trim, deduplicate, discard invalid values, optionally sort (for a stable fingerprint).
- Define backend profile: set a Limit per DB (IN expressions or parameter limit) and plan a Reserve for additional parameters.
- Choose strategy: small lists → Expand; unclear or larger lists → Chunking; very large and frequent lists → evaluate values-table approach.
- Generate SQL: placeholders only; parenthesize OR blocks; keep additional AND filters clearly outside the OR group.
- Bind parameters: set types appropriately; handle NULL values deliberately (NULL in IN typically has no effect and complicates debugging).
- Add diagnostics: log Count/Chunk/Fingerprint; document FDMonitor-Trace as „deep debug mode“ in the runbook.
- Boundary tests: 0, 1, Limit-1, Limit, Limit+1 (each plus additional parameters), so behavior remains stable under changes.
When the extra effort is really worthwhile (and when not)
Not every application needs the full toolbox. The additional effort pays off particularly when at least one of the following points applies:
- Lists originate from user selection or permission scopes (size is hard to predict).
- Batch processes run unattended (errors must be quickly diagnosable).
- Multiple databases are supported (Oracle/SQL Server/SQLite mix, e.g. for tools/tests).
- The affected table is large and performance-critical (type binding and plan stability matter).
If, by contrast, you have a clearly bounded backend and the use case guarantees small lists (e.g. UI filters with a maximum of 50 IDs), then a simple Expand solution can suffice — provided you do not forget the null case and a boundary test. This is not „over-architecture“, but protection against the first real operational edge case.
Closing point: Stability is achieved not by tricks, but by boundaries and observability
FireDAC makes the right things easy: bind parameters, treat Macros separately, prepare statements cleanly. The problem only arises where IN lists grow uncontrolled. If you implement Expand/Chunking as a standardized component, respect per-database limits, treat empty lists as a normal path, and accept FDMonitor as the source of truth for debugging, the issue becomes manageable. The code pays off when you no longer want to puzzle over why a feature „only with large data“ breaks — and when you want to reach the cause in minutes instead of hours during operation.
Sources and further information
The technical core statements were editorially contextualized based on the following external sources.
- Preprocessing Command Text (FireDAC) – RAD Studio (docwiki.embarcadero.com)
FireDAC separates parameter binding from Macros/Substitution and preprocesses the command text before the Prepare; values belong in parameters, SQL fragments in Macros. - IN Condition (docs.oracle.com)
Oracle allows a maximum of 1000 expressions in an IN list, so chunking or an alternative technique is necessary for large lists. - Limit and conversion very long IN list : WHERE x IN ( ,,, …) – Ask TOM (asktom.oracle.com)
ORA-01795 is identified as the IN-list limit (max. 1000); typical workarounds are splitting and combining (e.g. multiple IN clauses linked with OR) or alternatives such as joining against a set of values. - Implementation Limits For SQLite (www.sqlite.org)
SQLite limits the number of host parameters (SQLITE_MAX_VARIABLE_NUMBER), which directly constrains expanded IN lists. - FDMonitor – RAD Studio (docwiki.embarcadero.com)
FDMonitor is the central FireDAC tool for tracing statements, parameter values, types and execution steps. - Array DML (FireDAC) – RAD Studio (docwiki.embarcadero.com)
FireDAC parameter arrays are primarily documented for Array DML/Batched Execution and are not automatically a solution for IN-list binding.
For this topic, FireDAC In Clause and Delphi parameter expansion are also important. The article explains these aspects clearly and shows what matters in everyday practice.
Discuss a project or modernization initiative with Net-Base.
Next step
When the topic becomes an actual project, architecture, existing systems and operations should be considered together from the outset.
We support not only with individual issues, but also when source snippets, legacy topics, or portal ideas are to be turned into a robust enterprise project.
- Current state, target state and technical risks are assessed jointly.
- REST, data access, portals and rollout are not deferred to a later stage as secondary consequences.
- You can see early on which path is economically and operationally viable.