11 Replies Latest reply on Nov 19, 2012 1:25 PM by PeterGort

    Getting Database structure using ExecuteSQL

    PeterGort

      For reasons that are too long to go into... I am connecting from Filemaker Pro Advanced to another Filemaker Server via ODBC. (Both are Filemaker 11 at this point).

      I'd like to be able to find out the list of tables and fields that are visible through the DSN, via an SQL query. According to the ODBC Guide "SQLTables" function is a catalog function, but I can't figure out the syntax of the command to get this info. Of course once I have that, I'll need to go through each table for the list of field names and field types. Anybody know how to do this?

        • 1. Re: Getting Database structure using ExecuteSQL
          lhoong

          Peter,

           

          Andrew Duncan has a nice blog post on querying FM schema via SQL.

           

          See here: http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/

           

          Lee.

          =================

          Binary Assist

          220 Gaines Oak Way

          Suwanee, GA 30024

          Voice: (678) 313-5604

          Internet: lhoong@binaryassist.com

          Website: www.binaryassist.com

           

          FileMaker Certified Developer

          • 2. Re: Getting Database structure using ExecuteSQL
            beverly

            Lee, great article, yes but it is about ExecuteSQL (the function). Peter is asking about ODBC query (probably using the Execute SQL script step. The function does not require the ODBC, but does require a Table Occurrence to be on the Relationship Graph of the file where the function is called. It is a new function in FM12.

             

            Peter, using ODBC to see another FMServer, does not automatically put the TOs on the graph. Have you tried that?

             

            FM as an xDBC source can be queried. FM can interact with an ODBC source with the Import ( for SELECT statement ) script step. The Execute SQL script step is for changing data in an ODBC source using INSERT, UPDATE & DELETE SQL statements. These work in FM11 & FM12.

             

            I have not tried the FM looking at another FMS as ODBC source. I don't know if the Import or Execute SQL script steps would be used to get the table information.

             

            Perhaps you can used the design functions to get the information into fields and query from there?

             

            -- sent from my iPhone4 --

            Beverly Voth

            --

            • 3. Re: Getting Database structure using ExecuteSQL
              lhoong

              Beverly,

               

              Thanks for pointing out that the article is about the ExecuteSQL function (vs. the Execute SQL script step).

               

              Peter asked about SQL syntax to obtain FM schema information. I'm just starting to dip my toe into using SQL queries with FM, so apologies if this is a naive question  - shouldn't the syntax be identical since it's directed at the same FM SQL engine? That's what I assumed and why I pointed to Andrew's post.

               

              Also, does it matter, when using the Execute SQL script step, whether the SQL source is FMSA-hosted or non-FM (e.g. mySQL) as long as the correct syntax is used? I wouldn't think so, but I'm not sure…?

               

              Lee.

              =================

              Binary Assist

              220 Gaines Oak Way

              Suwanee, GA 30024

              Voice: (678) 313-5604

              Internet: lhoong@binaryassist.com

              Website: www.binaryassist.com

               

              FileMaker Certified Developer

              • 4. Re: Getting Database structure using ExecuteSQL
                beverly

                Right, Lee! ExecuteSQL( ) <> Execute SQL[ ]. I wanted Peter to be aware and to clarify the question. The article refers to the function and, yes, the syntax may be different for the script step/xDBC. If Peter is not using FM12, then he cannot use the function. There are plugins that may work...

                 

                -- sent from my iPhone4 --

                Beverly Voth

                --

                • 5. Re: Getting Database structure using ExecuteSQL
                  lhoong

                  Thanks for the clarification, Beverly!

                   

                  Lee.

                  =================

                  Binary Assist

                  220 Gaines Oak Way

                  Suwanee, GA 30024

                  Voice: (678) 313-5604

                  Internet: lhoong@binaryassist.com

                  Website: www.binaryassist.com

                   

                  FileMaker Certified Developer

                  • 6. Re: Getting Database structure using ExecuteSQL
                    wimdecorte

                    If you have full control over the solution then you can include an extra table that would serve as the Data Dictionary.  If you're in 12 you can use the ExecuteSQL function to populate it, in 11 or earlier you can use any of the SQL plugins - like the free BaseElements one.  Those can query the internal FileMaker_Tables and FileMaker_Fields schema tables to get the data you want.  Then you can just reference that data dictionary table from outside FM.

                    • 7. Re: Getting Database structure using ExecuteSQL
                      PeterGort

                      Andrew's article does indeed have what I needed.  And a blessing in another way, Andrew was my team leader at Apple Australia Tech support 12 years ago, and I still learn things from him;-)

                       

                      To clarify, I am querying 3 different Filemaker 11 servers via ODBC, and I can use either 11 or 12 to do it.  Because they are Filemaker Servers I cannot use ESS, so I can't make table occurences of them.

                       

                      A couple of years ago we bought a developer license to qFMSQL plugin, so I've been using that, but yes, it should be trivial to move to any of the various plugins that do SQL in Filemaker.  I have to use a plugin because I ALSO need to do SQL UPDATE and SQL INSERT, which Filemaker 12's native functionality does not support.

                       

                      I will be using both Import via ODBC and ExecuteSQL[] script step, and this just gave a big leg up in my task.

                      • 8. Re: Getting Database structure using ExecuteSQL
                        gdurniak

                        it is odd that you have ODBC connections, yet can't define External Data Sources

                         

                        greg

                         

                        > For reasons that are too long to go into

                         

                        > Because they are Filemaker Servers I cannot use ESS, so I can't make table occurences of them

                        • 9. Re: Getting Database structure using ExecuteSQL
                          PeterGort

                          ESS, where you can create table occurences in the relationship graph, only works for certain types of database, specifically, MySql, MSSQL, Oracle.  Filemaker is NOT supported as an ESS data source.  Somebody mentioned to me once that Filemaker tried to do it, but put it in the too-hard basket.... don't know if that's true or not.  I can wish....

                           

                          What I'm building is a tool that watches 3 Filemaker Servers, and maintains "shadows" of the tables, stored fields, and data. WITHOUT making ANY changes to the structure of the data sources (I hope!). I've succeeded in making it mirror the structure, and do table and field checks so if the sources change structure, the shadows will too.  Now I'm working on how to populate the shadows.

                           

                          I have to implement entirely in Filemaker 11, and it has to run as a server side process.  This means the only data interchange methods are ODBC or XML, because Filemaker Server can't import records from another Filemaker database.  One of Filemaker's less bright decisions in my view. but I can use plugins (and I'm using the Base Elements plugin). It's an interesting challenge!

                          • 10. Re: Getting Database structure using ExecuteSQL
                            beverly

                            If you are IN FileMaker 11, then you cannot use the ExecuteSQL() function. I guess I wonder how Andrew's article helps.

                            Beverly

                             

                            I have to implement entirely in Filemaker 11, and it has to run as a server side process.  This means the only data interchange methods are ODBC or XML, because Filemaker Server can't import records from another Filemaker database.  One of Filemaker's less bright decisions in my view. but I can use plugins (and I'm using the Base Elements plugin). It's an interesting challenge!

                             

                            • 11. Re: Getting Database structure using ExecuteSQL
                              PeterGort

                              Using an SQL Plugin, I can do the same query and get the answers of course.  The difference is that using an SQL Plugin, I can do INSERT and UPDATE as well.  So speaking practically, it doesn't matter if I am in 11 or 12.

                               

                              Use an Import via ODBC, I query the structure of the source and get records in my local database.  Using SQL CREATE TABLE and ALTER TABLE ADD COLUMN, I keep my local file in the same structure as the source.  Got that bit working now.