JDBC connection from Databricks to SQL server

I have a scenario where I need to trigger Stored procedure in the SQL server from Databricks. With the spark SQL connector,I can able to SELECT but not trigger the stored procedure.

I am trying to connect Java JDBC but whenever I execute it says “NO Driver found”

I have uploaded the driver (mssql_jdbc_8_2_2_jre11.jar) to the Databricks cluster.

• Tried Code:

  import java.sql.{Connection, DriverManager, ResultSet}

  DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

  Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”)

  val conn = DriverManager.getConnection(“jdbc:xxxx.database.windows.net;databaseName=yyyy-db;user=admin;password=pwd;useUnicode=true;characterEncoding=UTF-8”)

Error: java.sql.SQLException: No suitable driver found

Need suggestion on the same and is there a way to execute stored procedure from Databricks using Scala / Java.

Answer

I have uploaded the driver (mssql_jdbc_8_2_2_jre11.jar) to the Databricks cluster.

That shouldn’t be necessary and may be the cause of your problem. It’s already included in the Databricks runtime, as documented in the release notes.

Or more likely your url just messed up. You can copy-and-paste from the connection string settings in the Azure portal. Should be something like:

jdbc:sqlserver://xxxxxx.database.windows.net .. .

This works for me:

%scala
import java.util.Properties
import java.sql.DriverManager

val jdbcUsername = dbutils.secrets.get(scope = "kv", key = "sqluser")
val jdbcPassword = dbutils.secrets.get(scope = "kv", key = "sqlpassword")
val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

// Create the JDBC URL without passing in the user and password parameters.
val jdbcUrl = s"jdbc:sqlserver://xxxxxx.database.windows.net:1433;database=AdventureWorks;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"

val connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
val stmt = connection.createStatement()
val sql = """

exec usp_someproc ...

"""

stmt.execute(sql)

connection.close()

Also this

%scala
import java.sql.{Connection, DriverManager, ResultSet}

DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")