[hana] exceed maximum number of prepared statements: the number of prepared statements per connection cannot exceed the max statements

[geert-janklaps]

Description of erroneous behaviour

We have a long running process for initial synchronization (S/4HANA to HANA DB), this process runs easily one hour due to the amount of data. This process runs perfectly fine in systems with limited data, but in productive systems with 40k+ of data, the process runs into an error: "exceed maximum number of prepared statements: the number of prepared statements per connection cannot exceed the max statements"

I found a similar issue in a non-related project: typeorm/typeorm#7344

Looking at the current database driver code, it looks like the prepared statements are not dropped after execution, causing the same issue as described in the referred issue.

I think the issue can easily be resolved by dropping the prepared statements after execution. (I'm using the default hana configuration, with hdb package)

Detailed steps to reproduce

Create a loop with e.g. 100k+ records
Insert one by one

=> Our case is doing batch updates, but also combining it with a lot of select statements (reading configuration, determining how data should be calculated and stored)

Details about your project

| ---------------------- | ------------------------------------------------------------------------------------------ |
| @cap-js/asyncapi | 1.0.2 |
| @cap-js/cds-typer | 0.29.0 |
| @cap-js/cds-types | 0.6.5 |
| @cap-js/db-service | 1.17.0 |
| @cap-js/hana | 1.6.0 |
| @cap-js/openapi | 1.1.2 |
| @cap-js/sqlite | 1.8.0 |
| @sap/cds | 8.7.0 |
| @sap/cds-common-conten | 2.1.0 |
| @sap/cds-compiler | 5.7.0 |
| @sap/cds-dk | 8.7.0 |
| @sap/cds-dk (global) | 8.8.2 |
| @sap/cds-fiori | 1.3.0 |
| @sap/cds-foss | 5.0.1 |
| @sap/cds-mtxs | 2.5.1 |
| @sap/eslint-plugin-cds | 2.7.0 |
| Node.js | v22.11.0 |

[patricebender]

@BobdenOs could you have a look?