driver class, class path, quote identifiers for JDBC driver to Sybase IQ database in R language?

I'm trying to make a JDBC connection to Sybase IQ in the R language. One of the first steps appears to be to create a new DBI driver. To do this, I need the driver class, the class Path, and the quote identifier. I've used two options below that have ended up crashing R:

drive <- JDBC (driverClass = "com.sybase.jdbc4.jdbc.SybDriver", classPath = "/opt/app/SybaseIQ/IQ-16_1/java/sajdbc4.jar", identifier.quote = NA)

drive <- JDBC (driverClass = "com.sybase.jdbc4.jdbc.SybDriver", classPath = "/opt/app/SybaseIQ/jConnect-16_0/classes/jconn4.jar", identifier.quote = NA)

I'm not sure what I'm specifying incorrectly. Any suggestions are appreciated.

Below is some of the documentation from R, which may be helpful:

JDBC engine

Description

JDBC creates a new DBI driver that can be used to start JDBC connections.

Usage

JDBC (driverClass = "", classPath = "", identifier.quote = NA)

Arguments

driverClass
name of the Java class of the JDBC driver to load. If empty, it is assumed that corresponding JDBC drivers were loaded by other means.

classPath
class path that needs to be appended in order to load the desired JDBC driver. Usually it is the path to the JAR file containing the driver.

identifier.quote
character to use for quoting identifiers in automatically generated SQL statements or NA if the back-end doesn't support quoted identifiers. See details section below.

Details

JDBC function has two purposes. One is to initialize the Java VM and load a Java JDBC driver (not to be confused with the JDBCDriver R object which is actually a DBI driver). The second purpose is to create a proxy R object which can be used to a call dbConnect which actually creates a connection.

JDBC requires a JDBC driver for a database-backend to be loaded. Usually a JDBC driver is supplied in a Java Archive (jar) file. The path to such a file can be specified in classPath. The driver itself has a Java class name that is used to load the driver (for example the MySQL driver uses com.mysql.jdbc.Driver), this has to be specified in driverClass.

Due to the fact that JDBC can talk to a wide variety of databases, the SQL dialect understood by the database is not known in advance. Therefore the RJDBC implementation tries to adhere to the SQL92 standard, but not all databases are compliant. This affects mainly functions such as dbWriteTable that have to automatically generate SQL code. One major ability is the support for quoted identifiers. The SQL92 standard uses double-quotes, but many database engines either don't support it or use other character. The identifier.quote parameter allows you to set the proper quote character for the database used. For example MySQL would require identifier.quote="`". If set to NA, the ability to quote identifiers is disabled, which poses restrictions on the names that can be used for tables and fields. Other functionality is not affected.

As of RDJBC 0.2-2 JDBC-specific stored procedure calls starting with {call and {?= call are supported in the statements.

Value

Returns a JDBCDriver object that can be used in calls to dbConnect.

Examples

## Not run: drv <- JDBC("com.mysql.jdbc.Driver", "/etc/jdbc/mysql-connector-java-3.1.14-bin.jar", "`") conn <- dbConnect(drv, "jdbc:mysql://localhost/test") dbListTables(conn) data(iris) dbWriteTable(conn, "iris", iris) dbGetQuery(conn, "select count(*) from iris") d <- dbReadTable(conn, "iris")

## End(Not run)

asked 29 Apr '21, 17:04

Ted M
26●1●1●5
accept rate: 0%

edited 29 Apr '21, 17:06

flat view

One Answer:

active answers oldest answers newest answers popular answers

The driver class for the SQL Anywhere JDBC driver (sajdbc4.jar) is: sap.jdbc4.sqlanywhere.IDriver. The driver class for jConnect driver (jconn4.jar) is com.sybase.jdbc4.jdbc.SybDriver.

Here is sample R code (which assumes you have installed R JDBC) for Windows. Provide the full path to the jar for your specific OS/setup. Also note, you will need to source the IQ environment so that it is visible to the R development environment when using the SQL Anywhere JDBC driver as it has dependencies on native libraries.

SQL Anywhere JDBC Driver

require(RJDBC)
drv <- JDBC( "sap.jdbc4.sqlanywhere.IDriver", "c:/sa/sa17/java/sajdbc4.jar" )
conn <- dbConnect(drv, "jdbc:sqlanywhere:eng=demo17;dbn=demo", "dba", "sql")

jConnect JDBC Driver

require(RJDBC)
drv <- JDBC( "com.sybase.jdbc4.jdbc.SybDriver", "c:/sa/sa17/java/jconn4.jar" )
conn <- dbConnect(drv, "jdbc:sybase:Tds:localhost:2638", "dba", "sql")

If you continue to have problems, please include the specific errors that you are seeing.

permanent link

answered 30 Apr '21, 00:43

Chris Keating
7.0k●45●116
accept rate: 30%

Thank you, Chris.

Here is the error for the SQL Anywhere JDBC Driver

> drv <- JDBC( "sap.jdbc4.sqlanywhere.IDriver", "/opt/app/SybaseIQ/IQ-16_1/java/sajdbc4.jar" ) Error occurred during initialization of VM java/lang/NoClassDefFoundError: java/lang/Object

Here is the (same) error for the jConnect JDBC Driver

> drv <- JDBC( "com.sybase.jdbc4.jdbc.SybDriver", "/opt/app/SybaseIQ/jConnect-16_0/classes/jconn4.jar" ) Error occurred during initialization of VM java/lang/NoClassDefFoundError: java/lang/Object

I'm using a Red Hat Linux platform. Here's the top few lines of the sessionInfo() function:

> sessionInfo() R version 3.5.3 (2019-03-11) Platform: x86_64-pc-linux-gnu (64-bit) Running under: Red Hat Enterprise Linux Server 7.9 (Maipo)

Here is the Java version information:

$ java -version openjdk version "1.8.0_265" OpenJDK Runtime Environment (build 1.8.0_265-b01) OpenJDK 64-Bit Server VM (build 25.265-b01, mixed mode)

I received the same errors using either the Java Development Kit or the Java Runtime Environment as Java Home:

# Below is for Java Development Kit (JDK) as Java Home export JAVA_HOME=$(dirname $(dirname $(readlink -f $(which javac)))) $ echo $JAVA_HOME /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.265.b01-1.el7_9.x86_64 # Below is for Java Runtime Environment (JRE) as Java Home (current setting) export JAVA_HOME=$(dirname $(dirname $(readlink -f $(which java)))) $ echo $JAVA_HOME /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.265.b01-1.el7_9.x86_64/jre

In case this is related to some environmental variable settings, I've listed the output from the Sys.getenv() function below:

> Sys.getenv() CLICOLOR_FORCE 1 DISPLAY :0 EDITOR vi GDAL_DATA /usr/share/epsg_csv GIT_ASKPASS rpostback-askpass HOME /home/UID HOSTNAME xxx.xxx.xxx.xxx.xxx IQDIR16 /opt/app/SybaseIQ/IQ-16_1 LANG en_US.UTF-8 LD_LIBRARY_PATH /opt/glpk-4.65/lib:/usr/local/lib:usr/local/lib64:/opt/app/SybaseIQ/IQ-16_1/lib64 LN_S ln -s LOGNAME UID MAKE make ODBCINI /opt/app/SybaseODBC/odbc.ini PAGER /bin/less PATH /opt/R/R-3.5.3/bin:/opt/Python-3.7.4/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/opt/app/SybaseIQ/IQ-16_1/bin64 R_BROWSER /bin/xdg-open R_BZIPCMD /bin/bzip2 R_DOC_DIR /opt/R/R-3.5.3/lib64/R/doc R_GZIPCMD /bin/gzip R_HOME /opt/R/R-3.5.3/lib64/R R_INCLUDE_DIR /opt/R/R-3.5.3/lib64/R/include R_LIBS_SITE R_LIBS_USER /projects/Analysts/R/x86_64-pc-linux-gnu-library/3.5 R_PAPERSIZE a4 R_PDFVIEWER /bin/xdg-open R_PLATFORM x86_64-pc-linux-gnu R_PRINTCMD lpr R_RD4PDF times,hyper R_REPOSITORIES repos R_SESSION_TMPDIR /tmp/Rtmpba3iva R_SHARE_DIR /opt/R/R-3.5.3/lib64/R/share R_SYSTEM_ABI linux,gcc,gxx,gfortran,? R_TEXI2DVICMD texi2dvi R_UNZIPCMD /bin/unzip R_ZIPCMD /bin/zip RMARKDOWN_MATHJAX_PATH /usr/lib/rstudio-server/resources/mathjax-26 RS_RPOSTBACK_PATH /usr/lib/rstudio-server/bin/rpostback RSTUDIO 1 RSTUDIO_CONSOLE_COLOR 256 RSTUDIO_CONSOLE_WIDTH 148 RSTUDIO_PANDOC /usr/lib/rstudio-server/bin/pandoc RSTUDIO_R_MODULE RSTUDIO_R_PRELAUNCH_SCRIPT RSTUDIO_R_VERSION_LABEL RSTUDIO_SESSION_STREAM UID-d RSTUDIO_USER_IDENTITY UID RSTUDIO_VERSION 1.2.1335 RSTUDIO_WINUTILS bin/winutils SAP_JRE8 /opt/app/SybaseIQ/shared/SAPJRE-8_1_046_64BIT SAP_JRE8_64 /opt/app/SybaseIQ/shared/SAPJRE-8_1_046_64BIT SED /bin/sed SSH_ASKPASS rpostback-askpass SYBASE /opt/app/SybaseIQ SYBROOT /opt/app/SybaseIQ TAR /bin/gtar TERM xterm-256color USER UID

Please let me know if there's anything else I can provide, and thank you for taking a look!

(30 Apr '21, 10:10) Ted M

I believe that this suggests a problem with resolving the JRE and in particular the rt.jar. I do not have a lot of *nix experience so I am not sure specifically what that translates to in terms of the environment. The error itself is not specific to R or the SQLA or jConnect JDBC drivers. If you look at the output from Sys.getenv(), I do not see $JAVA_HOME defined nor $JAVA_HOME/bin in the path.

(30 Apr '21, 20:19) Chris Keating

Thank you for the feedback. I made additions to the .Renviron file so that Sys.getenv() shows the $JAVA_HOME, $PATH, and $CLASSPATH. Unfortunately, this made no difference. I'll try to reach out to a more general forum regarding Java to see if I can get assistance. Thanks again.

(03 May '21, 10:03) Ted M

If someone needs to follow this thread, please see https://github.com/s-u/rJava/issues/263 .

(03 May '21, 11:59) Ted M

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

connection ×157
jdbc ×86
java ×76
databasestart ×16
driver ×16

question asked: 29 Apr '21, 17:04

question was seen: 1,542 times

last updated: 03 May '21, 11:59

SAP SQL Anywhere