dbWriteTable error with empty char column

[tomazweiss]

I have a R table with an empty column of type character. When I try to write this table to Exasol with dbWriteTable (to a non-existing table), an invalid CREATE TABLE statement is generated:

[EXASOL][EXASolution driver]syntax error, unexpected '-', expecting UNSIGNED_INTEGER_ [line 1, column 815] 

In addition: Warning message:
In max(nchar(as.character(x)), na.rm = TRUE) :
  no non-missing arguments to max; returning -Inf

In these cases, the function max(nchar(as.character(x)), na.rm = TRUE) returns -Inf and the generated CREATE TABLE statement tries to create a column of type VARCHAR(-Inf).

Workaround:
backup your R table, identify empty char columns, temporary fill them with something, execute dbWriteTable, create and save DDL statement on exasol, drop table on exasol, create new table on exasol with saved DDL and fix varchar lenghts, write backup table to exasol.

[tkilias]

Hello @tomazweiss,

Thank you for reporting this bug.

I think, the problem happens here. The function dbDataType can't determine the database type for the empty column in the data.frame. I am not sure, if this is fixable, I need to look deeper into this problem.
However, you can use field_types parameter to specify the database types for the columns directly to work around the problem.

r-exasol/R/EXADBI.R

Line 1345 in 506ba2d

#' @param field_types A character vector containing the column data types, in the form