7 Replies Latest reply on Jan 24, 2012 9:07 AM by LSNOVER

    Ability to run .sql files?

    naustghoul

      Is it possible to import them or am I just supposed to create the sql script in MYSQL save that, go over to filemaker open pre-script, have it 'Execute SQL[ ]' with a copy of the sql text I just scripted and saved to my database folder? Is there anyway to just point the 'Execute SQL[ ]' to the script.sql file so I only have to update the one point of refernce?

        • 1. Re: Ability to run .sql files?
          beverly

          Typically, my advice is to save your data as .csv (comma-separated values). This file would have the field names and the data. You can use this to open or create tables in FileMaker Pro.

           

          The answer is NO! you cannot read .sql files for FileMaker.

           

          You would have to go back to the table just created with import and set things like text or number (if FMP cannot guess the type) and validation. All those good things that are set up in MySQL tables need to be re-created in FileMaker.

           

          The "Execute SQL[]" script step would allow you (if you have ODBC connection to the SQL), perform these on your SQL database (if you have the permission to do so):

               INSERT

               UPDATE

               DELETE

           

          The "Import[]" script step has the ability to import data from SQL, if you have the ODBC setup and have the permission. You can use SELECT to narrow the data to import.

           

          Neither of these steps are a direct connection to the SQL database and changes made in one, do not automagically appear in the other (FM & SQL). You must perform the steps to "sync" your data.

           

          The above two steps are NOT the same as ESS (External SQL Sources). With ESS, you have a "viewport" of sorts into the SQL tables. You can interact with the SQL tables as long as you have a connection to the datasource. You cannot, however, manage the SQL tables from FileMaker (you cannot create columns, for example).

           

          I suggest you read the xDBC guide and other technical publications on ESS (found in this forum).

           

          Beverly

          • 2. Re: Ability to run .sql files?
            naustghoul

            Some of what you said was salient but other points not so pertinent. I have a ODBC connection to a database and have my FileMaker interface setup with the managed external datasource that is the MySQL database. I would not want to EVER make changes to my dataabse structure through FileMaker EVER period. What I want to do is create my SQL code in MySQL Workbench, save that out as a .sql file and use that file instead of just copying and pasting it into any 'Execute SQL[ ]' hopper in FileMaker. You say that

             

            "The answer is NO! you cannot read .sql files for FileMaker."

             

            But you give no actual reason why, just an explinaiton about how to setup a table. I don't want to EVER (again) change my tables with FileMaker. I want to update data IN the tables.

            • 3. Re: Ability to run .sql files?
              beverly

              So, you are using ESS (is there a layout showing the SQL table and data)?

               

              If you are, you do not need to make SQL calls. Just use FileMaker finds and sorts (like SELECT). You can also create (like INSERT), edit (like UPDATE) or delete (DELETE) records in the SQL table through the ESS interface. You do not make SQL calls to do this. You manually (or through scripts) do what you want to do as you would in FileMaker tables. You do not use Execute SQL[] script step to interact with the ESS data.

               

              NO, you cannot create the SQL in MySQL Workbench and expect it to work as expected inside FileMaker. You cannot READ a .sql file period, for any reason, in FileMaker.

               

              Because FileMaker is not MySQL. I cannot read .sql from MS SQL in MySQL or the other way and they are both "SQL" databases. Does that explain "why" well enough?

               

              Again, I urge you to read the documentation on this.

               

              Beverly

              • 4. Re: Ability to run .sql files?
                beverly

                try this link for the Documentation you need https://fmdev.filemaker.com/docs/DOC-1117

                the "techbrief_ess_fm11_en.pdf" is full of goodies that might help you understand what you need to do.

                 

                Beverly

                • 5. Re: Ability to run .sql files?
                  LSNOVER

                  Naustghoul:

                   

                  As Beverly pointed out, you can not read the .sql files into Filemaker directly.  You can use the MySQL Workbench to assemble SQL commands then cut and paste them into the Execute SQL commands parameter field.    Unfortunately, you will also have to go in and edit that SQL to get Quotes and delimiters all properly parsed out so that it will execute properly from the Filemaker command.  There are many limitations, as Filemaker will not handle data binding, it doesn't really deal well with statements that bring back data, etc.  It is a good way to execute Stored Procedures and the like from within Filemaker, if you don't mind putting ODBC drivers on every client machine. 

                   

                  Cheers!

                  Lee

                  • 6. Re: Ability to run .sql files?
                    beverly

                    Pasting the SQL into an Execute SQL[] script step may NOT work as desired, as Lee pointed out that it must be "parsed out". So just use the FileMaker dialogs to set up the SQL calls.

                     

                    Remember that these do NOT need to be made if you are using ESS! Simply use the standard FileMaker methods to enter, edit or delete data in the External SQL Source!

                     

                    Beverly

                    • 7. Re: Ability to run .sql files?
                      LSNOVER

                      Beverly, correct.  I'm just saying you can use the tool to help get the basic syntax right and know that it works.

                       

                      Once you paste it into Filemaker, the biggest problem is that the parameter for Execute SQL is treated as a large string that has to be quoted, so you have to parse out your Quote delimiters, commas and such to get Filemaker to swallow the entire string without mis-interpreting anything actually embedded in the string.

                       

                      Also, as Beverly has alluded to, ESS and Execute SQL are COMPLETELY INDEPENDENT of one another. The two features do not overlap or interact in any way.  In a single client environment, they CAN use the same DSN/ODBC setup.  In a Filemaker Server environment, ESS will use the Server's DSN/ODBC setup while Execute SQL MUST use the workstation DSN/ODBC setup.