11 Replies Latest reply on Mar 20, 2017 11:27 AM by alecgregory

    EXECUTESQL() and I'm stumped

    aknudsen

      Trying to declare an SQL statement with a variable, but I just can't get it to work. Using FM 15.

       

      LET(

      publicaddress = $$publicaddress;

       

      ExecuteSQL ("select DISTINCT app, dport, protocol FROM pathfinder

                             WHERE dstloc IS NOT NULL

                             AND fromzone = 'ae_ext_in'

                             AND tozone = 'ae_ext_out'

                             AND src = ?

                            ORDER BY app";

      "";"";

      "";"";

      publicaddress

      )

      )

       

      If I replace the AND src = 'xx.xx.xx.xx' it works just as it should. I've also tried public address = "'" & $$publicaddress & "'" with no luck. The $$publucaddress value is text and I've also tried GetAsText() with the same result.

       

      This can't be that difficult

        • 1. Re: EXECUTESQL() and I'm stumped
          alecgregory

          It looks like you have too many parameters (5 instead of 3): The following should work:

           

          Let (

               publicaddress = $$publicaddress;

                    ExecuteSQL (

                    "SELECT

                         DISTINCT app,

                         dport,
                         protocol

                    FROM

                         pathfinder

                    WHERE

                         dstloc IS NOT NULL

                    AND

                         fromzone = 'ae_ext_in'

                    AND

                         tozone = 'ae_ext_out'

                    AND

                         src = ?

                    ORDER BY

                         app";

                    "";

                    "";

                    publicaddress

               )

          )

           

          In any case the Let is redundant, though I guess you may have just been using it for troubleshooting.

           

          ExecuteSQL (

               "SELECT

                    DISTINCT app,

                    dport,
                    protocol

                FROM

                    pathfinder

                WHERE

                    dstloc IS NOT NULL

                AND

                    fromzone = 'ae_ext_in'

                AND

                    tozone = 'ae_ext_out'

                AND

                    src = ?

                ORDER BY

                    app";

                "";

                "";

                $$publicaddress

          )

           

          For consistency it's probably best to use substitution throughout:

           

          ExecuteSQL (

               "SELECT

                    DISTINCT app,

                    dport,
                    protocol

               FROM

                    pathfinder

               WHERE

                    dstloc IS NOT NULL

               AND

                    fromzone = ?

               AND

                    tozone = ?

               AND

                    src = ?

               ORDER BY

                    app";

               "";

               "";

               "ae_ext_in";

               "ae_ext_out";

               $$publicaddress

          )

          • 2. Re: EXECUTESQL() and I'm stumped
            aknudsen

            Thanks for the help, but still doesn't work. I've been through a few various ways of doing it. In all your samples, if I substitute the src = ? with the real IP address which is what the global variable holds, it works.

             

            Yes I'm stumped on this

            • 3. Re: EXECUTESQL() and I'm stumped
              Jason Wood

              I was a bit confused at first by alecgregory's answer because he says "you have too many parameters (5 instead of 3)"

               

              When in fact you have 6 instead of 4. Or you could also say, you have 3 "arguments" instead of 1.

               

              The reason you're not getting a parse error is because multiple arguments are permitted. The problem is that the first (and only) question mark refers to the first argument, which in this case is "".

              • 4. Re: EXECUTESQL() and I'm stumped
                alecgregory

                Quite right. 6 instead of 4! I would be surprised if my final suggestion didn't work, can you just confirm you tried the below exactly?

                 

                ExecuteSQL (

                     "SELECT

                          DISTINCT app,

                          dport,
                          protocol

                     FROM

                          pathfinder

                     WHERE

                          dstloc IS NOT NULL

                     AND

                          fromzone = ?

                     AND

                          tozone = ?

                     AND

                          src = ?

                     ORDER BY

                          app";

                     "";

                     "";

                     "ae_ext_in";

                     "ae_ext_out";

                     $$publicaddress

                )

                • 5. Re: EXECUTESQL() and I'm stumped
                  aknudsen

                  Thanks, looking into it, but still no luck

                  • 6. Re: EXECUTESQL() and I'm stumped
                    aknudsen

                    Still no go, but if I use the IP address in the src field, no problem. The global variable does have the same value.Screen Shot 2017-03-20 at 13.44.25.pngScreen Shot 2017-03-20 at 13.44.38.png

                    • 7. Re: EXECUTESQL() and I'm stumped
                      alecgregory

                      Back into troubleshooting mode. Try the below to see if the $$publicaddress global variable is being seen by your calculation. What output does it produce?

                      Let (

                           [

                                ~queryResult =

                                     ExecuteSQL (

                                          "SELECT

                                               DISTINCT app,

                                               dport,

                                               protocol

                                          FROM

                                               pathfinder

                                          WHERE

                                               dstloc IS NOT NULL

                                          AND

                                               fromzone = ?

                                          AND

                                               tozone = ?

                                          AND

                                               src = ?

                                          ORDER BY

                                               app";

                                               "";

                                               "";

                                               "ae_ext_in";

                                               "ae_ext_out";

                                               $$publicaddress

                                     )

                           ];

                           List (

                                ~queryResult;

                                $$publicaddress

                           )

                      )

                      • 8. Re: EXECUTESQL() and I'm stumped
                        aknudsen

                        The output is blank, but the global variable is set. Tried this in both the script and the expression editor. Also tried FM 14 and 15. Same result. I really appreciate your help though.

                        • 9. Re: EXECUTESQL() and I'm stumped
                          alecgregory

                          There must be an issue with the global variable otherwise it would show up in that calculation. It may be as simple as a typo in the global variable name. To confirm see if this is also blank in the data viewer:

                          $$publicaddress

                          • 10. Re: EXECUTESQL() and I'm stumped
                            aknudsen

                            That's embarrassing. Must be the first p(l)ublicaddress I've seen. Time for a nap, my eyes stopped working as did the brain. Yes, it works and yes I truly appreciate your help. Like a second set of eyes .... remotely

                            • 11. Re: EXECUTESQL() and I'm stumped
                              alecgregory

                              No worries, we've all been there! Happy to help.