Datetime field overflow issues from version 1.6.0 onwards

[NyxHypnos]

Database Oracle.
System POSIT.

After updating packages I started getting datetime field overflow error for all datetimes. I tried various formatting like as.POSIXct or format, etc. but the only thing that worked as using a pure date not a datetime.

When debugging I figured out that I could run the following code on 1.5.0, but with 1.6.0 onwards I get the error below.

simple_table_working_df <- data.frame(ID = "TEST", SUBMITTED = "2021-01-01 01:01:01")
DBI::dbAppendTable(conn = con_main, table_name, simple_table_working_df)

Error:
! nanodbc/nanodbc.cpp:1867: 00000
[RStudio][Support] (40520) Datetime field overflow resulting from invalid datetime. 
Hide Traceback
    ▆
 1. ├─DBI::dbAppendTable(conn = con_main, table_name, simple_table_working_df)
 2. └─DBI::dbAppendTable(conn = con_main, table_name, simple_table_working_df)
 3.   └─odbc (local) .local(conn, name, value, ..., row.names = row.names)
 4.     ├─base::tryCatch(...)
 5.     │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
 6.     └─odbc:::result_insert_dataframe(rs@ptr, values, batch_rows)

[detule]

Hey - thanks for the report! Can you expand your example to either include the table structure of the target table you are writing to, or ideally to create the table from scratch.

Please also provide the database and driver information as in this example below:

> simple_table_working_df <- data.frame(ID = "TEST", SUBMITTED = "2021-01-01 01:01:01")
> DBI::dbWriteTable(conn, "test_table", simple_table_working_df)
> DBI::dbReadTable(conn, "test_table")
    ID           SUBMITTED
1 TEST 2021-01-01 01:01:0
> conn@info
...
$dbms.name
[1] "Oracle"
...
$db.version
[1] "21.00.0000"
...
$drivername
[1] "SQORA32.DLL"
...
$driver.version
[1] "21.08.0000"

[NyxHypnos]

So an example of the table I'm writing to would be

CREATE TABLE TEST_TABLE (
    ID                                       VARCHAR2(255 CHAR) NOT NULL,
   SUBMITTED                                DATE
)

DBI::dbReadTable(con_main, table_name) 
ID SUBMITTED        
FAA1A1  2025-07-08 09:43:09

> con_main@info
$dbms.name
[1] "Oracle"

$db.version
[1] "19.00.0000"

$drivername
[1] "RStudio Oracle ODBC Driver"

$odbc.version
[1] "03.52"

$driver.version
[1] "2.0.10.1010"

$odbcdriver.version
[1] "03.80"

[detule]

Thanks - was able to replicate.

Looks like issue is isolated to Posit PRO drivers; the OEM oracle driver seems to be able to handle the write just fine. I'll try and understand if there is something we should be doing differently in our package so that the write succeeds with both drivers.

I'll circle back once i have more.

Thanks again.

[detule]

Hi - thanks again for the report.

Had a chance to look into this - if you are able to, can you give this branch a try? The change in nanodbc has the potential to impact other drivers and back-ends; need to look into how our unit test suite fares with it.

Just to provide some context for future self, or other interested parties:

• You are seeing an issue writing character dates to DATE targets. Seems to affect the pro driver only.
• Starting with 1.5.1, we fixed a long standing issue and since then one is able to write Date and POSIXct [R] columns in batch sizes > 1.
• In doing so we wrote a custom query to suss out the parameter meta data (type, size, etc) in parameterized queries - our dbWriteTable and dbAppendTable are implemented with such a parametrized query.
• Oracle has a custom mapping of its DATE type. In particular, this type has a timestamp and both the oem and pro drivers map the oracle DATE type to the ODBC SQL_TYPE_TIMESTAMP (this is documented for both drivers; see for example here.).
• In this patch, I updated our custom "suss out paramter meta data" query to report "SQL_TYPE_TIMESTAMP" for any DATE columns since it looks like while the OEM driver can internalize the translation, the PRO Driver needs this explicitly stated by the caller.
• This then broke writing [R] Date types to Oracle DATE targets with the OEM driver. Call this "new" problem.
• Here is a little aside: The SQLBindParameter API call asks for two column/buffer sizes.
  • Column size - this is the implementation (read driver) defined size of the sql parameter type. Since we are reporting "TYPE_TIMESTAMP" this has to be the size of nanodbc::timestamp, I believe. When we can, we provide this value from [R] via the odbcConnectionColumns_ S4 method.
  • Buffer length - this value is used for character and binary data and should be the length of the C buffers that are bound to the parameter. We don't intentionally interact with this field from the [R] side, and let nanodbc handle.
• nanodbc has differentiated handling when binding character vs binary buffers:
  • When binding character buffers, nanodbc will analyze the buffers and infer the buffer size to report in the SQLBindParamters call.
  • When binding all other buffers, it will default back to the column size field for the buffer length.
• This last bit i think is where we are seeing the "new" problem. When writing the [R] Date columns into an Oracle DATE targets, we are reporting data of type TIMESTAMP and column size 16. nanodbc then uses that as the buffer length argument for c data type SQL_C_DATE. This is incorrect since the nanodbc::date type is 6 bytes long. The pro driver is robust to this misspecification. The OEM driver is not.

[NyxHypnos]

Thanks for investigating and providing the detailed response. I would like to test your branch, but the system has no direct internet access, and there are other policy issues preventing me from trying it. If it relates to POSIT, I don't suppose they would have someone who can replicate the issue and test your fix?

[detule]

No problem @NyxHypnos:

Was able to test with the POSIT drivers and verify the change works as expected. I opened a PR - as part of the PR there is a unit test that should cover this use case. If/when the PR is merged with main, those unit tests will run against both the OEM as well as PRO drivers and at that point we should have a nice litmus test in place.