14 Replies Latest reply on May 10, 2016 3:19 AM by sudha

    Execute SQL

    sudha

      Hi all,

       

      Is it is possible to export the entire set of columns in a table using Execute SQL function.

       

      Thank you.

        • 1. Re: Execute SQL
          beverly

          please clarify:

          1. ExecuteSQL (function)?

          or

          2. Execute SQL (script step)?

          3. define 'entire set of columns in a table': all fields & rows in a database table?

          4. what did you have in mind to post-process the result, if any?

           

          Remember that the ExecuteSQL() function returns the result in a Text field (or variable). You must define the column and row separators.

          beverly

          • 2. Re: Execute SQL
            user19752

            It may be

            ExecuteSQL ( "

            SELECT *

            FROM aTable

            " ; "" ; "" )

            with some WHERE if not want all records.

            But the result is text in field or variable as beverly mentioned.

            When exporting it from a field, you may meet problem of UTF-16 encoding or converted line breaks.

            We need more spefication for what you want.

             

            Do you want field names, not field data?

            • 3. Re: Execute SQL
              monkeybreadsoftware

              or maybe like this?

               

              Set Variable [$sql; Value:"SELECT \"Item\", \"Model\", \"Serial Number\" FROM \"Assets\""]

              Set Variable [$text; Value:MBS( "FM.ExecuteSQL"; $sql; 9; 13 )]

              Set Variable [$r; Value:MBS( "Text.WriteTextFile"; $text; "/tmp/test.txt"; "UTF-8" )]

               

              the plugin writes the text to the file in UTF-8. We requested 9 (tab)  and 13 (mac line ending) for the delimiters.

              • 4. Re: Execute SQL
                beverly

                I'm wondering if perhaps, OP wants to CREATE a new TABLE using SQL (depending on functions and/or script steps or ??). We need a bit more info.

                There are definitely SQL plugins that are tailored to that functionality, if needed.

                beverly

                • 5. Re: Execute SQL
                  sudha

                  Hi all,

                  Thanks for the reply.

                  Currently, I have been extracting the entire records in each of the table in the filemaker database file (.fp7) using Export Records (manual option)

                   

                  Can you please clarify me whether this exporting records can be achieved using any function in Filemaker version 14 instead of manual export records option?

                   

                  Thank you.

                  • 6. Re: Execute SQL

                    Options as I see them:

                     

                    1. Use the Export options available in scripts?

                     

                     

                    2. You might be able to use ExecuteSQL to copy fields to a variable and then export that variable to a file. I haven't tried that. Container fields would take extra care, of course.

                     

                    ---

                     

                    3. Coming in to the database from an external program, you could use the excellent FREE JDBC driver FM has, in a relatively simple Java program that:

                     

                    a. Gets the DB Metadata so you can programmatically determine the table and field names. Of course, you could just hard-wire all the SQL in the JDBC code too and forget about the DB Metadata if you have total access to the FM database. However, then that Java JDBC code s not portable (generic) if that's a concern.

                     

                    b. Once you create the JDBC ResultSets (like a FM FoundSet), simply extract the data and write it to disk files (container files take an extra step, but not difficult) or to another DB or to whatever.

                     

                    I've done this a lot.

                     

                    -----

                     

                    4. Use a Plug-in.

                     

                    HOPE THIS HELPS.

                     

                    - m

                    • 7. Re: Execute SQL
                      sudha

                      Hi morkus,

                       

                      Thank you for your reply.

                       

                      We have tried using ODBC driver FM to extract the records from the tables.

                       

                      However, the calculation fields are consuming more time to export. Does upgrading of database file from .fp7 to .fp12 would resolve the export of calculation fields consuming time issue?

                       

                      Thanks

                      • 8. Re: Execute SQL
                        beverly

                        Well, calculation fields can consume more time (to display, to print, to export) if they are complex (many dependancies, perhaps).

                         

                        What format are you using for the export? .csv or ??

                        Exporting as FileMaker will convert the calculations into plain fields and export from there into .csv would be faster. But it is unknown by us how much time would be used for the initial export.

                         

                        Perhaps you can find a smaller set (say 100 records) and test compare various methods?

                        beverly

                        • 9. Re: Execute SQL
                          sudha

                          I have been using .csv/excel format.

                          Below are the methods I have tried in Filemaker Pro 11:

                          1. File -> Export Records Option

                          2. Script step -> Export Records

                          3. Using ODBC driver configuration, developed a .NET tool to extract the records from Filemaker using SQL queries.

                          All the above mentioned steps shows the same time consuming issue for exporting the calculation fields.

                           

                          The only left out method is to try the Execute SQL in Filemaker Pro 14. The client is suggesting to upgrade the file from .fp7 to .fp14 to see if there is any improvement in exporting process. But my thoughts are:

                           

                          1. As we are doing data migration, the entire file format would be changed which doesn't sounds good for me.

                          2. If we also Execute SQL once after upgrading, the calculation field would definitely take more time because when we tried using the ODBC driver configuration, there we used the same SQL queries which also shows the issue.

                          The calculation involved here has dependency and due to it's complexity, there is no such solution to provide.

                          Please let me know is there any best approach to be followed to sort this issue out.

                           

                          Thanks

                          • 10. Re: Execute SQL
                            beverly

                            ExecuteSQL() would NOT help.

                            Export as FileMaker into a new db and see if you have any difference. Your calculation fields would be converted to regular fields and may facilitate the ODBC to extract from there. As I said, test with a smaller set of records.

                            beverly

                            • 11. Re: Execute SQL
                              sudha

                              How to export the filemaker into new db ?

                              Choosing the layout -> File-> Export Records-> Save as (name) type: Filemaker database file(.fp7)

                              Please correct me if I am wrong.

                              Thanks

                              • 12. Re: Execute SQL

                                I didn't see where you stated your time constraints.

                                 

                                I used JDBC, not ODBC, for database tramsfers (a sync program) between FIleMaker and MySQL. It was quite fast even with container fields.

                                 

                                If you have lots of data, it will take more time.

                                 

                                You'll need to do some bench-marking I guess.

                                 

                                - m

                                • 14. Re: Execute SQL
                                  sudha

                                  Thank you