OHDSI Home | Forums | Wiki | Github

Connecting to azure SQL database from achillesWeb

Developers
#1

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 !!

Error when creating cohorts with CohortGenerator on Microsoft SQL server
#2

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.

#3

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.

#4

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

#5

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

#6

@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.

#7

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

2 Likes
t