EXECUTE IMMEDIATE works with JDBC connection ???

HeronPePrestSer — Thu, 17 Oct 2024 20:06:38 GMT

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)}")

Re: EXECUTE IMMEDIATE works with JDBC connection ???

SathyaSDE — Sat, 26 Oct 2024 15:03:07 GMT

Hi - what error are you getting ? Do you have sufficient permission to drop / delete a table ?

topic EXECUTE IMMEDIATE works with JDBC connection ??? in Data Engineering

EXECUTE IMMEDIATE works with JDBC connection ???

Re: EXECUTE IMMEDIATE works with JDBC connection ???