I am trying to connect to my MS SQL database with RJDBC and I don't know what to fill in url argument. With odbc this was enough:
dbConnect(odbc::odbc(),Driver = "SQL Server",dsn = "MyDsn",uid = "User",pwd = "123456",server = "myserver123456\\myserver1",database = "MyDatabase")When I swap the driver from odbc to jdbc then it fails:
dbConnect(RJDBC::JDBC(classPath = "C:/jdbc/mssql-jdbc-7.0.0.jre8.jar"),Driver = "SQL Server",dsn = "MyDsn",uid = "User",pwd = "123456",server = "myserver123456\\myserver1",database = "MyDatabase")error: Error in .jcall("java/sql/DriverManager", "Ljava/sql/Connection;", "getConnection", : argument "url" is missing, with no default
What should i write in url argument? How to get know?
Best Answer
RJDBC uses different arguments for the dbConnect function: a driver definition and a connection URL (the piece you are missing). For example (from https://www.rforge.net/RJDBC/), to connect to a MySQL Database, your code would look like the following:
library(RJDBC)drv <- JDBC("com.mysql.jdbc.Driver","/etc/jdbc/mysql-connector-java-3.1.14-bin.jar",identifier.quote="`")conn <- dbConnect(drv, "jdbc:mysql://localhost/test", "user", "pwd")Loads a JDBC driver for MySQL (adjust the path to the driver's JAR file as necessary) and connects to the local database "test". The connection handle conn is used for all subsequent operations.
For SQL Server, your code will look something like the following (from https://www.r-bloggers.com/connecting-to-sql-server-from-r-using-rjdbc/):
require(RJDBC)drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver","C:/jdbc/mssql-jdbc-7.0.0.jre8.jar") conn <- dbConnect(drv, "jdbc:sqlserver://serverName", "userID", "password")