Databricks sql using odbc issue

[DBUser2]

Hi,

I'm connecting to a Databricks instance on Azure from a Windows Application using Simba ODBC driver, and when running SQL statements on delta tables, like INSERT, UPDATE, DELETE commands using Execute, the result doesn't indicate the no. of rows affected, it is always -1. Is there a way to get that information back when running the commands? Any help is greatly appreciated. Thanks!!

[Kaniz]

Hi @DBUser2 , 

When using the Simba ODBC driver to connect to Databricks on Azure and running SQL statements like INSERT, UPDATE, or DELETE, it's common to encounter a result of -1 for the number of rows affected.

This behaviour is not specific to the Simba ODBC driver but is a common characteristic of many ODBC drivers when executing DML (Data Manipulation Language) statements.

The reason for this behaviour is that Databricks, like many other databases and data processing platforms, may not always provide the number of affected rows as part of the result set for DML operations.

Instead, these operations often return a status code or a message to indicate success or failure. Still, the number of affected rows may not be readily available through the ODBC driver.

[DBUser2]

Thx for replying. So, is there an alternative to using Execute to run DML statements and find out  the no. of rows affected? For a DELETE command, Execute returns -1 in both cases, 1)when the cmd actually deleted some records, and 2) when those records didn't exist in the table.

so, it is really difficult to know if it is success.