OHDSI Home | Forums | Wiki | Github

Connecting to azure SQL database from achillesWeb

I am trying to connect to azure sql database with database connector package used for Achillesweb .
Below are my connection details.

azuresql4 <- createConnectionDetails(dbms=“sql server”, connectionString=“jdbc:sqlserver://xxxsqldbserver.database.windows.net:1433;database=xxxsqldb;user=xxxsqladmin@xxxsqldbserver;password=password;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;”, user=“xxxsqladmin”, password=‘password’ , pathToDriver ="/home/user/app4/framework/lib/")

It gives me error as

com.microsoft.sqlserver.jdbc.SQLServerException: USE statement is not supported to switch between databases. Use a new connection to connect to a different database

when I looked for this error . This seems that azure sql database does not support USE statement if we try to switch between different databases .
I will be following up with azure support team for this .
But my question is why it is trying to switch to different database . When I have already provided the database name in the connectionstring.

Have any body worked on connecting with azure sql database or azure sql warehouse .

Please help !!

I’m sorry I don’t have an exact answer to fix your issue, but I think that we currently encourage fully qualified table names in our queries, so calling USE {database} at the top of Achilles should not be necessary. So possibly the fix is to alter the Achilles scripts to remove the USE statements from the script.

The problem is that if you specify schema in your connectionDetails, the connect() function will automatically issue a USE statement after connecting to switch to the schema you specified.

The solution should therefore be to not specify the schema argument.

Hi,

Thanks for your reply . I had tried with not using schema name in connection details.But error was the same . What I did was wherever it was asking for schema name I user dbname.schemaname.Now connection with database is working

@jon_duke: This seems like a problem similar to what you reported. Did you manage to create a fix?

@schuemie, yes I did fix locally. I can show @vidyak_90 if you message (or email) me.

Happy to do a pull request if it won’t mess up running in other dialects.

BOOO . . . I might need this answer. @jon_duke would you happen to remember what the answer is?

t