Resolving "incompatible types" error with sqlUpdate

I'm attempting to write the following data via RODBC to a database. How can I resolve the error below or what are some additional troubleshooting steps?

writeData  <- structure(list(`__ActivityID` = c("8000002", "8000003", "8000005"), 
`__ID` = c("ZRH_06d88315", "ZRH_8161772a", "ZRH_b9f9798a")), 
.Names = c("__ActivityID", "__ID"), row.names = c(NA, 3L), class = "data.frame")

Here's the command which should update the database. I've also tried it with test = false:

sqlUpdate(channel = liveConnection, dat = writeData, 
          tablename = "__Marketing Activities", 
index = "__ActivityID", nastring = NULL, test = TRUE)

It returns this error:

# Query: UPDATE "__Marketing Activities" SET "__ID"=? WHERE "__ActivityID"=?
Error in sqlUpdate(channel = con$liveConnection, dat =     
res$writeDataNewPKs$`__Marketing Activities`,  : 
HY000 8309 [FileMaker][FileMaker ODBC] FQL0013/(1:36): 
Incompatible types in assignment.[RODBC] 
ERROR: Could not SQLPrepare 'UPDATE "__Marketing Activities" 
SET "__ID"=? WHERE "__ActivityID"=?'

Here's the structural information about the __ID column:

  TABLE_CAT TABLE_SCHEM             TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
1      <NA>        <NA> __Marketing Activities        __ID        12   VARCHAR     1000000       1000000             NA
  NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
1             NA        0    <NA>                       12               NA           1000000              180          NO

Here's the structural information about the __ActivityID column:

  TABLE_CAT TABLE_SCHEM             TABLE_NAME  COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
1      <NA>        <NA> __Marketing Activities __ActivityID         8   DECIMAL          15             8             15
  NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
1             -1        1    <NA>                        8               NA                NA               39         YES

I've attempted writing other data to the same columns and I still receive the same error.

writeData  <-structure(list(`__ActivityID` = c("8000002", "8000003", "8000005", 
"8000008", "8000009", "8000010"), `__ID` = 1:6), .Names = c("__ActivityID", 
"__ID"), row.names = c(NA, 6L), class = "data.frame")

When writing to other columns in the same database, I don't have any issues.

I've also tried converting both of the columns in writeData to these classes: numeric, character, integer.

I suspect that there's something about the data type in the __ID column that I don't understand on the database side. By the way, this is connecting to a FileMaker 11 database.

Lastly, here's some data about the connection itself. It was created with this command:

sqlTypeInfo(con$liveConnection, type = "all", errors = TRUE, as.is = TRUE)

And here's the output:

structure(list(TYPE_NAME = c("VARCHAR", "CHARACTER VARYING", 
"DECIMAL", "NUMERIC", "INT", "DECIMAL", "DECIMAL", "BLOB", "VARBINARY", 
"LONGVARBINARY", "BINARY VARYING", "DATE", "TIME", "TIMESTAMP"
), DATA_TYPE = c(12L, 12L, 3L, 2L, 4L, 6L, 8L, -4L, -4L, -4L, 
-4L, 9L, 10L, 11L), COLUMN_SIZE = c(-1L, -1L, 10L, 10L, 10L, 
15L, 15L, -1L, -1L, -1L, -1L, 10L, 10L, 19L), LITERAL_PREFIX = c("'", 
"'", NA, NA, NA, NA, NA, "X'", "X'", "X'", "X'", "{d '", "{t '", 
"{ts '"), LITERAL_SUFFIX = c("'", "'", NA, NA, NA, NA, NA, "'", 
"'", "'", "'", "'}", "'}", "'}"), CREATE_PARAMS = c("length", 
"length", "precision,scale", "precision,scale", NA, NA, NA, NA, 
NA, NA, NA, NA, "length", "length"), NULLABLE = c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), CASE_SENSITIVE = c(1L, 
1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), SEARCHABLE = c(3L, 
3L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 2L, 2L, 2L), UNSIGNED_ATTRIBUTE = c(NA, 
NA, 0L, 0L, 0L, 0L, 0L, NA, NA, NA, NA, NA, NA, NA), FIXED_PREC_SCALE = c(0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), AUTO_UNIQUE_VALUE = c(NA, 
NA, 0L, 0L, 0L, 0L, 0L, NA, NA, NA, NA, NA, NA, NA), LOCAL_TYPE_NAME = c("varchar", 
"character varying", "decimal", "numeric", "int", "decimal", 
"decimal", "blob", "varbinary", "longvarbinary", "binary varying", 
"date", "time", "timestamp"), MINIMUM_SCALE = c(NA, NA, 0L, 0L, 
0L, NA, NA, NA, NA, NA, NA, NA, NA, 0L), MAXIMUM_SCALE = c(NA, 
NA, 15L, 15L, 0L, NA, NA, NA, NA, NA, NA, NA, NA, 0L), SQL_DATA_TYPE = c(12L, 
12L, 3L, 2L, 4L, 6L, 8L, -4L, -4L, -4L, -4L, 9L, 9L, 9L), SQL_DATETIME_SUB = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1L, 2L, 3L), NUM_PREC_RADIX = c(NA, 
NA, 10L, 10L, 10L, 10L, 10L, NA, NA, NA, NA, NA, NA, NA), INTERVAL_PRECISION = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_)), .Names = c("TYPE_NAME", 
"DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREFIX", "LITERAL_SUFFIX", 
"CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", 
"UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", 
"LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", 
"SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERVAL_PRECISION"), row.names = c(NA, 
14L), class = "data.frame")

Solution

The error is resolved by changing the IS_NULLABLE parameter of the __ID column to true.

In FileMaker, this is done by turning off the Not empty field option as show below.

Perhaps someone can also explain why this is the case? I've only tested that it works and was able to write to the column.