Does anyone know of a way to download blob data from an Oracle database using RJDBC package?
有没有人知道使用RJDBC包从Oracle数据库下载blob数据的方法?
When I do something like this:
当我做这样的事情时:
library(RJDBC)
drv <- JDBC(driverClass=..., classPath=...)
conn <- dbConnect(drv, ...)
blobdata <- dbGetQuery(conn, "select blobfield from blobtable where id=1")
I get this message:
我收到这条消息:
Error in .jcall(rp, "I", "fetch", stride) :
java.sql.SQLException: Ongeldig kolomtype.: getString not implemented for class oracle.jdbc.driver.T4CBlobAccessor
Well, the message is clear, but still I hope there is a way to download blobs. I read something about 'getBinary()' as a way of getting blob information. Can I find a solution in that direction?
嗯,消息很明确,但我仍然希望有一种方法可以下载blob。我读了一些关于'getBinary()'作为获取blob信息的方法。我可以找到朝这个方向的解决方案吗?
1 个解决方案
#1
The problem is that RJDBC tries to convert the SQL data type it reads to either double or String in Java. Typically the trick works because JDBC driver for Oracle has routines to convert different data types to String (accessed by getString() method of java.sql.ResultSet class). For BLOB, though, the getString() method has been discontinued from some moment. RJDBC still tries calling it, which results in an error.
问题是RJDBC尝试将它读取的SQL数据类型转换为Java中的double或String。通常这种技巧是有效的,因为Oracle的JDBC驱动程序具有将不同数据类型转换为String的例程(由java.sql.ResultSet类的getString()方法访问)。但是对于BLOB,getString()方法已经停止了。 RJDBC仍在尝试调用它,这会导致错误。
I tried digging into the guts of RJDBC to see if I can get it to call proper function for BLOB columns, and apparently the solution requires modification of fetch S4 method in this package and also the result-grabbing Java class within the package. I'll try to get this patch to package maintainers. Meanwhile, quick and dirty fix using rJava (assuming conn and q as in your example):
我试着挖掘RJDBC的内容,看看我是否可以让它为BLOB列调用正确的函数,显然该解决方案需要修改此包中的fetch S4方法以及包中的结果抓取Java类。我将尝试将此补丁包装到包维护者。同时,使用rJava进行快速而脏的修复(假设你的例子中有conn和q):
s <- .jcall(conn@jc, "Ljava/sql/Statement;", "createStatement")
r <- .jcall(s, "Ljava/sql/ResultSet;", "executeQuery", q, check=FALSE)
listraws <- list()
col_num <- 1L
i <- 1
while(.jcall(r, 'Z', 'next')){
listraws[[i]] <- .jcall(r, '[B', 'getBytes', col_num)
i <- i + 1
}
This retrieves list of raw vectors in R. The next steps depend on the nature of data - in my application these vectors represent PNG images and can be handled pretty much as file connections by png package.
这将检索R中的原始向量列表。接下来的步骤取决于数据的性质 - 在我的应用程序中,这些向量表示PNG图像,并且可以像png包一样处理文件连接。
Done using R 3.1.3, RJDBC 0.2-5, Oracle 11-2 and OJDBC driver for JDK >= 1.6
使用R 3.1.3,RJDBC 0.2-5,Oracle 11-2和OJDBC驱动程序完成JDK> = 1.6
#1
The problem is that RJDBC tries to convert the SQL data type it reads to either double or String in Java. Typically the trick works because JDBC driver for Oracle has routines to convert different data types to String (accessed by getString() method of java.sql.ResultSet class). For BLOB, though, the getString() method has been discontinued from some moment. RJDBC still tries calling it, which results in an error.
问题是RJDBC尝试将它读取的SQL数据类型转换为Java中的double或String。通常这种技巧是有效的,因为Oracle的JDBC驱动程序具有将不同数据类型转换为String的例程(由java.sql.ResultSet类的getString()方法访问)。但是对于BLOB,getString()方法已经停止了。 RJDBC仍在尝试调用它,这会导致错误。
I tried digging into the guts of RJDBC to see if I can get it to call proper function for BLOB columns, and apparently the solution requires modification of fetch S4 method in this package and also the result-grabbing Java class within the package. I'll try to get this patch to package maintainers. Meanwhile, quick and dirty fix using rJava (assuming conn and q as in your example):
我试着挖掘RJDBC的内容,看看我是否可以让它为BLOB列调用正确的函数,显然该解决方案需要修改此包中的fetch S4方法以及包中的结果抓取Java类。我将尝试将此补丁包装到包维护者。同时,使用rJava进行快速而脏的修复(假设你的例子中有conn和q):
s <- .jcall(conn@jc, "Ljava/sql/Statement;", "createStatement")
r <- .jcall(s, "Ljava/sql/ResultSet;", "executeQuery", q, check=FALSE)
listraws <- list()
col_num <- 1L
i <- 1
while(.jcall(r, 'Z', 'next')){
listraws[[i]] <- .jcall(r, '[B', 'getBytes', col_num)
i <- i + 1
}
This retrieves list of raw vectors in R. The next steps depend on the nature of data - in my application these vectors represent PNG images and can be handled pretty much as file connections by png package.
这将检索R中的原始向量列表。接下来的步骤取决于数据的性质 - 在我的应用程序中,这些向量表示PNG图像,并且可以像png包一样处理文件连接。
Done using R 3.1.3, RJDBC 0.2-5, Oracle 11-2 and OJDBC driver for JDK >= 1.6
使用R 3.1.3,RJDBC 0.2-5,Oracle 11-2和OJDBC驱动程序完成JDK> = 1.6