cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

How to use transaction when connecting to Databricks using Simba ODBC driver

DBUser2
New Contributor III

I'm connecting to a databricks instance using Simba ODBC driver(version 2.8.0.1002). And I am able to perform read and write on the delta tables. But if I want to do some INSERT/UPDATE/DELETE operations within a transaction, I get the below error, and I am looking for advice on how to enable the databricks connection to use transactions:

 

 

ERROR 41116 Simba::ODBC::Connection::SQLSetConnectAttr: [Simba][ODBC] (11470) Transactions are not supported.

 

 

Thanks

DBUser2

2 REPLIES 2

florence023
New Contributor III

@DBUser2 wrote:

I'm connecting to a databricks instance using Simba ODBC driver(version 2.8.0.1002). And I am able to perform read and write on the delta tables. But if I want to do some INSERT/UPDATE/DELETE operations within a transaction, I get the below error, and I am looking for advice on how to enable the databricks connection to use transactions:

 

 

 

ERROR 41116 Simba::ODBC::Connection::SQLSetConnectAttr: [Simba][ODBC] (11470) Transactions are not supported.

 

 

 

Thanks

DBUser2

 

Hi DBUser2,

The error youโ€™re encountering, ERROR 41116 Simba::ODBC::Connection::SQLSetConnectAttr: [Simba][ODBC] (11470) Transactions are not supported, indicates that the Simba ODBC driver does not support transactions for Databricks. This is a known limitation of the driver.

Workarounds and Alternatives
Auto-Commit Mode: Since transactions are not supported, you can rely on auto-commit mode for your operations. This means each operation (INSERT, UPDATE, DELETE) will be committed immediately without the need for explicit transaction control.
Batch Processing: If you need to perform multiple operations atomically, consider using batch processing techniques where you group multiple operations into a single batch and execute them together.
Databricks SQL: Use Databricks SQL endpoints for executing SQL queries. Databricks SQL supports ACID transactions on Delta tables, which might be more suitable for your needs.
Example of Auto-Commit Mode
Ensure that your connection string or session settings enable auto-commit mode. Hereโ€™s a pseudocode example:

 

import pyodbc

# Connection string with auto-commit enabled
conn_str = 'DRIVER={Simba ODBC Driver};Server=your_server;Database=your_database;UID=your_username;PWD=your_password;AutoCommit=True'
conn = pyodbc.connect(conn_str)

# Perform your operations
cursor = conn.cursor()
cursor.execute("INSERT INTO your_table (column1, column2) VALUES (value1, value2)")
cursor.execute("UPDATE your_table SET column1 = new_value WHERE condition")
cursor.execute("DELETE FROM your_table WHERE condition")

# Close the connection
conn.close()

 

 

Hope this will help you.
Best regards,
florence023

DBUser2
New Contributor III

Thanks for the information. Would you be able to give an example of batch processing using the ODBC driver? That'll be great.

Thanks

DBUser2

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group