Unable to save an R data frame to filemaker Pro database using SQL and ODBC connection

Question

I have a Filemaker Pro 12 relational database of lung cancer patients treated for surgery. Because one patient may have one or more cancers removed at one or more operations, etc., the patient characteristics/demographics are in one table (Patient), the surgery they have had is in another table (Treatment) linked by PatientID and the cancer characteristics are in another table (Pathology) identified by PatientID, but linked via the SurgeryID of the surgery table from which the cancer was removed.

I want to backup all the "inner joined" data as a flat file with a record for each different cancer and each operation for every patient (so there will be multiple same patient records if they have had multiple cancers and/or operations).

I have therefore imported the 3 Filemaker tables Patient, Treatment, Pathology using:

con = odbcConnect("LC", uid = "uid", pwd = "pwd")
PatSurg <-sqlQuery(con, paste("SELECT * FROM Patient P INNER JOIN Treatment T ON P.PatientID = T.PatientID"))
PatPath <-sqlQuery(con, paste("SELECT * FROM Patient P INNER JOIN Pathology H ON P.PatientID = H.PatientID"))
Full <- merge(PatSurg, PatPath, by = intersect(names(PatSurg), names(PatPath)))
View(Full)

This successfully created the data frame (Full) exactly as the flat flat file I want to save into an empty existing Filemaker Pro database with identical column names with a table Thoracic.

I then thought I'd try the simple instruction:

sqlSave(con, Full, tablename = "Thoracic")

and was returned the error:

Error in sqlSave(con, Full, tablename = "Thoracic") : table ‘Thoracic’ already exists

Undeterred, I dropped the table name and re-saved it:

sqlDrop(con, "Thoracic", errors = FALSE)
sqlSave(con, Full, tablename = "Thoracic")

which returned a new error:

Error in sqlSave(con, Full, tablename = "Thoracic") : [RODBC] Failed exec in Update HY000 502 [FileMaker][FileMaker] (502): Field failed numeric value validation test

I then thought, maybe I should use the sqlQuery command and INSERT, but wasn't sure how to refer to my R data frame when as an expression for SQL/ODBC. I tried:

sqlQuery(con, "INSERT INTO Thoracic ", Full)
sqlQuery(con, "INSERT INTO Thoracic VALUES", Full)

Both returned:

Error in if (errors) return(odbcGetErrMsg(channel)) else return(invisible(stat)) : argument is not interpretable as logical In addition: Warning message: In if (errors) return(odbcGetErrMsg(channel)) else return(invisible(stat)) : the condition has length > 1 and only the first element will be used

sqlAppendTable(con, "Thoracic", Full)

Returned:

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘sqlAppendTable’ for signature ‘"RODBC"’

sqlQuery(con, "INSERT INTO Thoracic VALUES Full")

Returned:

[1] "42000 8310 [FileMaker][FileMaker] FQL0001/(1:29): There is an error in the syntax of the query." [2] "[RODBC] ERROR: Could not SQLExecDirect 'INSERT INTO Thoracic VALUES Full'"

How should I refer to my R data frame "Full" in the SQL query. All of the questions I have seen relate only to importing data from OBDC source to a data frame. That's the easiest bit!

Answer 1

I have found the obvious workaround

I have installed WriteXLS and exported the data frame to an XLSX file, then simply imported using the first row as column names:

install.packages("WriteXLS")
library(WriteXLS)
WriteXLS(Full, "SQL Backup.xlsx")

Then used the import function of Filemaker Pro. There were some problems with free text containing certain characters, and dates all returned in the format yyyy-mm-dd regardless of original format. The main problem was that there could be an intra-row right frame shift if free text contained a separator, therefore that should be the last column. I just corrected those in the XLSX file and re-imported as an update based on PatientID, SurgeryID and DiagnosisID.

Addit: Actually, I have found that exporting as .csv corrects these minor problems.