14 Replies Latest reply on Jun 1, 2017 8:09 AM by fmpdude

    User-defined sql function on a layout

    mainframe

      What is the best practice that a user can define/execute a 'user defined SQL' and the result gets written to a flat file or a table for export later on?

       

      e.g.

      SELECT *  FROM CUST WHERE  _NAME LIKE 'BEN'

       

      I am not an SQL expert but wanna open the doors so a user can define his/her own SQLs and the output  records gets at the end to a text file ...

        • 1. Re: User-defined sql function on a layout
          beverly

          Presumptions are that the users can write a query correctly (know the name of the tables/fields) and that it will return an acceptable result.

          Start here for the function ExecuteSQL():

          The "sqlQuery" part can be written into a field by your users and then you must push the values into the function to evaluate. You must also handle the possibilities for errors.

           

          See how I did this (get the pdf and sample files):

          Beverly

          • 2. Re: User-defined sql function on a layout
            mainframe

            Thanks beverly. Those who wanna use the function I try to implement will know SQLs ...

            What I wanna provide is a table where a user can store all his SQLs for retrieval and execution:

             

            e.g. he/she enters only :

            SELECT NAME FROM \"RAK.SEK0200\" WHERE ACTION='NO' ORDER BY NAME

             

            instead of

            "SELECT NAME FROM \"RAK.SEK0200\" WHERE ACTION='NO' ORDER BY NAME"  ;";";"¶"

             

            as A result of this, the found values should then be exported to text file ... for further processing...

             

            My issue is to to execute the SQL:

            set variabel $SQL = value  "ExecuteSQL (" &

            Char(34) &

            SQL.GENERATE.REPOSITORY::SQL_STORED &

            Char(34) &

            "   ;" &

            Char(34) & ";" & Char(34) &

            ";" & Char(34) & Char(182) & Char(34)

            &")"

             

            Using set variable $EXEC_SQL; value $SQL does not work. In the hard coded format it does.

             

            I am not even sure if that is possible at all the way it is coded.

             

             

             

            • 3. Re: User-defined sql function on a layout
              beverly

              Yes! Did you download my example file? That is basically how I use the queries (stored in a field). Then you are the one scripting the call to execute. I store the entire query with a Let() function, but there's no reason that you cannot just have the query part in the field and call.

               

              You might also check out:

              A handy way to write the queries without the user knowing SQL!

               

              You need to study the ExecuteSQL help. You don't directly export to a FILE. What you get is a TEXT result to save in a field or variable. With FM16, you can export this to a file:

              With these caveats:

               

              You might also study the method to "parse" to a virtual list/table which should be able to export:

              and

               

              You may have your Set Variable (script step) incorrect:

              And perhaps study the Let() function for setting the variable (as I did in my examples).

               

              Beverly

              1 of 1 people found this helpful
              • 4. Re: User-defined sql function on a layout
                beverly

                Try this:

                Set Variable [ $SQL ; ExecuteSQL (

                SQL.GENERATE.REPOSITORY::SQL_STORED ; "" ; "" )

                // change the delimiters as needed ]

                Of course I would trap for the error:

                Set Variable [ $SQL ;

                    Let (

                        [ ~query = SQL.GENERATE.REPOSITORY::SQL_STORED

                        ; ~result = ExecuteSQL ( ~query ; "" ; "" )

                        ; ~catchError = If ( ~result = "?" ; "" ; ~result )

                            /* alternative error capture: */

                            // ; ~catchError = If ( ~result = "?" ; EvaluationError(~result) )

                        ]; ~catchError

                    )

                ]

                Beverly

                 

                p.s. by the way your naming of the table "SQL.GENERATE.REPOSITORY" can lead to problems down the road. I use alphanumerics and underscore wherever a "name" is used in FileMaker.

                • 5. Re: User-defined sql function on a layout
                  mainframe

                  Many thanks.

                   

                  Your suggestion:  without using Evaluate(SQL.GENERATE.REPOSITORY::SQL_STORED ) as well it simply does not work.

                   

                  In addition I do not want that the user has to put quotes on the input field around the SELECT... this silly code below does do the trick though there must be  a smarter way...

                  Evaluate(

                  "ExecuteSQL (" &

                  Char(34) &

                  SQL.GENERATE.REPOSITORY::SQL_STORED &

                  Char(34) &

                  "                ;" &

                  Char(34) & ";" & Char(34) &

                  ";" & Char(34) & Char(182) & Char(34)

                  &")"

                  )

                   

                  What surprised me with another SQL I tried, that suddenly  FM starts with summarising fields in a table which even are not referenced ...  in FM12 and FM15

                   

                  Naming of the table "SQL.GENERATE.REPOSITORY": all my tables are like this because I do FOLDER import. the data comes from another platform and has naming conventions which must be equal.

                  All of it worked great for years so far.

                  • 6. Re: User-defined sql function on a layout
                    beverly

                    Do not quote. Do not use Evaluate(). Use the field as the query. What error do you get? Did you try the second with error capture? Did you look at the seed code for an easy way to generate eSQL?

                     

                     

                     

                    Sent from miPhone

                    • 7. Re: User-defined sql function on a layout
                      mainframe

                      if the SELECT is not in quotes I get a '?'

                      My silly coding I mentioned works ...

                      With your solution it works only when I specify on the input field the quotes for the select which I do not want.

                       

                      yes I checked it ... great stuff ... thanks

                      • 8. Re: User-defined sql function on a layout
                        beverly

                        I don't see exactly what you are doing and perhaps you are trying to be 'obscure'. You have not posted any error codes/messages, just that you got "?". If my advice so far has not helped, I cannot see what more I may suggest.

                        Beverly

                        1 of 1 people found this helpful
                        • 9. Re: User-defined sql function on a layout
                          mainframe

                          Thanks beverly ... I did what u suggested ...

                           

                          the obscure stuff is the table name in the select which must be in quotes

                          SELECT MED_NAME FROM

                          "DECK.PARL.MED"  WHERE MED_SPECIAL='YES' ORDER BY MED_NAME

                           

                          versus what I defined which worked fine byusing evaluate

                           

                          SELECT MED_NAME FROM

                          \"DECK.PARL.MED\"  WHERE MED_SPECIAL='YES' ORDER BY MED_NAME

                          • 10. Re: User-defined sql function on a layout
                            fmpdude

                            As a minor aside, to avoid getting the maddening "?" in a SQL statement, I understand that version 16 gives real SQL error messages now, finally (hurray, only took 5 new FMP versions to get SQL error messages -- after ExecuteSQL introduced in v. 12.).

                             

                            Or, you can use an external tool like RazorSQL or even a programmer's IDE with a data tab. Razor is nice in that it's inexpensive and works not only with FMP but any JDBC-enabled database (that is, ALL of them).

                             

                            Since I am not upgrading to 16 I use an external tool that connects to FMP to prototype SQL statements (yes, against the live FMP database) before moving them into FMP.

                             

                            HOPE THIS HELPS.

                            • 11. Re: User-defined sql function on a layout
                              beverly

                              Then you have found the reason many use the SQL Explorer. You could create your own interface using the same principles that it does. Because of the name of the table (with dots), it does require being quoted in the query. If you place the query in a field, then the quotes (you must train your users to include them) will be escaped for you.

                               

                              Evaluate() is still not needed if the query is in a field and the script uses the function ExecuteSQL(). The function can take the field "as is" and use it, thus 'evaluating' for you.

                               

                              TheSQLquery (text field) for user entry (contains):

                              SELECT MED_NAME

                              FROM "DECK.PARL.MED" 

                              WHERE MED_SPECIAL='YES'

                              ORDER BY MED_NAME

                              (note whitespace, including returns are allowed and will be ignored by the function)

                               

                              The Script:

                              ==========

                              Set Field ( TheSQLresult ; ExecuteSQL ( TheSQLquery ; "" ; "" ) )

                               

                              Or use the Let() syntax earlier posted to trap for error.

                               

                              Beverly

                              1 of 1 people found this helpful
                              • 12. Re: User-defined sql function on a layout
                                mainframe

                                thanks.

                                I do not use any external solutions because of security issues .... some do a 'call-home' to check license code  ... others are not always  upwards compatible ...

                                 

                                I have FM12 and FM15 ... I dislike FM15 like hell since they changed e.g. the script layout to enter code.

                                 

                                I would have preferred FM would have integrated TCPIP (dating now for 15 years) so I can talk to an IBM mainframe or even MQ Series from IBM.  I know there are 3rd party tools for it.

                                • 13. Re: User-defined sql function on a layout
                                  beverly

                                  I merely suggested looking at the SQL Explorer, so you can see how they did what they did. It may be helpful to you to do something similar without integrating into your system.

                                  Beverly

                                  1 of 1 people found this helpful
                                  • 14. Re: User-defined sql function on a layout
                                    fmpdude

                                    That's cool, but you should know that FMI crippled the JDBC driver so it only works on the same machine anyway.

                                     

                                    And, if you're not using a firewall where you can block any connection attempts anyway, that's a huge security problem. There are some programs -- using the firewall software -- I simply block from "phoning home". Others I don't worry about.

                                     

                                    And, you're using a VPN for all communications too, right?

                                     

                                    And, you're encrypting your hard drive too, right?

                                     

                                    Doing off-site backups?

                                     

                                    etc.....

                                     

                                    ---

                                     

                                    So, in my case, I have good security measures but then am totally comfortable augmenting the heck out of FMP so it's truly a powerhouse.

                                     

                                     

                                    Good luck.