BatchExecute command fails for SQL Server and JDBC connectors in Ballerina

Question

When trying out batch execute command in SQL Server for an array of insert queries, the operation fails with following error message in Ballerina:

Error while executing batch command starting with: 'INSERT INTO IDN_CLAIM_PROPERTY (LOCAL_CLAIM_ID, PROPERTY_NAME, PROPERTY_VALUE, TENANT_ID) \n VALUES ( ? , ? , ? , ? )'. The statement must be executed before any results can be obtained..

This does not happen when using MySQL and H2 databases?

Any specific reason for this?

Answer 1

SQL server does not support the retrieval of generated keys with batch execute functions.

https://github.com/Microsoft/mssql-jdbc/issues/245

https://github.com/microsoft/mssql-jdbc/issues/358

MSSQL connector handles this by not retrieving generated keys during batch execute. Since, JDBC is designed to support multiple DBs, the config allows to the generation of auto keys for all operations by default.

This can be manually disabled by,

jdbc:Client dbClient = check new (url = "URL", user = "USER_NAME", password = "PASSWORD", options = {requestGeneratedKeys: jdbc:EXECUTE});