9 Replies Latest reply on Jan 27, 2016 6:40 AM by mjp

    ExecuteSQL query against external ODBC-Table

    mjp

      Hi there,

       

      i want to execute a simple SQL query against a table (docs) which is in external data source linked via ODBC. Is that possible? When I do:

       

      ExecuteSQL ( "select max(created_at) from docs" ; ";"; "")


      The return value is just a question mark?

       

      The other way to figure out the maximum - sorting by created_at asc and getting the last value - is quite slow due to more than a million records. And I also think using the Filemaker Max-Function with a self-referencing is slow, too.

       

      Any hints?

       

      Thanks very much,

      Markus

        • 1. Re: ExecuteSQL query against external ODBC-Table
          bob_ellis

          Have you tried wrapping created_at in quotes? I'm thinking the underscore might be causing the ? result. There is also a nifty little custom function that can be used to troubleshoot sql queries in Filemaker: http://www.fmfunctions.com/fid/335

          • 2. Re: ExecuteSQL query against external ODBC-Table
            beverly

            While a handy-dandy custom function, note the "this technique only works in the dataviewer". I'll add that if you put EvaluationError() around the ExecuteSQL(), then you'll get a code for the error. No need to be in Data Viewer.

             

            On Sep 4, 2015, at 6:47 AM, bob_ellis

             

             

            ExecuteSQL query against external ODBC-Table

            reply from bob_ellis in Discussions - View the full discussion

            Have you tried wrapping created_at in quotes? I'm thinking the underscore might be causing the ? result. There is also a nifty little custom function that can be used to troubleshoot sql queries in Filemaker: http://www.fmfunctions.com/fid/335

             

            • 3. Re: ExecuteSQL query against external ODBC-Table
              bob_ellis

              I was not aware of that, thank you Beverly!

              • 4. Re: ExecuteSQL query against external ODBC-Table
                coherentkris

                ExecuteSQL ( "select max(created_at) from docs" ; ";"; ")

                 

                max() returns one value.. Why bother specifying a field separator?

                ExecuteSQL ( "select max(created_at) from docs" ; ""; "") will suffice

                • 5. Re: ExecuteSQL query against external ODBC-Table
                  beverly

                  out of habit? for consistency? or because one of the helper query builders out there created the actual code? or why does it matter if there's a field separator or not just because there's only one value returned?

                   

                   

                  On Sep 4, 2015, at 7:35 AM, coherentkris

                   

                  max() returns one value.. Why bother specifying a field separator?

                   

                  • 6. Re: ExecuteSQL query against external ODBC-Table
                    mjp

                    Thanks for your message and that sql debug hint. It works now:

                     

                    ExecuteSQL ( "SELECT max(\"created_at\") from docs  " ; "" ; "" )

                     

                    (I don't mind the separators).

                     

                    But it's even more slow. On the mysql-Host the following queries are executed:

                    SELECT id FROM bla.docs WHERE id>4515 ORDER BY id

                    ....

                    SELECT id FROM bla.docs WHERE id>4566 ORDER BY id

                    ....

                    SELECT id FROM bla.docs WHERE id>4591 ORDER BY id

                    ....

                    ....

                    Obviously the max() SQL-Function isn't used but all records are fetched and the maximum value is calculated afterwards!? That's grossly inefficient and therefore useless for me. It's a pity!

                    • 7. Re: ExecuteSQL query against external ODBC-Table
                      bob_ellis

                      If you have access to the ODBC source and using a view, it may be more efficient to calculate the max created_at within the view itself. Though I'm not sure what the result is being used for so it may not be applicable.

                      • 8. Re: ExecuteSQL query against external ODBC-Table
                        beverly

                        something else that might help:

                        IF you have access to the SQL (external) and can create a view which performs this query, then you can get the value probably* in a more efficient fashion than using ExecuteSQL() on the ESS. This 'pre-fetches' what you need.

                         

                        • probably is based on too many variables here.

                         

                        Or use the Import script step to make a SQL query (just as you have it in the SQL db natively). As you are returning one value it can be into a global field. This might also be slow, but does not use the ExecuteSQL() function.

                         

                        beverly

                         

                        On Sep 4, 2015, at 9:23 AM, mjp <noreply@filemaker.com> wrote

                         

                         

                        ExecuteSQL query against external ODBC-Table

                        reply from mjp in Discussions - View the full discussion

                        Thanks for your message and that sql debug hint. It works now:

                         

                        ExecuteSQL ( "SELECT max(\"created_at\") from docs  " ; "" ; "" )

                         

                        (I don't mind the separators).

                         

                        But it's even more slow. On the mysql-Host the following queries are executed:

                        SELECT id FROM bla.docs WHERE id>4515 ORDER BY id

                        ....

                        SELECT id FROM bla.docs WHERE id>4566 ORDER BY id

                        ....

                        SELECT id FROM bla.docs WHERE id>4591 ORDER BY id

                        ....

                        ....

                        Obviously the max() SQL-Function isn't used but all records are fetched and the maximum value is calculated afterwards!? That's grossly inefficient and therefore useless for me. It's a pity!

                        Reply to this message by replying to this email, or go to the message on FileMaker Community

                        Start a new discussion in Discussions by email or at FileMaker Community

                        Following Discussions in these streams: Connections Stream

                        Following ExecuteSQL query against external ODBC-Table in these streams: Inbox

                        Manage your email preferences

                         

                        FileMaker Developer Conference 2016 • Las Vegas, Nevada • July 18-21 • www.filemaker.com/devcon

                         

                        • 9. Re: ExecuteSQL query against external ODBC-Table
                          mjp

                          Thanks, MrWatson.

                           

                          Deleting is not an option for me because I want to keep the solution as clean as possible (I've got 10k+ fields and I'm quite sure 10% are not needed anymore).

                           

                          But the upgrade to FM14 is a very good hint, so I will reschedule the upgrade.

                           

                          Thanks,

                          Markus