1 2 Previous Next 18 Replies Latest reply on Feb 23, 2010 3:32 PM by mrvodka

    FM 10-Exporting .tab file to SQL server

    BCATLYMCA

      Title

      FM 10-Exporting .tab file to SQL server

      Post

      We have a set of scripts that import and sync information to and from an SQL server. 

       

      The import aspect works great!  However, when it creates a .tab file and then tries to upload/export that file out to our SQL server it doesn't send the information back out (sync).

       

      We are confused as to why it is communicating correctly on the "import" side of the equation but is not communicating as it tries to "export" the info back out?

       

      Any help would be appreciated!

       

      Thanks!

        • 1. Re: FM 10-Exporting .tab file to SQL server
          philmodjunk
             Please describe the steps you are taking to export your data and what, if any results you get. (There's more than one way to do this and more than one way it might not work the way you expect.)
          • 2. Re: FM 10-Exporting .tab file to SQL server
            BCATLYMCA
              

            Thanks Phil.... We believe we have it narrowed down to one place in the script definitions.  Here are the script comments as they are in the computer:

             

            ---------------

            Allow User Abort [On]

            Perform Script ["Open Connection"]

            Set Field [FM Mirror Requests::tSQLStatus; External ("SQL-executeFile"; "C:\Sync Files\UpdateRequests.tab")]

            Perform Script ["Close Connection"]

            ---------------

             

            We are also getting a "?" in one of the fields that is in the database, but we haven't been able to figure out how that calculates into the equation or get it corrected.

             

            Thanks for all of your help!

            • 3. Re: FM 10-Exporting .tab file to SQL server
              mrvodka
                 What plugin are you using?
              • 4. Re: FM 10-Exporting .tab file to SQL server
                BCATLYMCA
                  

                I am checking on the plugin... unfortunately I'm on a network where I don't have full access to the control panel and other administrative areas in the computer.  I have a coworker in the IT dept at our Metro office who is checking that for me.  He is working very directly with me on the project so his "access" and my knowledge of Filemaker is what we're counting on.  I'll let you know what we find out about the plugin.

                 

                A question, would the plugin make a difference in the connection only working one direction? In other words, our database is importing the information correctly from the website, it's just not sending the information correctly back out to sync the website after our internal manipulation.  Would the plugin be the cause of that?

                 

                Also, how complicated is it to create a new script to send a .tab file out to our SQL server?  We are considering just trying to totally rebuild the process?

                 

                Thanks!

                • 5. Re: FM 10-Exporting .tab file to SQL server
                  mrvodka
                    

                  From what you have posted, it seems as though you are using a plugin. There may be an issue with the plugin. Just check under Edit --> Preferences --> Plugins

                   

                   

                  • 6. Re: FM 10-Exporting .tab file to SQL server
                    BCATLYMCA
                      

                    Thanks! 

                     

                    Ok, on our old version (version 6.0) of Filemaker that we are upgrading from, to Filemaker 10... we were using the following Plug-Ins:

                     

                    Dacons Mail-it 2.5

                    JPlug-In

                    Local Data Access Companion

                    Server Administration

                    SQL Plug-In

                     

                    It appears we were using the PDM JPlug-In Version 2 for the communication that we're speaking of...

                     

                    At this point, on our new Filemaker 10, we have not installed any plug-in...but we have access to do that. 

                     

                    Is it possible that could be creating our issue of it only working 1 direction?

                     

                    Thanks so much for all of your help!

                    • 7. Re: FM 10-Exporting .tab file to SQL server
                      mrvodka
                        

                      Well yes, your script uses the old pre 7 way to call plugin functions. It will not work in your new version. You will have to update your plugins as well as change it to the new Function equivalent of your old one (External ("SQL-executeFile" ).

                       

                       

                      FMP7 changes the architecture for plugins and now has a new API.

                      • 8. Re: FM 10-Exporting .tab file to SQL server
                        BCATLYMCA
                          

                        Thank you, working on the plugin aspect with my IT guy now... we see several articles with information about them.  We are connecting to a Microsoft SQL server... do you have experience with a certain plugin that may work well for that, to import information from the SQL server and also send it back out after being synced in the office?

                         

                        I will work on the equivalent command to the "SQL-executeFile" command"... do you know of a Forum where I might be able to find that information directly?

                         

                        Thanks again for your help!

                        • 9. Re: FM 10-Exporting .tab file to SQL server
                          mrvodka
                            

                          I think you have may have misunderstood. That is the external call to the SQL plugin.

                           

                          Perhaps you shold re-evaluate and modernize your current system. Much has changed since the FM6 days. More specifically, starting with FM9 it has a capability to see live data to external sources such as MS SQL. You may not need to upload tab files to sync up the data.

                           

                          If you could explain what your needs are and how you intend to use SQL / FMP then perhaps I can recommend some things.  

                           

                           

                           

                           

                          • 10. Re: FM 10-Exporting .tab file to SQL server
                            BCATLYMCA
                              

                            Thanks, I think I did misunderstand, sorry! 

                             

                            Unfortunately, modernizing the system is going to be a tough sell because we are 1 branch of 26 in our YMCA... and financially and timewise that would be very tough.

                             

                            Let me explain what we are trying to do... and ask a question or two as I go. 

                             

                            We take website registrations (through SQL server) on our website for our Summer Camp.  We allow parents to put in 3 registration "requests" that we then import into our system in-house.  We then manipulate the data in-house and "accept" 1 registration and "void" the other two.  At that point, we send that information back out to the website (through SQL server) so it lets the parents know which registration was accepted.

                             

                            In our old system, we are doing that through a succession of scripts that create a .tab file after we make the manipulation in the office and then export that .tab file out to the SQL server, which then executes an "update" command on the affiliated records and displays that on the website.

                             

                            Hopefully that makes at least a little bit of sense... sorry for any confusion.

                            • 11. Re: FM 10-Exporting .tab file to SQL server
                              BCATLYMCA
                                

                              Left out one thing...

                               

                              My only worry with the "live data" situation with an external source that Filemaker is now capable of would be how it would "sync" the information.  If it "gets" the information live from the SQL server... how would we go about manipulating the data and then sending it back out for the parents to see?

                               

                              Also, my IT guy has found quite a few SQL plugins... if we were to go that route... do all SQL plugins do the same thing, or are they for different things?  Is there a certain one that you would suggest?

                               

                              Thanks!

                              • 12. Re: FM 10-Exporting .tab file to SQL server
                                mrvodka
                                  

                                EAch of the SQL plugins handle things slightly different.

                                 

                                From what you have posted, my recommendation is to forget the plugin and just use the live data. If something on the submitted form has to be changed, then you can just modify it through a layout in FileMaker based on the SQL shadow table. No plugins. No tab file. No syncing.

                                 

                                You can also use the interface to pick and choose which form gets approved.

                                • 13. Re: FM 10-Exporting .tab file to SQL server
                                  BCATLYMCA
                                    

                                  Ok... I'll look into that. 

                                   

                                  So, to make sure I understand correctly... I would need to setup the SQL server as an External Data Source and then how would I go about the steps of matching up the data with what is in our existing database?  (Is that the shadow table you're speaking of?) 

                                   

                                  It sounds like a totally different thought process...so I'm trying to make sure I'm up to speed with what you're saying, I apologize if this sounds elementary. 

                                   

                                  So, in a bare sense... this would allow us to "read" the live data from the SQL server... and then when we make changes in house... it would basically be changing them on the SQL server, at the same time.... is that correct?  So the parents would see it as soon as the changes were made, if they were to login to their acct. on the website?

                                   

                                  Thanks!

                                  • 14. Re: FM 10-Exporting .tab file to SQL server
                                    mrvodka
                                       Yes. As soon as the record is commited, it sends an update SQL statement in the background to the External table.
                                    1 2 Previous Next