13 Replies Latest reply on Oct 2, 2015 2:22 PM by beverly

    Displaying/Storing ExecuteSQL Select

    rustyshackleford

      How do I take the result of a "ExecuteSQL Select statement" and display it in show custom dialog or store the results into a table in Filemaker?

        • 1. Re: Displaying/Storing ExecuteSQL Select
          erolst

          rustyshackleford wrote:

          display it in show custom dialog or store the results into a table in Filemaker?

           

          In Show Custom Dialog[], you (must) specify the title and the message as a calculation; in Set Field[], you (must) specify the result to set as a calculation;

           

          and ExecuteSQL() is a function that you use in a calculation

           

          Is that what you wanted to know? (Also, there is the Auto-Enter option of "Calculated value” … )

          • 2. Re: Displaying/Storing ExecuteSQL Select
            Mike_Mitchell

            Oliver (erolst) is correct. The ExecuteSQL function returns essentially a block of text, delimited as you choose when you specify the calculation. One common way to delimit the result is to use carriage returns to separate rows. Should you choose to do so, you can use the various functions dealing with return delimited lists (GetValue, LeftValues, etc.) to extract one or more values and insert them into rows in a table.

             

            Another way is to use the Virtual List technique, which will create table records out of a return-delimited variable, from which you can import the results into a table.

             

            Does that help?

            • 3. Re: Displaying/Storing ExecuteSQL Select
              rustyshackleford

              So if I understand you correctly, I should make a calculated field with executeSQL and then refer to the field when i show custom dialog?

              • 4. Re: Displaying/Storing ExecuteSQL Select
                rustyshackleford

                So I could "GetAsText" the "ExecuteSQL" statement within a calculated field on a table? Then, I could set that field equal to a variable, and then, I could parse that field with "Position", "Left", and "Right"?

                • 5. Re: Displaying/Storing ExecuteSQL Select
                  erolst

                  You were asking two different questions, and got two different answers.

                   

                  If you're asking specifically about Show Custom Dialog (SCD), be aware that when you need the result to be displayed only during the runtime of your script, there is no need to store it anywhere (or, more precisely, create a dedicated field for it; that would usually be an unstored calculation).

                   

                  You can do

                   

                  Set Variable [ $mySQLResult ; sqlQuery ]

                  Show Custom Dialog [ title: someTitle; message: $mySQLResult ]

                   

                  or simply

                   

                  Show Custom Dialog [ title: someTitle; message: sqlQuery ]


                  The actual form would depend on whether you will need the result of the query elsewhere, or if you change context after calculating, but before performing SCD, or if you want to use the Data Viewer to inspect the script while debugging etc.

                  • 6. Re: Displaying/Storing ExecuteSQL Select
                    Mike_Mitchell

                    No, you're making it too complicated. Just use a Set Variable script step and set the variable equal to the result of the ExecuteSQL. Then you can parse it any way you want, including using Set Field script steps to shove bits of it into fields or new records, or having it separated out automatically using the Virtual List.

                    • 7. Re: Displaying/Storing ExecuteSQL Select
                      rustyshackleford

                      erolst I tried to show custom dialog in both of those fashions. Both results returned a "?" for me. Here's my executeSQL statement: ("Select * From Bill";",";"¶"). I get no error message when running Get(LastODBCError), and 0 for (Get(LastError)), and I know my connection is good because I've tested it, and the connection is currently working in QODBC's VB Demo 32

                      • 8. Re: Displaying/Storing ExecuteSQL Select
                        beverly

                        Set Variable can also be used. Result is text.

                         

                        There is a good example of ExecuteSQL and placing the resulting values into a virtual list table. This can be found in the FileMaker Training Series.

                         

                        -- sent from myPhone --

                        Beverly Voth

                        --

                        • 9. Re: Displaying/Storing ExecuteSQL Select
                          Nehme

                          You can download this excellent file:

                          http://www.seedcode.com/filemaker-sql-explorer/

                           

                          It is very helpful for learning sql

                          and it parses the results in a table => you can follow the steps

                          • 10. Re: Displaying/Storing ExecuteSQL Select
                            erolst

                            Right – so you were asking about the script step Execute SQL[] (note the blank in the name), about which I declare myself entirely ignorant – except that, again AFAIK, this step is used to set values in a data source, not query them …?!

                             

                            I'm sure Mike and Beverly can provide you with all the advice you need.

                            • 11. Re: Displaying/Storing ExecuteSQL Select
                              rustyshackleford

                              I was under the impression that up until FM 12, you could only "select" with "executeSQL"

                              • 12. Re: Displaying/Storing ExecuteSQL Select
                                erolst

                                If you mean the ExecuteSQL() function – that was only introduced with FM12, and supported (and as of v14 still supports) only the SELECT statement.

                                 

                                As for the Execute SQL[] script step: as mentioned, I couldn't tell.

                                • 13. Re: Displaying/Storing ExecuteSQL Select
                                  beverly

                                  The Execute SQL script step to an external SQL DB is used for the INSERT, UPDATE or DELETE actions on those external records/rows. Use the IMPORT script step to bring in the records for you query. Note you need to specify a table (create one in the step if needed). These two script steps are not "live connect" to the SQL db. Any actions are independant of  what is shown in FMP and in the SQL tables.

                                   

                                  If you wish to have "live connect" so that what you see is what you get in both, then use ESS (External SQL Sources). You place a TO (table occurence) to the SQL table on the Relationship Graph (RG). Then it appears and works with most of the FM functions and methods (find, create, delete, edit) as if a native FMP table.

                                   

                                  I suggest you research the HELP for all of these terms.

                                   

                                  Yes, IF the records are "IN" FMP (via import or ESS), then you can use the ExecuteSQL() FUNCTION on those records. Note: the result of this query is still a TEXT result and can be placed in a field or variable. It is NOT put into a table as with the IMPORT. You must still post-process.

                                   

                                  Again you need to research: Execute SQL (script step), Import (script step), ESS (External SQL Sources), ExecuteSQL() - the function. Many of these have been covered well in this forum (and others) as well.

                                   

                                  beverly

                                  SQL DBA