Sync between Filemaker Database and SQL Database
|
|
Kruno Celing
|
I'm trying to live sync a Filemaker Database with a SQL database to automatically update a website list. The SQL database contain the same fields as it is in FileMaker database.
I would like to be able to write a script in Filemaker to update the SQL database once the Filemaker database is updated. Could someone please point me in the right direction about how to accomplish this. Thank you in advance for your assistance. |
||||||||||||||||
Ernest Koe
|
You can either do this through ESS (external SQL source) or via a SQL calls
over ODBC. With the former, you would be doing scripted set fields, with the later, it'll probably be scripts that make sql calls. hope this helps On Tue, Jul 7, 2009 at 3:18 PM, Kruno Celing <[hidden email]>wrote: > > I'm trying to live sync a Filemaker Database with a SQL database to > automatically update a website list. The SQL database contain the same > fields as it is in FileMaker database. > I would like to be able to write a script in Filemaker to update the SQL > database once the Filemaker database is updated. Could someone please > point > me in the right direction about how to accomplish this. Thank you in > advance for your assistance. > -- > View this message in context: > http://www.nabble.com/Sync-between-Filemaker-Database-and-SQL-Database-tp24379670p24379670.html > Sent from the FMPExperts mailing list archive at Nabble.com. > > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > -- Ernest Koe The Proof Group LLC _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
Beverly Voth-2
|
Also, you may want to take a look at SyncDek
<http://www.worldsync.com/syncdek/uses/data_integration.html> if you need to truly "sync" and make sure that the data is the same on both sides. What do you do if the SQL changes data and the FMP changes data? Who's the "boss"? You know, that sort of "syncing feeling".... :) If you need one-way push or pull, then ESS or SQL calls are great. Beverly On 7/7/09 3:29 PM, "Ernest Koe" <[hidden email]> wrote in whole or in part: > You can either do this through ESS (external SQL source) or via a SQL calls > over ODBC. > With the former, you would be doing scripted set fields, with the later, > it'll probably be scripts that make sql calls. > > hope this helps > > On Tue, Jul 7, 2009 at 3:18 PM, Kruno Celing > <[hidden email]>wrote: > >> >> I'm trying to live sync a Filemaker Database with a SQL database to >> automatically update a website list. The SQL database contain the same >> fields as it is in FileMaker database. >> I would like to be able to write a script in Filemaker to update the SQL >> database once the Filemaker database is updated. Could someone please >> point >> me in the right direction about how to accomplish this. Thank you in >> advance for your assistance. _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
Kruno Celing
|
In reply to this post
by Ernest Koe
Thank you on your answers
I'm using FileMaker 8.5 Advanced. Is it possible to make SQL call (FM - Oracle) over ODBC? In my case, FileMaker is 'boss' (main database which is in usage for entering data), and SQL is only replication database as source for web reports. I'm concerned for correct syntax which will be used for query that will insert data to SQL. Please, any idea is appreciated.
|
||||
David McQueen
|
Hi Kruno,
I would recommend that you seriously thing of upgrading to FMP 10, or at least 9. Then you will have the ability to bring the SQL database tables as secondary data sources right into FileMaker as FileMaker table occurrences . Then you can then take care of the updating via standard FileMaker scripting. This effectively puts the SQL database right into the user's desktop application. It is really neat and to my view represents the ultimate in the KISS formula. There is nothing like having the real time tables right there. HTH Dave >Thank you on your answers > >I'm using FileMaker 8.5 Advanced. Is it possible to make SQL call (FM - >Oracle) over ODBC? >In my case, FileMaker is 'boss' (main database which is in usage for >entering data), and SQL is only replication database as source for web >reports. > >I'm concerned for correct syntax which will be used for query that will >insert data to SQL. > >Please, any idea is appreciated. > > > > > >Ernest Koe wrote: >> >> You can either do this through ESS (external SQL source) or via a SQL >> calls >> over ODBC. >> With the former, you would be doing scripted set fields, with the later, >> it'll probably be scripts that make sql calls. >> >> hope this helps >> >> On Tue, Jul 7, 2009 at 3:18 PM, Kruno Celing >> <[hidden email]>wrote: >> >>> >>> I'm trying to live sync a Filemaker Database with a SQL database to >>> automatically update a website list. The SQL database contain the same >>> fields as it is in FileMaker database. >>> I would like to be able to write a script in Filemaker to update the SQL >>> database once the Filemaker database is updated. Could someone please >>> point >>> me in the right direction about how to accomplish this. Thank you in >>> advance for your assistance. >>> -- >>> View this message in context: >>> >>>http://www.nabble.com/Sync-between-Filemaker-Database-and-SQL-Database-tp24379670p24379670.html >>> Sent from the FMPExperts mailing list archive at Nabble.com. >>> >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>> >> >> >> >> -- >> Ernest Koe >> The Proof Group LLC >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> > >-- >View this message in context: >http://www.nabble.com/Sync-between-Filemaker-Database-and-SQL-Database-tp24379670p24389826.html >Sent from the FMPExperts mailing list archive at Nabble.com. > >_______________________________________________ >FMPexperts mailing list >[hidden email] >http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au -- David A. McQueen LICHEN Software Barrie, ON, Canada 705-720-9022 705-728-2289 www.lichen-software.com _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
Patrick Neame-2
|
KISS?
On Jul 8, 2009, at 1:53 pm, David McQueen wrote: > Hi Kruno, > > I would recommend that you seriously thing of upgrading to FMP 10, > or at least 9. Then you will have the ability to bring the SQL > database tables as secondary data sources right into FileMaker as > FileMaker table occurrences . Then you can then take care of the > updating via standard FileMaker scripting. This effectively puts the > SQL database right into the user's desktop application. It is > really neat and to my view represents the ultimate in the KISS > formula. There is nothing like having the real time tables right > there. > > HTH > > Dave > >> Thank you on your answers >> >> I'm using FileMaker 8.5 Advanced. Is it possible to make SQL call >> (FM - >> Oracle) over ODBC? >> In my case, FileMaker is 'boss' (main database which is in usage for >> entering data), and SQL is only replication database as source for >> web >> reports. >> >> I'm concerned for correct syntax which will be used for query that >> will >> insert data to SQL. >> >> Please, any idea is appreciated. >> >> >> >> >> >> Ernest Koe wrote: >>> >>> You can either do this through ESS (external SQL source) or via a >>> SQL >>> calls >>> over ODBC. >>> With the former, you would be doing scripted set fields, with the >>> later, >>> it'll probably be scripts that make sql calls. >>> >>> hope this helps >>> >>> On Tue, Jul 7, 2009 at 3:18 PM, Kruno Celing >>> <[hidden email]>wrote: >>> >>>> >>>> I'm trying to live sync a Filemaker Database with a SQL database to >>>> automatically update a website list. The SQL database contain the >>>> same >>>> fields as it is in FileMaker database. >>>> I would like to be able to write a script in Filemaker to update >>>> the SQL >>>> database once the Filemaker database is updated. Could someone >>>> please >>>> point >>>> me in the right direction about how to accomplish this. Thank >>>> you in >>>> advance for your assistance. >>>> -- >>>> View this message in context: >>>> http://www.nabble.com/Sync-between-Filemaker-Database-and-SQL-Database-tp24379670p24379670.html >>>> Sent from the FMPExperts mailing list archive at Nabble.com. >>>> >>>> _______________________________________________ >>>> FMPexperts mailing list >>>> [hidden email] >>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts- >>>> ironclad.net.au >>>> >>> >>> >>> >>> -- >>> Ernest Koe >>> The Proof Group LLC >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>> >>> >> >> -- >> View this message in context: http://www.nabble.com/Sync-between-Filemaker-Database-and-SQL-Database-tp24379670p24389826.html >> Sent from the FMPExperts mailing list archive at Nabble.com. >> >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > > -- > David A. McQueen > LICHEN Software > Barrie, ON, Canada > 705-720-9022 > 705-728-2289 > www.lichen-software.com > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
Damian Kelly
|
Keep It Simple Stupid
Damian On 8 Jul 2009, at 16:46, Patrick Neame wrote: > KISS? > > > On Jul 8, 2009, at 1:53 pm, David McQueen wrote: > >> Hi Kruno, >> >> I would recommend that you seriously thing of upgrading to FMP 10, >> or at least 9. Then you will have the ability to bring the SQL >> database tables as secondary data sources right into FileMaker as >> FileMaker table occurrences . Then you can then take care of the >> updating via standard FileMaker scripting. This effectively puts >> the SQL database right into the user's desktop application. It is >> really neat and to my view represents the ultimate in the KISS >> formula. There is nothing like having the real time tables right >> there. >> >> HTH >> >> Dave >> >>> Thank you on your answers >>> >>> I'm using FileMaker 8.5 Advanced. Is it possible to make SQL call >>> (FM - >>> Oracle) over ODBC? >>> In my case, FileMaker is 'boss' (main database which is in usage for >>> entering data), and SQL is only replication database as source for >>> web >>> reports. >>> >>> I'm concerned for correct syntax which will be used for query that >>> will >>> insert data to SQL. >>> >>> Please, any idea is appreciated. >>> >>> >>> >>> >>> >>> Ernest Koe wrote: >>>> >>>> You can either do this through ESS (external SQL source) or via a >>>> SQL >>>> calls >>>> over ODBC. >>>> With the former, you would be doing scripted set fields, with the >>>> later, >>>> it'll probably be scripts that make sql calls. >>>> >>>> hope this helps >>>> >>>> On Tue, Jul 7, 2009 at 3:18 PM, Kruno Celing >>>> <[hidden email]>wrote: >>>> >>>>> >>>>> I'm trying to live sync a Filemaker Database with a SQL database >>>>> to >>>>> automatically update a website list. The SQL database contain >>>>> the same >>>>> fields as it is in FileMaker database. >>>>> I would like to be able to write a script in Filemaker to update >>>>> the SQL >>>>> database once the Filemaker database is updated. Could someone >>>>> please >>>>> point >>>>> me in the right direction about how to accomplish this. Thank >>>>> you in >>>>> advance for your assistance. >>>>> -- >>>>> View this message in context: >>>>> http://www.nabble.com/Sync-between-Filemaker-Database-and-SQL-Database-tp24379670p24379670.html >>>>> Sent from the FMPExperts mailing list archive at Nabble.com. >>>>> >>>>> _______________________________________________ >>>>> FMPexperts mailing list >>>>> [hidden email] >>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >>>>> >>>> >>>> >>>> >>>> -- >>>> Ernest Koe >>>> The Proof Group LLC >>>> _______________________________________________ >>>> FMPexperts mailing list >>>> [hidden email] >>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts- >>>> ironclad.net.au >>>> >>>> >>> >>> -- >>> View this message in context: http://www.nabble.com/Sync-between-Filemaker-Database-and-SQL-Database-tp24379670p24389826.html >>> Sent from the FMPExperts mailing list archive at Nabble.com. >>> >>> _______________________________________________ >>> FMPexperts mailing list >>> [hidden email] >>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> >> -- >> David A. McQueen >> LICHEN Software >> Barrie, ON, Canada >> 705-720-9022 >> 705-728-2289 >> www.lichen-software.com >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au > > _______________________________________________ > FMPexperts mailing list > [hidden email] > http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
Kruno Celing
|
In reply to this post
by David McQueen
Hi David,
I've just installed FM 9 (Advanced) and try to execute SQL. System message appears (same as with FM 8.5): "ODBC Error: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist" My execute SQL: INSERT INTO Oracle_table_name (Field1, Field2) SELECT FileMaker_table_name.Field1, FileMaker_table_name.Field2 FROM FileMaker_table_name In FM 9 there is properly set External Data Source and relationship which has enabled portal view that shows related records from Oracle (through ODBC). On another database I've tested the same connection to Oracle database with more than 1 mil. records - and everything is working fine! Something is wrong with SQL statement... this statement even doesn't recognize Oracle table. I really don't know what to do. SQL query used in a script is very simple and works in Oracle. Why doesn't here? Please, any suggestion is appreciated.
|
||||||||||||||||
|
Beverly Voth-2
|
Kruno, don't use the SELECT statement to get your FMP records. Rather, use
FMP's find to get the records and create a loop to INSERT each found record. "INSERT INTO oracle_table ( column1, column2 ) VALUES (" & "'" & field1 & "'," & field2 & "'" & ")" This properly quotes the values for SQL, this uses the field *contents* from FMP. Remember that if a field contains a single quote ('), then you need to escape it: Substitute(field1; "'"; "''") -- Beverly Voth Tier3 Data & Web Services Group, LLC 606-258-7399 http://www.tier3web.com/xml.htm Web Design & Hosting * Apple: CAN, ACN & ADC Certified FileMaker 7 & 9 Developer * FileMaker Business Alliance Coldfusion, Witango, PHP, MS SQL, MySQL, FMP, XML/XSLT, CSS Over 15 years experience integrating databases and the internet! On 7/13/09 3:40 PM, "Kruno Celing" <[hidden email]> wrote in whole or in part: > > Hi David, > > I've just installed FM 9 (Advanced) and try to execute SQL. System message > appears (same as with FM 8.5): > "ODBC Error: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist" > > My execute SQL: > INSERT INTO Oracle_table_name (Field1, Field2) > SELECT FileMaker_table_name.Field1, FileMaker_table_name.Field2 > FROM FileMaker_table_name > > In FM 9 there is properly set External Data Source and relationship which > has enabled portal view that shows related records from Oracle (through > ODBC). On another database I've tested the same connection to Oracle > database with more than 1 mil. records - and everything is working fine! > > Something is wrong with SQL statement... this statement even doesn't > recognize Oracle table. I really don't know what to do. SQL query used in a > script is very simple and works in Oracle. Why doesn't here? > > Please, any suggestion is appreciated. > > > > David McQueen wrote: >> >> Hi Kruno, >> >> I would recommend that you seriously thing of upgrading to FMP 10, >> or at least 9. Then you will have the ability to bring the SQL >> database tables as secondary data sources right into FileMaker as >> FileMaker table occurrences . Then you can then take care of the >> updating via standard FileMaker scripting. This effectively puts the >> SQL database right into the user's desktop application. It is really >> neat and to my view represents the ultimate in the KISS formula. >> There is nothing like having the real time tables right there. >> >> HTH >> >> Dave >> >>> Thank you on your answers >>> >>> I'm using FileMaker 8.5 Advanced. Is it possible to make SQL call (FM - >>> Oracle) over ODBC? >>> In my case, FileMaker is 'boss' (main database which is in usage for >>> entering data), and SQL is only replication database as source for web >>> reports. >>> >>> I'm concerned for correct syntax which will be used for query that will >>> insert data to SQL. >>> >>> Please, any idea is appreciated. >>> >>> >>> >>> >>> >>> Ernest Koe wrote: >>>> >>>> You can either do this through ESS (external SQL source) or via a SQL >>>> calls >>>> over ODBC. >>>> With the former, you would be doing scripted set fields, with the >>>> later, >>>> it'll probably be scripts that make sql calls. >>>> >>>> hope this helps >>>> >>>> On Tue, Jul 7, 2009 at 3:18 PM, Kruno Celing >>>> <[hidden email]>wrote: >>>> >>>>> >>>>> I'm trying to live sync a Filemaker Database with a SQL database to >>>>> automatically update a website list. The SQL database contain the same >>>>> fields as it is in FileMaker database. >>>>> I would like to be able to write a script in Filemaker to update the >>>>> SQL >>>>> database once the Filemaker database is updated. Could someone please >>>>> point >>>>> me in the right direction about how to accomplish this. Thank you in >>>>> advance for your assistance. >>>>> -- _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
|
Kruno Celing
|
Thanks for answer
I'm sorry, but I'm little confused here. What is proper syntax for describing FileMaker fields that I wish to upload to SQL (Oracle)? Where to use single, where double quotes? After word VALUES there are several instances where is single quote inserted in double quotes. Does it means that every FileMaker field in SQL statement must have before and after field name those syntax used: & "'" &? And generally, is it only way to do with loop script (one-by-one records)? My final goal is to update (daily) SQL table with all of FileMaker data (all records).
|
||||||||||||||||
|
Beverly Voth-2
|
Kruno, The syntax I gave you is the proper way. You make a calculated "SQL
statement" and include the fields as you would for any other calculation. I did not show the tableOccurance, but it certainly would be included in the calculation. All data needs to be in a single quote for INSERT (or UPDATE) per SQL 'rules'. All integers can omit the single quote, but it doesn't hurt to include them (as I've found). Any single quote within the field needs to be escaped, as I showed. The substitute takes care of that. I tend to make two calculated fields (one for INSERT and one for UPDATE), these are used in the EXECUTE SQL step, with a loop to call for each record. Yes, you can: INSERT (with loop) or you can use ESS and import directly into the SQL. - again look back at the answers to this thread (and others) about what will import and where. Beverly On 7/13/09 5:28 PM, "Kruno Celing" <[hidden email]> wrote in whole or in part: > > Thanks for answer > > I'm sorry, but I'm little confused here. What is proper syntax for > describing FileMaker fields that I wish to upload to SQL (Oracle)? Where to > use single, where double quotes? > > After word VALUES there are several instances where is single quote inserted > in double quotes. Does it means that every FileMaker field in SQL statement > must have before and after field name those syntax used: & "'" &? > > And generally, is it only way to do with loop script (one-by-one records)? > My final goal is to update (daily) SQL table with all of FileMaker data (all > records). > > > > Beverly Voth-2 wrote: >> >> Kruno, don't use the SELECT statement to get your FMP records. Rather, use >> FMP's find to get the records and create a loop to INSERT each found >> record. >> >> "INSERT INTO oracle_table ( column1, column2 ) >> VALUES (" >> & "'" & field1 & "'," & field2 & "'" >> & ")" >> >> This properly quotes the values for SQL, this uses the field *contents* >> from >> FMP. >> >> Remember that if a field contains a single quote ('), then you need to >> escape it: >> >> Substitute(field1; "'"; "''") >> >> -- >> Beverly Voth Tier3 Data & Web Services Group, LLC >> 606-258-7399 http://www.tier3web.com/xml.htm >> >> Web Design & Hosting * Apple: CAN, ACN & ADC >> Certified FileMaker 7 & 9 Developer * FileMaker Business Alliance >> Coldfusion, Witango, PHP, MS SQL, MySQL, FMP, XML/XSLT, CSS >> Over 15 years experience integrating databases and the internet! >> >> >> On 7/13/09 3:40 PM, "Kruno Celing" <[hidden email]> wrote in >> whole or in part: >> >>> >>> Hi David, >>> >>> I've just installed FM 9 (Advanced) and try to execute SQL. System >>> message >>> appears (same as with FM 8.5): >>> "ODBC Error: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist" >>> >>> My execute SQL: >>> INSERT INTO Oracle_table_name (Field1, Field2) >>> SELECT FileMaker_table_name.Field1, FileMaker_table_name.Field2 >>> FROM FileMaker_table_name >>> >>> In FM 9 there is properly set External Data Source and relationship which >>> has enabled portal view that shows related records from Oracle (through >>> ODBC). On another database I've tested the same connection to Oracle >>> database with more than 1 mil. records - and everything is working fine! >>> >>> Something is wrong with SQL statement... this statement even doesn't >>> recognize Oracle table. I really don't know what to do. SQL query used in >>> a >>> script is very simple and works in Oracle. Why doesn't here? >>> >>> Please, any suggestion is appreciated. >>> >>> >>> >>> David McQueen wrote: >>>> >>>> Hi Kruno, >>>> >>>> I would recommend that you seriously thing of upgrading to FMP 10, >>>> or at least 9. Then you will have the ability to bring the SQL >>>> database tables as secondary data sources right into FileMaker as >>>> FileMaker table occurrences . Then you can then take care of the >>>> updating via standard FileMaker scripting. This effectively puts the >>>> SQL database right into the user's desktop application. It is really >>>> neat and to my view represents the ultimate in the KISS formula. >>>> There is nothing like having the real time tables right there. >>>> >>>> HTH >>>> >>>> Dave >>>> >>>>> Thank you on your answers >>>>> >>>>> I'm using FileMaker 8.5 Advanced. Is it possible to make SQL call (FM - >>>>> Oracle) over ODBC? >>>>> In my case, FileMaker is 'boss' (main database which is in usage for >>>>> entering data), and SQL is only replication database as source for web >>>>> reports. >>>>> >>>>> I'm concerned for correct syntax which will be used for query that will >>>>> insert data to SQL. >>>>> >>>>> Please, any idea is appreciated. >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> Ernest Koe wrote: >>>>>> >>>>>> You can either do this through ESS (external SQL source) or via a SQL >>>>>> calls >>>>>> over ODBC. >>>>>> With the former, you would be doing scripted set fields, with the >>>>>> later, >>>>>> it'll probably be scripts that make sql calls. >>>>>> >>>>>> hope this helps >>>>>> >>>>>> On Tue, Jul 7, 2009 at 3:18 PM, Kruno Celing >>>>>> <[hidden email]>wrote: >>>>>> >>>>>>> >>>>>>> I'm trying to live sync a Filemaker Database with a SQL database to >>>>>>> automatically update a website list. The SQL database contain the >>>>>>> same >>>>>>> fields as it is in FileMaker database. >>>>>>> I would like to be able to write a script in Filemaker to update the >>>>>>> SQL >>>>>>> database once the Filemaker database is updated. Could someone >>>>>>> please >>>>>>> point >>>>>>> me in the right direction about how to accomplish this. Thank you >>>>>>> in >>>>>>> advance for your assistance. >>>>>>> -- >> >> >> _______________________________________________ >> FMPexperts mailing list >> [hidden email] >> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au >> >> _______________________________________________ FMPexperts mailing list [hidden email] http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au |
||||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
