Why maintain both? What about removing the FM database, and just have it access the SQL one?
We're doing basically the same thing, and must use a Mac OS X 'robot' running FMP, and an Automator script. Could also use cron and an applescript, if you're on a Mac. Not sure about 'bots running Windows.
I'm on Windows platform (XP Professional) and I'm using FileMaker 8.5 Advanced. Sync is neccessary only in one direction: FM to SQL (Oracle). My existing Query (scripted in FM script as Execute SQL command) is using ODBC connection that is in function - at least, Windows recognize connection and Oracle database send correct response that is linked with ODBC driver.
Execute SQL command has value:
INSERT INTO 'Oracle_table_name' ('Field1', 'Field2')
SELECT 'FileMaker_table_name'.'Field1', 'FileMaker_table_name'.'Field2'
Script is not executed with following message:
ODBC Error: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist
Try removing the single quotes around the table and field names. With FM8.5, you'll need to setup a calculated SQL command, based on the records in the current found set. You can't use 'SELECT' on the FM records. Example:
Go to Record [First]
Set Variable: [$sql] = $sql & "'" & FileMaker_table_name::Field1 & "','" & FileMaker_table_name::Field2 & "'"
Go to Record [Next, exit after last]
Set Variable: [$sql] = $sql & ","
Execute SQL: Calculated = "INSERT INTO Oracle_table_name (Field1,Field2) (" & $sql & ")"
My Insert SQL is a bit rusty, but I think the syntax is 90% correct
Thanks you on answer.Single quotes in my example was only for purpose of marking a field names in this message - in a script there were no single quotes.
Please I don't understand why SELECT is not possible for usage? Is there some special syntax that is necessary to use for SQL query when it is written from FileMaker?
And also I don't understand why is not possible to move all data to Oracle and only use loop that will do it 'one-by-one' (as you explained in your post). Inserting in SQL (Insert into) is something very basic operation. FileMaker declares compatibility with Oracle, but without this command (Insert into) - it is meaningless compatibility.
What is purpose that I can only 'see' data from Oracle (through portal row in FileMaker 9), if I can't work with this data (use or update)?
Executing my SQL script I have screen message:
"ODBC Error: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist"
This message describe main problem - FileMaker can't EVEN RECOGNIZE any table in Oracle, although in the same time I can see all of Oracle data in FileMaker (External Data Source properly setup, Oracle table added as table in FileMaker and joined in relationship).
Please, if somebody can help.
The issue is not the INSERT INTO. It is the SELECT because FileMaker does not understand what you mean by SELECTING the FileMaker records.
Try doing a single insert such as:
INSERT INTO Oracle_table_name (Field1,Field2) VALUES ('Mickey', 'Mouse')
If that works, then try doing a scripted version:
Set Variable [ $sq; "'"] //single quote
Execute SQL [ "INSERT INTO Oracle_table_name (Field1,Field2) VALUES (" & $sq & field1 & $sq & "," & $sq & field2 & $sq & ")" // where field1 and field2 are the FileMaker fields
If that works, then you can just loop through your FileMaker found set.
Another option is considering that you are using ESS, you could just use Set Field.
When you're using Execute SQL within Filemaker, you can't use SQL to talk to the FM databases. Most of what FM does is at the per record level, except for imports/exports. And while it's easy to import from ODBC, there's no method for exporting directly to the data source. Guess that's why they call them sources, instead of destinations.
With that said, ESS is a little different, because it allows you to see, and update multiple records in a table directly based on an ODBC data source. But you can't use SQL commands with ESS tables, FM takes care of the SQL for you.