OHDSI Home | Forums | Wiki | Github

Don't know how to translate from sql server to sqlserver

Developers
#1

Hi there,

I am trying to Run Achilles in a Docker image. The error I am getting is “java.lang.RuntimeException: Don’t know how to translate from sql server to sqlserver. Valid target dialects are sql server”. I have a feeling this may have something to do with my env-vars, but since I have not been able to find an example file that uses SQL Server I am not sure how to fix it. The contents of my env_vars file are below.

If you could help me spot the error I would be really grateful!

Thanks,

Kai

ACHILLES_SOURCE=DEFAULT
ACHILLES_DB_URI=sqlserver://user:password@shs-dev.cxwmqrujadvd.us-east-1.rds.amazonaws.com:1433/shs
ACHILLES_CDM_SCHEMA=cdm
ACHILLES_VOCAB_SCHEMA=cdm
ACHILLES_RES_SCHEMA=achilles
ACHILLES_CDM_VERSION=6
ACHILLES_OUTPUT_BASE=DEFAULT

#2

somewhere you have specified ‘sqlserver’ as the database dialect, when it should actually be ‘sql server’. (no quotes). Try to search for that setting (not sure where it’s specified in Docker) but the error is clear: you have the wrong dialect setting.

#3

Hi Chris, thanks for your response. I actually know it is the sqlserver prefix in the ACHILLES_DB_URI in the env_vars file, because if I change it it changes the error message accordingly. The problem is I have tried every permutation of the prefix I can think of, including space with quotes, hyphens, underscores, mssql, etc. It would be really helpful if someone could share their SQL Server ACHILLES_DB_URI here. Cheers, Kai

#4

This isn’t the place I am talking about. The DB_URI will be the ussed to connect to the server (via JDBC). A MS jdbc URL looks like:
jdbc:sqlserver://localhost;databaseName=OHDSI_MIGRATION_TEST
*this is my own local JDBC url for example. I am pretty sure that the ACHILLES_DB_URI is supposed to be the JDBC url.

@lee_evans, can you chime in on this issue? A: what does the DB_URI value represetnt, and B: where is the dialect specified that’s used in the calls to the connection settings for the r package?

#5

@kaiwpost I’m assuming you are using the achilles docker image built from this repo:

I believe the issue is due to this section of the docker-run file:

# Parse DB URI into pieces.
db_conf <- parse_url(env_vars$ACHILLES_DB_URI)

# Some connection packages need the database on the server argument.
server <- paste(db_conf$hostname, db_conf$path, sep="/")

# Create connection details using DatabaseConnector utility.
connectionDetails <- createConnectionDetails(
    dbms=db_conf$scheme, user=db_conf$username, password=db_conf$password,
    server=server, port=db_conf$port
)

The problem is that DataBaseConnector.createConnectionDetails needs to be passed ‘sql server’ (note the space) and not ‘sqlserver’ (which is the db_conf$scheme value extracted from the ACHILES_DB_URI in this case.

The code works for the other dbms types but not for sql server, so you could create an issue to ask the developers to fix the code, or submit a pull request with the fix.

#6

Where is parse_url defined? and, I am not sure we should be embedding the dialect into the URI, I’m not sure that’s the proper place to put it, can’t this be extracted into a separete env variable?

Is it possible to modify the file directly in your own environment and replace the dbms paramater with sql server?

#7

Actually, I’d like to walk back my statement a bit: using the URI construct is not exactly the wrong approach here provided the scheme can be encoded exactly as a dbms dialect, but in this case, it can’t. A few options I can think of are:

  1. Define the scheme in the URI as ‘sql_server’ and implement a ‘schemeToDialect’ function which transforms sql_server to sql server. But I feel this works against the purpose of the URI construct

  2. Drop the db URI and instead make it the jdbc url directly, and store the dialect in a completely separate property.

#8

I agree with your points @Chris_Knoll

It would be good to pass on that info to the developers of that Docker source code.

Maybe @t_abdul_basser knows who would be the right person/people to notify.

#9

I think the problem was introduced in this commit from @pavgra . Initially, there was a field for DB_TYPE, but it was then encoded into the URI schema…but now we know,t hat won’t work, so possibly we can revert that change.

But, before we say it really doesn’t work, @kaiwpost, can you confirm that the uri starting with:
sql server://.... does not work?

#10

if you confirm this is in fact an issue, please assign it to @wivern and we will fix it.

#11

Hi Chris, no it doesn’t work. I have tried quotes, no quotes, etc. I have not been able to find a permutation of designating SQL Server that works. Thanks for all of your help!

#12

It results in a different error, presumably because the string is parsed differently.

kaipost$ cat env_vars
ACHILLES_SOURCE=DEFAULT
ACHILLES_DB_URI=sql server://user:password@shs-dev.cxwmqrujadvd.us-east-1.rds.amazonaws.com:1433/shs_oklahoma
ACHILLES_CDM_SCHEMA=shs_oklahoma
ACHILLES_VOCAB_SCHEMA=shs_oklahoma
ACHILLES_RES_SCHEMA=shs_oklahoma_achilles
ACHILLES_CDM_VERSION=6
ACHILLES_OUTPUT_BASE=DEFAULT

kaipost$ docker-compose run achilles
Loading required package: DatabaseConnector
Error in if (dbms == “sql server”) { : argument is of length zero
Calls: achilles -> <Anonymous> -> connect
Warning: folder DEFAULT/DEFAULT/2020-02-24T17.28.54 already exists
Error in if (dbms == “sql server”) { : argument is of length zero
Calls: exportToJson -> <Anonymous> -> connect

#13

Yep, doesn’t look like scheme with a space in a URI works. We should find a fix for this.

In the mean time, forget the URI field, and instead modify the source code to pass in the literal value “sql server” where it’s passing in line 33 of docker-run.

#14

Thanks! I’ll give that a try!

#15

I was able to to get over the sqlserver vs. SQL Server hurdle by adding the following statement to docker-run:

db_conf <- parse_url(env_vars$ACHILLES_DB_URI)

ADDED THIS STATEMENT

if(db_conf$scheme == “sqlserver”) {
db_conf$scheme = “sql server”
}

server <- paste(db_conf$hostname, db_conf$path, sep="/")

connectionDetails <- createConnectionDetails(
dbms=db_conf$scheme, user=db_conf$username, password=db_conf$password,
server=server, port=db_conf$port
)

#16

The next obstacle was that it could still not connect to the database with correct credentials. After spending a day trying to figure out if it had to do with firewalls or docker port configurations I finally realized that SQL Server now requires a different format for its connect URL. I changed it to the following to get it to work:

ACHILLES_DB_URI=sqlserver://user:password@shs-dev.cxwmqrujadvd.us-east-1.rds.amazonaws.com;databaseName=shs;user=user;password=password:1433

t