Segmentation fault on reading 100 rows of TIMESTAMP(3) data from Oracle DB

[gedrox]

Segmentation fault on reading at least 100 rows of TIMESTAMP(3) (which is not the default precision) data from Oracle DB using RODBCext 0.3.0 on R 3.3.2. Also fails in R 3.4.0 and R 3.4.1.

The code works fine with RODBCext 0.2.7.

Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Code to reproduce:

# These are main conditions to reproduce:
#  * Not default timestamp column precision (TIMESTAMP(3) instead of default 6 digit precision)
#  * at least 100 rows in resultset

library("RODBCext")
sessionInfo()

channel <- odbcConnect("***********", uid = "*********", pwd = "*********")

message("DROP..", appendLF = FALSE)
sqlExecute(channel, "drop table ag_rodbcext030_segfault", errors = FALSE)
message(" [OK]")

message("CREATE..", appendLF = FALSE)
sqlExecute(channel, "create table ag_rodbcext030_segfault (dt TIMESTAMP(3))")
for (i in 1:100) {
  sqlExecute(channel, "insert into ag_rodbcext030_segfault values (sysdate)")
}
message(" [OK]")

message("COUNT..", appendLF = FALSE)
data <- sqlExecute(channel, "select count(*) from ag_rodbcext030_segfault", fetch = TRUE, stringsAsFactors = FALSE)
message(" [", data[1, 1], "]")

# RODBCext 0.3.0 fails here with Segmentation fault on Windows..
message("SELECT..", appendLF = FALSE)
data <- sqlExecute(channel, "select * from ag_rodbcext030_segfault", fetch = TRUE, stringsAsFactors = FALSE)
message(" [", paste(dim(data), collapse = " x "), "]")

odbcClose(channel)

Output:

Loading required package: RODBC

Attaching package: 'RODBCext'

The following objects are masked from 'package:RODBC':

    odbcFetchRows, sqlFetchMore

Warning message:
package 'RODBC' was built under R version 3.3.3
R version 3.3.2 (2016-10-31)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=English_United States.1252
[2] LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  base

other attached packages:
[1] RODBCext_0.3.0 RODBC_1.3-15
DROP.. [OK]
CREATE.. [OK]
COUNT.. [100]
SELECT..
This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

[gedrox]

Caused by line removal https://github.com/zozlak/RODBCext/commit/359506b3f51996151c95b09d10171c5c654844d4#diff-54c58beea857a89caa55e1b73c348b4cL290 which capped replaced the value of datalen to 256 if it's below.

[zozlak]

The only Oracle instance I can test against is 11g express edition but I hope it should not make a difference.

I will look at it by the end of week.

[zozlak]

It looks like a problem with the Oracle ODBC driver.

• When asked about a column the Oracle ODBC driver reports column's internal length (26 for timestamp, 20 + n for timestamp(n)).
• But timestamp data passed to the client have always length of 26 characters, no matter what the internal database size is.
  • It is perfectly fine to allocate only 26 byte buffers for timestamp(9) and there are no segfaults (although reported column length is 29).
  • It is required to allocate 26 byte buffers for timestamp(0) to avoid segfaults (although reported column length is 20).

My guess is that while there is only one timestamp data type in ODBC the Oracle ODBC driver is internally casting all the timestamps to Oracle's "ordinary" timestamp before passing them to the client. So sad it doesn't report column length accordingly...

The workaround will be to add a check

if (column->dataType == SQL_TYPE_TIMESTAMP && datalen < 26) {
    datalen = 26;
}

It's not very pretty. The standard ODBC timestamp length is 20 and it will unnecessarily increase memory usage for ODBC drivers passing timestamps shorter then 26 characters. On the other hand who would care about 6 bytes per row nowadays. Especially taking into account that by default 100 rows are fetched at once.

I should upload updated version to CRAN next week.

[zozlak]

On CRAN now.