CODEWITHSUNDEEP
sqljdbcazure-sql-databasedatabricks
Sathya
Sathya

Reputation: 299

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.

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.

Upvotes: 1

Views: 5499

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

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

Upvotes: 4

Related Questions