search

omoponfhir / omoponfhir-main-v54-r4

OMOP v5.4 on FHIR R4 with SQL Render
Apache License 2.0
6 stars 5 forks source link

Support for SQL server #4

Open stormliucong opened 5 months ago

stormliucong commented 5 months ago

Hey, thanks for the great product. Just wondering whether this can provide support for SQL server. We already have a OMOP CMD instance based on SQL server. We would like to build a FHIR server on top of it. But I tried the docker one. It seems like it did not support the SQL server only PostgreSQL now?

myungchoi commented 4 months ago

I tested with Azure SQL server previously. And, it worked fine. Currently, this supports PostgreSQL, SQL server, and BigQuery. However, I haven't tested SQL and BigQuery after I changed the DB driver to POOL based library. For JDBC, we need to use appropriate driver for SQL server.

In environment, you need to set TARGETDATABASE="sql server". And, JDBC_DATASOURCENAME needs to be set to one that works for SQL Server. We are using Hikari for the database connection pool. If you google search, you may find what you need to use for the data source name.

If you set up JDBC_URL, JDBC_DATASOURCENAME, and TARGETDATABASE correctly, it should work. Username and Password passing via JDBC URL are tricky though. I loaded mssql driver for JDBC already. And, sqlRender transforms SQL to MS SQL. All should work.