wrong column type with snowflake and writeTable

[dcaud]

DBI::dbWriteTable doesn't use "VARCHAR(16777216)" for a field.type when the input column is a long string. I'm getting this error when writing to snowflake via DBI::dbConnect(odbc::odbc()...

Error in result_insert_dataframe(rs@ptr, values, batch_rows) : 
  nanodbc/nanodbc.cpp:1655: 00000: String 'my string here ...' is too long and would be truncated 

If I manually specify field.types then all works, but I don't think I should have to do that.

[machow]

Running into this in a data pipeline, so thought I'd put some notes from researching whether there'd be a downside to VARCHAR(max). According to the snowflake docs, there's no performance or storage difference for using "VARCHAR(16777216)". Here's the relevant sections w/ emphasis below.

A column only consumes storage for the amount of actual data stored. For example, a 1-character string in a VARCHAR(16777216) column only consumes a single character.

There is no performance difference between using the full-length VARCHAR declaration VARCHAR(16777216) or a smaller size. Note that in any relational database, SELECT statements in which a WHERE clause references VARCHAR columns or string columns are not as fast as SELECT statements filtered using a date or numeric column condition. https://docs.snowflake.com/en/sql-reference/data-types-text.html

So changing to max VARCHAR should...

• prevent errors related to string length
• not have a performance or storage cost

[simonpcouch]

Closed in https://github.com/r-dbi/odbc/pull/599. :)