@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
