title: Unable to save an R data frame to filemaker Pro database using SQL and ODBC connection
body: <p>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.</p>
  
  <p>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).</p>
  
  <p>I have therefore imported the 3 Filemaker tables Patient, Treatment, Pathology using:</p>
  
  <pre><code>con = odbcConnect("LC", uid = "uid", pwd = "pwd")
  PatSurg &lt;-sqlQuery(con, paste("SELECT * FROM Patient P INNER JOIN Treatment T ON P.PatientID = T.PatientID"))
  PatPath &lt;-sqlQuery(con, paste("SELECT * FROM Patient P INNER JOIN Pathology H ON P.PatientID = H.PatientID"))
  Full &lt;- merge(PatSurg, PatPath, by = intersect(names(PatSurg), names(PatPath)))
  View(Full)
  </code></pre>
  
  <p>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.</p>
  
  <p>I then thought I'd try the simple instruction:</p>
  
  <pre><code>sqlSave(con, Full, tablename = "Thoracic")
  </code></pre>
  
  <p>and was returned the error:</p>
  
  <blockquote>
    <p>Error in sqlSave(con, Full, tablename = "Thoracic") :    table
    ‘Thoracic’ already exists</p>
  </blockquote>
  
  <p>Undeterred, I dropped the table name and re-saved it:</p>
  
  <pre><code>sqlDrop(con, "Thoracic", errors = FALSE)
  sqlSave(con, Full, tablename = "Thoracic")
  </code></pre>
  
  <p>which returned a new error:</p>
  
  <blockquote>
    <p>Error in sqlSave(con, Full, tablename = "Thoracic") :    [RODBC]
    Failed exec in Update HY000 502 [FileMaker][FileMaker]  (502): Field
    failed numeric value validation test</p>
  </blockquote>
  
  <p>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:</p>
  
  <pre><code>sqlQuery(con, "INSERT INTO Thoracic ", Full)
  sqlQuery(con, "INSERT INTO Thoracic VALUES", Full)
  </code></pre>
  
  <p>Both returned:</p>
  
  <blockquote>
    <p>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</p>
  </blockquote>
  
  <pre><code>sqlAppendTable(con, "Thoracic", Full)
  </code></pre>
  
  <p>Returned:</p>
  
  <blockquote>
    <p>Error in (function (classes, fdef, mtable)  :    unable to find an
    inherited method for function ‘sqlAppendTable’ for signature ‘"RODBC"’</p>
  </blockquote>
  
  <pre><code>sqlQuery(con, "INSERT INTO Thoracic VALUES Full")
  </code></pre>
  
  <p>Returned:</p>
  
  <blockquote>
    <p>[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'"</p>
  </blockquote>
  
  <p>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!</p>