Hi,
The following code is throwing an error (object 'x' not found):
install the package RJDBC.jar if you don't already have it
if(!require('RJDBC')) {
install.packages('RJDBC')
adds the RDJBC functions to the current R session
library('RJDBC')
}
library(DBI)
library(rJava)
tell R where the Filemaker Pro JDBC driver is placed by default
drv <- JDBC("com.filemaker.jdbc.Driver", "/Library/Java/Extensions/fmjdbc.jar", "`")
connect to Filemaker Pro database named "Rcalc".
Rcalc_connect <- dbConnect(drv, "jdbc:filemaker://19x.xxx.xxx.xx:2399/Rcalc", "user", "password")
Name the table inside Rcalc.fmp12
dbListTables(Rcalc_connect)
[1] "calculation"
Correct, table name is "calculation"
dbReadTable(Rcalc_connect, "calculation")
The 'calculation' table is retrieved successfully, The 'answer' field is located properly.
From FileMaker, send the query and id (in this example, '120* 2' and '14') to R (this becomes visible in R Console), and send the result into the "answer" field in FileMaker, using the DBI library. DBI accepts dbGetQuery, dbSendQuery, dbFetch, dbSendStatement
dbGetQuery(Rcalc_connect, paste ("UPDATE calculation SET answer = ", answer, " WHERE id = ", FMPid, sep = ""))
dbGetQuery returns an error: Error: 'answer' not found
dbSendQuery(Rcalc_connect, paste ("UPDATE calculation SET answer = ", answer, " WHERE id = ", FMPid, sep = ""))
dbSendQuery returns an error: Error: 'answer' not found
result <- dbSendStatement(Rcalc_connect, "UPDATE calculation SET answer = ", answer, " WHERE id = ", FMPid, sep = "")
print(result)
dbSendStatement returns an error: Error: 'answer' not found
dbGetQuery(Rcalc_connect, paste ("UPDATE calculation SET answer = ", 120* 2, " WHERE id = ", 14, sep = ""))
Success!
dbDisconnect(Rcalc_connect)
If I replace the field name 'answer' with the calculation '120* 2', and FMPid with '14', the result and id are properly exported to the Rcalc file, find the calculation table, and both results are loaded into their corresponding 'answer' and 'FMPid' fields.
So, why do I see this error? When I add the values manually, it works.
Best,
Daniel