Execute SQL calculations: reality check

[Robert B Carter]

Hi all--

The question I tried to post earlier today seems to have vanished into the ether, maybe because the forum shut down for backup right about the time I hit the "submit" button. So here goes a second try...

This topic was addressed in a previous post (Topic #190513), but I don't think it was ever really resolved, at least not publicly.

I'm trying to write a FMP script to send data from a FileMaker Pro 8.5 table to one in a SQL Server via ODBC. I seem able to connect to the database OK, but I can't seem to figure out the proper syntax for getting the data from my FMP table fields into the execute SQL text. (SQL Query Builder isn't much help, either.)

First question: can anyone tell me how to do this?

Second question: should I be doing this in the first place? I looked at the OBDC/JBDC Developer Guide (http://www.filemaker.com/downloads/documentation/fm8_odbc_jdbc_developer.pdf), and it seems to prefer to use an OBDC Client Driver to pull the data out of the FMP data host and insert it into the target table. It seems like my way would be more direct, but if there's a logical reason why one should pull the data out with a OBDC driver vs. pushing it out with a script, I'm willing to change my strategy here.

Some technical info:

source FMP file name: soa_vrc_vireocat.fp7

source table name: Work

source fields: pk_WorkID, AgentDisplay, MaterialsDisplay

target database name: archtest

target table name: arch.fmp_to_sql

target columns: WorkID, AgentDisplay, MaterialsDisplay

My most recent attempted calc:

Execute SQL [DSN:sql001; SQL Text: use archtest INSERT INTO arch.fmp_to_sql (WorkID, AgentDisplay, MaterialsDisplay) VALUES (" & Work::pk_WorkID & ", " & Work::AgentDisplay & ", " & Work::MaterialsDisplay & ")]

Which gives the error message:

"OBDC Error: [Microsoft][OBDC SQL Server Driver][sql Server] The name '& Work::pk_WorkID &' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not"

Thanks in advance.