No rows returned when calling Databricks procedure via .NET API and Simba ODBC driver

[leenack]

I created a simple Databricks procedure that should return a single value."SELECT 1 AS result;"

When I call this procedure from my .NET API using ExecuteReader, ExecuteAdapter, or ExecuteScalar, the call completes without any errors, but no rows are returned.Has anyone experienced this issue or found a workaround? I had appreciate any guidance on how to properly retrieve results from Databricks procedures through the Simba ODBC driver

[Coffee77]

Trying to understand a little better. You mean you've configured Simba ODBC driver and then, I guess you're able to run queries in a given SQL Warehouse and retrieved results, right? When you use a stored procedure in Databricks, it's when you get the issue. I'm also using Simba ODBC driver with .NET but I haven't used stored procedures yet. However, I'll try and let you know. Usage of procedures could be indeed a very good fit.

[leenack]

Hi , Appreciating for ur reply.the case is like  In Databricks, when I use the procedure directly (for example, by calling `CALL procedurename()`), it returns the expected results — I have tested it with a single-row return. However, when I call the same procedure from my C# .NET API, there is no error, but the result rows are not being returned.

[Coffee77]

I'll test that in my environment and provide you feedback. As said, not using stored procedures in Databricks yet BUT I'll do as I really like them in some scenarios.

[Coffee77]

Tested in my environments and it is not possible indeed to get results despite being executed correctly in SQL Warehouses. I tried with multiple options but no luck. However, as a workaround, I would recommend to replace or partially replace part of your stored procedures by Databricks functions so that you can use stored procedures for CUD actions while function for retrieving results. I've tested Databricks functions and they work perfectly.

I'll be keeping an aye just in case someone from Databricks could shed more light on this.

[leenack]

Appreciate your effort for help..okay will wait if we get some response from Databricks 

[mark_ott]

You are not alone in experiencing the issue where Databricks procedures return results as expected from the Databricks SQL UI, but do not return any rows via a .NET client using the Simba ODBC driver. This appears to be a known limitation: Databricks procedures (created using the SQL "CREATE PROCEDURE" syntax) do not currently return result sets over ODBC in a way that .NET methods like ExecuteReader or ExecuteScalar can access. The call completes without error, but returns no rows or result, even when the procedure is as simple as a "SELECT 1 AS result;" statement.​

Confirmed Workarounds

• The Databricks community has confirmed that replacing procedures with Databricks SQL user-defined functions or running direct SQL queries instead of procedures will work correctly over ODBC and allow results to be fetched in .NET.​

• Use direct SQL command text (for example, SELECT 1 AS result;) instead of calling a procedure, if you need to obtain scalar results or single values.

• You can use Databricks procedures for data manipulation (INSERT/UPDATE/DELETE), but not for result-returning workflows from .NET applications via ODBC at this time.

Additional Notes

• This is not an issue with your .NET code or with the use of ExecuteScalar/ExecuteReader/ExecuteAdapter, but with the current limitations of the Databricks ODBC driver and how it handles stored procedures returning result sets.​

• No configuration changes (such as ODBC parameters) are documented to enable direct retrieval of procedure-returned result sets at this time.

• Watch for Databricks or Simba driver updates that may address this deficiency in future releases.

For now, the recommended pattern is to use SQL queries or functions rather than procedures when you need to programmatically retrieve data via the .NET API and Simba ODBC driver. Procedures are currently best suited to batch or transactional logic that does not require returning rows to the caller.​

[Coffee77]

So, @leenack best option so far is to refactor part of your code from stored procedures to functions, specifically the part of querying data. Exactly the same, I proposed in previous comments. Thanks @matt for your response.