4 Replies Latest reply on Apr 29, 2017 6:51 AM by carlos_m26

    ExecuteSQL() doubt

    carlos_m26

      Hi everyone,

      I have a problem with ExecuteSQL() in a search script...

      in a variable I put this :

       

      ExecuteSQL (

      "SELECT pais FROM permiso_paises WHERE cuenta_usuario = CURRENT_USER "

      ;"";""

      )

       

      is a simple SELECT to an other table (in the same file), this step works all the time when I (full access) perform the script...but when some other user (not full access) perform the search script....this step dosen't work all the time, somes does but other dosen't, and I dont know why... it is pretty frustrating

       

      the executesql() run when filemaker is in  find mode... could be that my problem?? but I have not problem with my account... do you need special privilege to use it????

       

      I'm using FMPA 14

       

      I hope you can understand me and I apologize for my poor english...

        • 1. Re: ExecuteSQL() doubt
          philmodjunk

          When it doesn't work, how does it fail? Do you get an empty result? Do you get a ? result?

           

          What limits have been put one what records or tables can be accessed by these users with other privilege sets?

          • 2. Re: ExecuteSQL() doubt
            greglane

            Text comparisons with ExecuteSQL are case sensitive. A user could log in with "user name" or "User Name" and your SQL statement would return different results. Try:

             

            ExecuteSQL (

            "SELECT pais FROM permiso_paises WHERE LOWER(cuenta_usuario) = LOWER(CURRENT_USER) "

            ;"";""

            )

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL() doubt
              beverly

              Go with Greg's answer. I merely wanted to answer your final questions:

              • "in find mode"? - setting the variable with a calculation (the function, ExecuteSQL, in this case) does not matter even in Find Mode.

              • "special privilege"? - no privilege on the calculation, but the permission of the fields "pais" & "cuenta_usuario" and the table "permiso_paises" may be limited in your Security set up (only you would know).

               

              & see this comment for suggestions on how to check for errors with ExecuteSQL. (require FMPro Advanced)

               

              Your English was good enough. Feel free to use whatever language you want (both if you think that helps) when asking this forum.

              beverly

               

              EDITED: p.s. I wanted to add: I probably would set variables before entering find mode, in case I need the values from parameters.

              • 4. Re: ExecuteSQL() doubt
                carlos_m26

                Thank you guys!

                 

                case sensitive, my god, I don't even think about it