1 2 Previous Next 17 Replies Latest reply on Jul 1, 2017 11:27 AM by gofmp15

    SQL Java JSON etc.

    gofmp15

      Do these techniques require third party software to be installed next to FIlemaker?

       

      For instance, what happens if I remove all traces of Javascript from my computer?

       

      If so, what is required to make these puppies bark?

        • 1. Re: SQL Java JSON etc.
          wimdecorte

          JSON handling is backed into FM and does not depend on anything outside of FM.  JSON is not a system component; it is a standard of data representation.

           

          SQL is a language, again a standard, not a component.  What use of it are you asking about, ExecuteSQL() calls?  Those are just native to FM and don't depend on anything.  "Execute SQL" script step depends on a DSN and ODBC driver to be present in the proper OS location.

          • 2. Re: SQL Java JSON etc.
            gofmp15

            Thanks;

            ""Execute SQL" script step depends on a DSN and ODBC driver to be present in the proper OS location." This is what I was curious about.

             

            I have almost decided to replace my 'grinder' script that changes the language of 500 $$Variables to one the uses Execute SQL which saves a dozen script steps but I am concerned about it not working if the user doesn't have something installed.

             

            Looks like for safeties sake I will use the old 'grinder'.

             

             

            • 3. Re: SQL Java JSON etc.
              wimdecorte

              I think you are confusing ExecuteSQL() as a function and "Execute SQL" as a script step.  The later is for pushing data into another system.

               

              ExecuteSQL() is strictly internal to FM and does not depend on anything outside of FM.

               

              It's been around since FM12, so it's ok to use it by now...

              • 4. Re: SQL Java JSON etc.
                gofmp15

                Thanks again and ditto on my quote...

                • 5. Re: SQL Java JSON etc.
                  bigtom

                  gofmp15 wrote:

                   

                  Thanks;

                  ""Execute SQL" script step depends on a DSN and ODBC driver to be present in the proper OS location." This is what I was curious about.

                   

                  I have almost decided to replace my 'grinder' script that changes the language of 500 $$Variables to one the uses Execute SQL which saves a dozen script steps but I am concerned about it not working if the user doesn't have something installed.

                   

                  Looks like for safeties sake I will use the old 'grinder'.

                   

                   

                  From what I understand FM runs ExecuteSQL [the function] queries by having an engine that converts SQL syntax into an internal Find request. From that perspective it is 100% FM and that is also why not all SQL operations are supported. ExecuteSQL is often a core function of many of the common things done in FM by many developers. Virtual lists is one of them.

                  • 6. Re: SQL Java JSON etc.
                    bigtom

                    I think you also might be confusing Java (JRE) and Javascript.

                    • 7. Re: SQL Java JSON etc.
                      gofmp15

                      bigtom wrote:

                       

                      I think you also might be confusing Java (JRE) and Javascript.

                      It wouldn't be the first time nor would it be the last. For simplicity I just say Java...

                      • 8. Re: SQL Java JSON etc.
                        gofmp15

                        bigtom wrote:

                         

                        gofmp15 wrote:

                         

                        Thanks;

                        ""Execute SQL" script step depends on a DSN and ODBC driver to be present in the proper OS location." This is what I was curious about.

                         

                        I have almost decided to replace my 'grinder' script that changes the language of 500 $$Variables to one the uses Execute SQL which saves a dozen script steps but I am concerned about it not working if the user doesn't have something installed.

                         

                        Looks like for safeties sake I will use the old 'grinder'.

                         

                         

                        From what I understand FM runs ExecuteSQL [the function] queries by having an engine that converts SQL syntax into an internal Find request. From that perspective it is 100% FM and that is also why not all SQL operations are supported. ExecuteSQL is often a core function of many of the common things done in FM by many developers. Virtual lists is one of them.

                        My life was simpler before you pestered me with your sql thingy that I am now using exclusively in my app with sufficient changes to avoid copyright infringement...

                        • 9. Re: SQL Java JSON etc.
                          bigtom

                          gofmp15 wrote:

                           

                          My life was simpler before you pestered me with your sql thingy that I am now using exclusively in my app with sufficient changes to avoid copyright infringement...

                          I honestly figured you were already using ExecuteSQL since you said you were not needing to change layouts to process the variable changes. My assumption was you were grabbing a list of key values and looping through them to reset the variable, which is a step I made along the way.

                           

                          I just wanted to help you skip the loop script. However it seems you were doing this via some other "grinding" method I did not anticipate.

                           

                          If you are using my method exclusively it would be great to get a copy to see how you implemented it.

                          • 10. Re: SQL Java JSON etc.
                            gofmp15

                            bigtom wrote:

                             

                            gofmp15 wrote:

                             

                            My life was simpler before you pestered me with your sql thingy that I am now using exclusively in my app with sufficient changes to avoid copyright infringement...

                            I honestly figured you were already using ExecuteSQL since you said you were not needing to change layouts to process the variable changes. My assumption was you were grabbing a list of key values and looping through them to reset the variable, which is a step I made along the way.

                             

                            I just wanted to help you skip the loop script. However it seems you were doing this via some other "grinding" method I did not anticipate.

                             

                            If you are using my method exclusively it would be great to get a copy to see how you implemented it.

                            Sorry, I have copyrighted the file...

                            I am using one table for the names of the languages and one for the translations. I use another file to do the heavy work of getting the translations from Google and delete all the records and import the new data. I reference your line of sql on occassion when changing language but I have changed the field names and fisual formating so as to not violate any copyright of yours.

                             

                            Instead of using your 'hidden' technique, I use a script to do the work. Nothing very complex. I hit a few bumps when I deleted my tables and scripts and had to revert to a backup and start over.

                             

                            The sql thing seems to run a bit faster and I think the files being interpreted has a tendency to slow things down a bit. 4D which is also an interpeted data/script file really runs faster when that file is compiled.

                            • 11. Re: SQL Java JSON etc.
                              bigtom

                              The hidden calculation was a bit of a joke, but you seem to think it was an integral part of the system.

                               

                              Small credit for the eSQL calc is good enough for me. Will I see you at DevCon?

                              • 12. Re: SQL Java JSON etc.
                                bigtom

                                Many developers at the forefront of FM development are all using eSQL, JS and JSON frequently. I often feel like I am still catching up. If you are not using these features the only ones missing out on the benefits are your clients.

                                • 13. Re: SQL Java JSON etc.
                                  gofmp15

                                  I know that it was a joke and I appreciate it as it let me hone my detective skills. I spent some time wondering how in the world you made it happen since there was no obvious way that it should. Then... aha!

                                   

                                  The step you created is now part of a script with refresh that I call when needed.

                                   

                                  Set global field to 2 letter code

                                  do your step

                                  refresh

                                   

                                  So simple... Thanks.

                                   

                                  Now how about one that creates a var from a field in a table, only contains one unique value with no repetition and is sorted. This is kinda valuable. It bypasses having to use the value list thingy.

                                  • 14. Re: SQL Java JSON etc.
                                    wimdecorte

                                    gofmp15 wrote:

                                     

                                    Now how about one that creates a var from a field in a table, only contains one unique value with no repetition and is sorted. This is kinda valuable. It bypasses having to use the value list thingy.

                                     

                                    To build the list itself, use the Summary field of type 'List Of' or use one of many other techniques described in the excellent HyperList v2 write-up and speed comparison.

                                     

                                    To make it unique and sort:

                                    In 16 use the two new functions: UniqueValues() and SortValues(). 

                                    In 15 and earlier use one of the many custom functions that people have built that do this.

                                    1 2 Previous Next