Have problem to use the module "DatabaseConnector"

Developers
1

I have successfully installed cohortMethod on my VA computer. But I have
problem to use the module “DatabaseConnector.”

The following code failed to connect our VA CDW datasource.

library(DatabaseConnector)

conn ← createConnectionDetails(dbms=“sql server”,
user=“vhaNN\vhaslcxxx”, password=“123456”,
server=“vhacdwYYYY.zzz.www.va.gov”,
schema=“PBM_XXXXXX”)

channel <-connect(conn)

Connecting using SQL Server driver
Error in .jcall(drv@jdrv, “Ljava/sql/Connection;”, “connect”,
as.character(url)[1], :
java.sql.SQLException: Login failed for user ‘vhaNN\vhaslcxxx’.

But if I directly use RJDBC based on JTDS drive, it works!

library(RJDBC)
drv ← JDBC(“net.sourceforge.jtds.jdbc.Driver”, “C:/jtds/jtds-1.3.1.jar”)
conn1 ← dbConnect(drv, “jdbc:jtds:sqlserver://vhacdwYYYY.zzz.www.va.gov
:1433;databaseName=PBM_XXXXXX;user=
vhaslcxxx;password=123456;domain=vhaNN”)

dbListTables(conn1)

[1] “All6_Sep14Jan15_0Day_DiagDate”

We know that “DatabaseConnector” is a wrapper of RJDBC. My Question is
what java driver is under the module "DatabaseConnector² and do we need to
do some configuration work to make “DatabaseConnector” work?

2

Hi tao, I wonder if it has to do with the version of Java you’re using?

From the databaseconnector readme:

Requires R. Also requires Java 1.6 or higher (Oracle Java is recommended.
Issues have been reported when using GCJ.)

3

I use Java 7 in my computer.

4

In connections using integrated security, DatabaseConnector uses sqljdbc4 and otherwise falls back to jtds. There may be a bug here because it expects domain and username to be separated by “/” whereas I think Windows violates convention and typically uses “”. Can you please try using user=“vhaNN/vhaslcxxx” and report back the outcome?

5

I think the problem is you’re using a backslash instead of a slash to separate domain and user name. Please try

conn <- createConnectionDetails(dbms="sql server",
user="vhaNN/vhaslcxxx", password="123456",
server="vhacdwYYYY.zzz.www.va.gov",
schema="PBM_XXXXXX")
6

I think this should be considered a bug since Windows always uses backslash to split domain and username. What do you think?

7

it works now after I use “/” but it is true windows use “” as domain separator. Might we need to add a parameter specially for domain to avoid this error in the next version?

8

I just added the domain parameter. Specifying the domain in the user argument will now throw a (hopefully) meaningful error. So now you should be able to connect with:

conn <- createConnectionDetails(dbms="sql server",
user="vhaslcxxx", domain="vhaNN", password="123456",
server="vhacdwYYYY.zzz.www.va.gov",
schema="PBM_XXXXXX")
9

removed