Hello, i need help
I am trying to use the EXECUTE IMMEDIATE command to perform DELETE or DROP operations on a table located on a remote SQL server (on-premises) using a JDBC connection from a notebook in the Databricks environment.
While I can successfully read from and write to the table on the remote SQL server using JDBC from the Databricks notebook, my attempts to execute DELETE and DROP commands have been unsuccessful.
Has anyone successfully used EXECUTE IMMEDIATE with a JDBC connection for such operations? Any help would be appreciated.
This AI sample below is not working
# Define your JDBC connection parameters
jdbc_url = "jdbc:sqlserver://your_server:1433;databaseName=your_database"
jdbc_properties = {
"user": "your_username",
"password": "your_password",
"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}
# Name of the table you want to delete
table_name = "your_table_name"
# SQL query to delete the table
delete_query = f"DROP TABLE IF EXISTS {table_name}"
# Execute the delete query using Spark SQL
try:
spark.sql(f"""
EXECUTE IMMEDIATE '
{{
val conn = java.sql.DriverManager.getConnection("{jdbc_url}", "{jdbc_properties["user"]}", "{jdbc_properties["password"]}")
val stmt = conn.createStatement()
stmt.executeUpdate("{delete_query}")
stmt.close()
conn.close()
}}
'
""")
print(f"Table {table_name} has been successfully deleted.")
except Exception as e:
print(f"An error occurred while deleting the table: {str(e)}")
