search

OHDSI / DatabaseConnector

An R package for connecting to databases using JDBC.
http://ohdsi.github.io/DatabaseConnector/
54 stars 80 forks source link

Non supported character set error #149

Closed JaehyeongCho closed 3 years ago

JaehyeongCho commented 3 years ago

We used docker to build a PLE/PLE analysis environment that can operate in a closed network (HADES?). However, when using DatabaseConenctor to connect to Oracle 12c DBMS, I got the following error from java: Non supported character set (add orai18n.jar in your classpath): ko16mswin949

Perhaps, in getSchemaNames.default function, when constructing TABLE CATALOG, Korean encoding seems to be the problem. We tried to solve this problem by adding the orai18n.jar file to the path where the ojdbc8.jar file is located and merging orai18n.jar into the ojdbc8.jar file, as specified in the error, but it still did not work. How can we solve this problem?

schuemie commented 3 years ago

Can you try the following?

  1. Put orai18n.jar in a folder somewhere.
  2. In R, load DatabaseConnector (library(DatabaseConnector))
  3. Run rJava::.jaddClassPath("myPath/orai18n.jar"), where "myPath/orai18n.jar" should be changed to the exact path where the jar can be found.
  4. Try to connect as usual.
JaehyeongCho commented 3 years ago

Thanks for your quick response! I can't do it right now, because I have to go to where the intranet environment is located, but I'll try it ASAP.

ChungsooKim commented 3 years ago

I tried to add a path using rJava::.jaddClassPath suggested by @schuemie and finally solved this error. Great thanks.

However, I met one more error having a message like below. It occurred when I sent a query using the DatabaseConnector::querySql function. (R 4.0.5 / DBMS: oracle / DatabaseConnector 4.0.2) -> java.sql.SQLException: Numeric Overflow.

It seemed it was because a specific column, person_id, had data type as "Number" without length limit in our database (Oracle). and to this, querySql function looked like trying to transform this to integer format using rJava::.jcall with "getInteger" method. is it correct?

image

Then, should I alter the data type of the person_id column to another type (Number(19) in oracle DDL) or are there any better options for this? The number of patients is 56M, so I would like to seek your advice before a change.

schuemie commented 3 years ago

Yes, adhering to the CDM DDL as close as possible is always advised (it is what we use when developing the software). Oracle doesn't really distinguish between integers and real numbers, so DatabaseConnector has to guess. Using NUMBER(19) has been shown to be correctly detected by DatabaseConnector.

Alternatively, you could try the version of DatabaseConnector in the develop branch:

remotes::install_github("ohdsi/DatabaseConnector", ref = "develop")

I did make some fixes related to integers in Oracle there, although I'm not sure they would solve your specific problem.

ChungsooKim commented 3 years ago

Thank you for your help. I'll try with the develop branch and also share whether it is working!

JaehyeongCho commented 2 years ago

This thread has been closed for a long time, but I'm re-commenting because the issue suggested by @ted9219 is now resolved. In Oracle, if the type of the person_id column in the CDM table is number for which precision is not specified, the following error occurs: java.sql.SQLException: Numeric Overflow.

We solved the overflow problem by changing the column type to number(19).