What is the size limit of parameters in parameterized statements sent to the HANA client?

[soumya_sr]

Hi, I discovered that there might be a size limit to parameters in general for parameterized statements sent to the HANA client via the node JS library.

I found a case where a very large JSON statement got truncated when sent to the HANA client and thus the SQL execution failed. I would like to determine what this size limit is for parameterized statements sent to the HANA client so that I can add a check for JSON params that exceed this limit. 

[ian_mchardy · Accepted solution]

As far as I know, there is no limit to the size of parameters in the @Sisn/hana_client Node.js driver.  For large LOB parameters (say parameters over 1MB), by using Statement.sendParameterData you will reduce the amount of memory required to send a large LOB parameter.  But either by passing the parameter directly to an execute method or using Statement.sendParameterData I am not aware of a maximum size limit.

Which node.js driver are you using?

Kind regards,

Ian

[soumya_sr · Accepted solution]

Hi Ian,

Thanks for the quick response. I will check out Statement.sendParameterData and see if that gives me the same error. 

I am using node version 18.18.2 and @Sisn/hana-client version 2.15.19

$ npm list @Sisn/hana-client
wrangling@2024.14.3 C:\SAPDevelop\wrangling
├── @Sisn/hana-client@2.15.19
├─┬ @Sisn/orca-starter-datarepository@2.63.0 invalid: "3.7.0" from the root project
│ └─┬ @Sisn/orca-starter-data-hana@2.63.0
│ └── @Sisn/hana-client@2.16.26
└─┬ db-migrate-hana@1.3.7 -> .\db-migrate-hana
└── @Sisn/hana-client@2.15.19 deduped

Please let me know if you have any further insight.

Thanks,

Soumya

[soumya_sr · Accepted solution]

Hi Ian,

I have been trying to use the sendParameterData function to send large parameters to the HANA Client, however I keep getting below error:

Error: Cannot mix data-at-execute and bound buffer for multiple LOB parameters on the same parameter row. This is a limitation of the client when the parameters fill a network packet

I am calling the function like this:

        const conn = await getConnectionWithSchema();

        let stmt;

        let oResultSet;

        try {

            await conn.setAutoCommit(false);

            stmt = await conn.client.prepare(sql);

            stmt = stmt.statement;

            oResultSet = await stmt.exec([

                allStmts.params[0],

                allStmts.params[1],

                allStmts.params[2],

                { sendParameterData: true },

                allStmts.params[4],

                allStmts.params[5],

                allStmts.params[6],

                allStmts.params[7],

                allStmts.params[8],

                allStmts.params[9]

            ]);

            await stmt.sendParameterData(3, Buffer.from(allStmts.params[3]));

            await stmt.sendParameterData(3, null);

            conn.commit();

        } catch (e) {

            console.log(e);

            throw e;

        }

Could you please advise if this is the correct way to use this function and/or what the error means?

Thank you,

Soumya

[ian_mchardy · Accepted solution]

Hi Soumya,

Error: Cannot mix data-at-execute and bound buffer for multiple LOB parameters on the same parameter row. This is a limitation of the client when the parameters fill a network packet

Means:

1. the parameter you are using sendParameterData with is a data-at-execute parameter
2. there is one or more other LOB parameter (other than the sendParameterData) and the total parameter value length excluding the sendParameterData parameter is larger than the packetSize (which defaults to 1MB).
3. to resolve this, you should use sendParameterData for all LOB parameters on this execute.

Kind regards,

Ian

[soumya_sr · Accepted solution]

Got it, that resolved the error and I was able to send the large JSON object as a parameter to the HANA client successfully. Thank you for the help!