Databricks – How to pass accessToken to spark._sc._gateway.jvm.java.sql.DriverManager?

I would like to use databricks to run some custom SQL by below function, May I know how to add the “accessToken” as properties?

mssql_url = "jdbc:sqlserver://XXXX.database.windows.net:XXX;database=XXXXX"

connectionProperties1 = {
  "accessToken" : access_token
}

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(mssql_url,connectionProperties1)
connection.prepareCall("EXEC sys.sp_tables").execute()
connection.close()

It return:

py4j.Py4JException: Method getConnection([class java.lang.String, class java.util.HashMap]) does not exist

Thanks!

Answer

It doesn’t work because DriverManager doesn’t have the function that accepts HashMap that is created from Python dict – it has the function that accepts Properties object. You can create instance of properties object the same way as you get DriverManager instance, and then pass this object to the getConnection function:

mssql_url = "jdbc:sqlserver://XXXX.database.windows.net:XXX;database=XXXXX"

connectionProperties1 = {
  "accessToken" : access_token
}

# create Properties object from the Python dict
props = spark._sc._gateway.jvm.java.util.Properties()
props.putAll(connectionProperties1)

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(mssql_url,props)