3 Replies Latest reply on Nov 19, 2013 11:31 AM by philmodjunk

    ExecuteSQL

    laudmusic

      Title

      ExecuteSQL

      Post

           Pardon my ignorance as this is the first time I'm delving into ExecuteSQL.

           Do I need to do anything special, install the ODBC drivers and or set up an external datasource in order to use ExecuteSQL on my FM solution?

           When I just try to run ExecuteSQL with a statement that I know works, I get an error saying I need to setup ODBC.

           Thanks in advance,

           Michael

        • 1. Re: ExecuteSQL
          philmodjunk

               It depends on whether you should be using the function or the script step of that name.

               The ExecuteSQL() function is something that you can use in a calculation to query your own FileMaker files.

               The script step is for querying ODBC and JDBC data sources and thus you need to set your file up for access to such a data source before there is any point in using that script step. There's a PDF you can open from FileMaker help | Product documentation called the ODBC JDBC guide where you can learn more about those methods of accessing data in another database. The SELECT query examples in chapter 7 are also valid examples of SELECT queries that you can use with the ExecuteSQL() function.

          • 2. Re: ExecuteSQL
            laudmusic

                 OK. So to confirm: If I want to use ExecuteSQL in a script to pull records into a layout, I need to set up FileMaker (the current and solution I want to query) as an ODBC source? Seems circuitous as I'm just wanted to query my file. 

                 Thanks,

                 M

            • 3. Re: ExecuteSQL
              philmodjunk

                   Well you could, I think, but why go to such trouble?

                     
              1.           You can perform a find to get those records and not use any SQL at all.
              2.      
              3.           You can also use ExecuteSQL() in a calculation to pull up a list of primary keys. You can then use a relationship matching that executeSQL field to the same table (and same primary key field) with Go To Related Records or a portal to see the group of related records.
              4.      
              5.           And you can also list all the data in a large field formatted with tab stops (if you use Char (9) as your field separator and ¶ as your record separator), to look much like a portal for displaying the matching data.


                   If you are new to FileMaker, be advised that FileMaker is not set up to use SQL as the main tool for querying data in FileMaker tables. It's possible, as I have outlined in 2 and 3 above, but ExecuteSQL() is a newly added feature and not the usual method used...

                   I believe that there is also a plug in that can be used to make a SQL query of your data possible so you might web search for that to see what's out there.