6 Replies Latest reply on Jun 10, 2016 2:27 PM by JohnGagne

    Execute SQL and WEB Direct

    JohnGagne

      Execute SQL is incredible.  Love it.

       

      But it is not working in my solution when executed from WEB Direct.

       

      Works perfectly when I run it from FileMaker Advanced; but, same line fails in WEBD.

       

      Hate seeing "?"

       

      Any thoughts?

       

      Thanks John Gagne

        • 1. Re: Execute SQL and WEB Direct
          beverly

          first John, clarify:

           

          Execute SQL - script step?

          or

          ExecuteSQL() - function?

           

          second question: how are you using?

          beverly

          • 2. Re: Execute SQL and WEB Direct
            coherentkris

            check the web direct user permission set to make sure the database objects you are selecting are accessible

            • 3. Re: Execute SQL and WEB Direct
              JohnGagne

              Good question.  I forgot there were two forms.  I'm using the FUNCTION.  In a SET FIELD script step, I include the following calculation:

               

              ExecuteSQL ( "SELECT b.\"p_unit_ID\"

              FROM \"z_resources\" a

              INNER JOIN \"UNIT\" b ON a.\"g_web_account\" = b.\"account\" AND a.\"g_web_password\" = b.\"password\" AND a.\"g_web_security_code\" = b.\"Security_Code\"" ; " " ; " " )

               

              Three global fields each contain credential information.  If all three match, then b.p_unit_ID is returned.  The ID for the matching record.

               

              When I run from FileMaker Advanced, it is successful.  When I execute from WEB Direct, it fails.  Even when I run the WEBD from a full-access administrator account.  So, access is not an issue.  I put in CUSTOM DIALOG script steps along the way, displaying info for my debugging because I'm not sure if script debugger would work while in WEBD.  So, I know INPUT fields are up to speed, as is ACCESS.  And the execution fails.  I think the calculation worked in MONITOR/DATA VIEWER too.

               

              I'm running the FileMaker Community test server 15 on a MACBOOK PRO, which is up to speed.  Browser was Safari.

              Thank you for your time and support.

               

              John Gagne

              • 4. Re: Execute SQL and WEB Direct
                JohnGagne

                When I run from FileMaker Advanced, it is successful.  When I execute from WEB Direct, it fails.  Even when I run the WEBD from a full-access administrator account.

                 

                So, access is not an issue.  I put in CUSTOM DIALOG script steps along the way, displaying info for my debugging because I'm not sure if script debugger would work while in WEBD.  So, I know INPUT fields are up to speed, as is ACCESS.  And the execution fails.  I think the calculation worked in MONITOR/DATA VIEWER too.

                 

                Thanks

                jg

                • 5. Re: Execute SQL and WEB Direct
                  JohnGagne

                  Coherentkris, great suggestion.  While I was driving home, I had a thought.  My Bad.

                  I did not allow WEB Direct access to my data file.

                   

                  WEB File --->   ENGINE File  --->  DATA File

                   

                  I had hoped the by running a script in the Engine with FULL ACCESS privileges, it would be "all powerful."  But you bring up a good question when you ask about another dimension of the access.  This is my first time working with WEB Direct, and it looks like "it" needs access all the way down the line.

                   

                  So, I'll be quiet for a while.  I will re-think and re-engineer my my WEB D portion of the solution going forward.

                   

                  Thank you

                  John Gagne

                  • 6. Re: Execute SQL and WEB Direct
                    JohnGagne

                    SENT TO COHERENTKRIS...BELOW...

                     

                    Coherentkris, great suggestion.  While I was driving home, I had a thought.  My Bad.

                    I did not allow WEB Direct access to my data file.

                     

                    WEB File --->   ENGINE File  --->  DATA File

                     

                    I had hoped the by running a script in the Engine with FULL ACCESS privileges, it would be "all powerful."  But you bring up a good question when you ask about another dimension of the access.  This is my first time working with WEB Direct, and it looks like "it" needs access all the way down the line.

                     

                    So, I'll be quiet for a while.  I will re-think and re-engineer my my WEB D portion of the solution going forward.

                     

                    Thank you

                    John Gagne