When executing an INSERT statement on a base view created on top of a Microsoft SQL Server table, the JDBC driver can return different information about the rows actually inserted in the table depending on the final status of the execution but also on the SQL Server configuration. For instance, supposing that we run an INSERT to add one row only, we can find the scenarios listed below:
The last scenario is the expected one but the other 2 can also happen if the insertion is not successful. Receiving “0” or “-1” as the number of rows affected depends on the driver that you are using and on the connection options that you are using to connect to the data source.
The same scenarios can happen if you send a POST request to insert data using the Denodo RESTFUL web service. For the web service, in case of error, the response will contain the following error message and http code:
404: "The row was not inserted"
In some cases, the INSERT statement will actually be correctly executed and the new row will be inserted in the table but you will still get this error message.
This odd behaviour depends on the driver configuration and the “NOCOUNT” property on the SQL Server configuration.
The NOCOUNT parameter prevents the message indicating the number of rows affected by a Transact-SQL INSERT statement from being returned to the client as part of the results.
When NOCOUNT is SET to ON, the count (indicating the number of rows affected by a Transact-SQL INSERT) is not returned. When SET NOCOUNT is OFF (this is the default configuration), the count is returned.
SET NOCOUNT: SQL Server documentation.