8 Replies Latest reply on Sep 20, 2015 2:46 AM by raykennedy

    Trying to figure out ExecuteSQL

    raykennedy

      Here is another question on attempting to use ExecuteSQL. These scripts are in a calculating field set to text which is the result that I am looking for. I did verify all values being used exist. Eventually I will be using a dynamic value with phoID but for now I am hardcoding the number 11 for testing purposes which represents a specific record in the PHONE table.

       

      THIS ONE WORKS FINE...

      ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoType = 'Fax'" ; "" ; "")

       

       

      THIS ONE DOESN'T... (phoID is a number)

      ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = 11" ; "" ; "")
      

       

      THIS ONE DOESN'T EITHER... (phoID is a number but I tried putting it in single quotes, just to see if it made a difference)

      ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = '11'" ; "" ; "")
      

       

      Am I missing something obvious?

        • 1. Re: Trying to figure out ExecuteSQL
          BruceRobertson

          One thing you're missing is taking advantage of the ? placeholder.

           

          1. ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = ?" ; "" ; "" ; 11
          • 2. Re: Trying to figure out ExecuteSQL
            erolst

            Have you tried

             

            ExecuteSQL ( "

              SELECT phoNumber

              FROM PHONE

              WHERE phoID = ?

              " ; "" ; "" ; 11

            )

             

            Using the optional parameter (argument) will ensure that the value you're passing is correctly quoted (or not, as it were).

            • 3. Re: Trying to figure out ExecuteSQL
              raykennedy

              Changing the setup as your request.

               

              THIS ONE WORKS FINE...

              ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoType = ?" ; "" ; "" ; "Fax")

               

              THIS ONE DOES NOT WORK...

              ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = ?" ; "" ; "" ; 11)

               

              The result is a....  ?

               

               

              NOR DOES THIS ONE... (just put 11 in quotes although it shouldn't be because it is an interger/number)

              ExecuteSQL ( "SELECT PHONE.phoNumber FROM PHONE WHERE PHONE.phoID = ?" ; "" ; "" ; "11")

               

              The result is a....  ?

               

               

              So my conclusion is that it is not a syntax thing I am doing wrong. For whatever reason the statement executes the where clause on the phoType field but it won't on the phoID field for whatever reason.

               

              I tried it on another number field and it didn't work but it seems to work on text field types. So it works on phoType (text), phoStatus (text), Does not work on phoID (number, indexed) and phoArea (number). The calculation where the SQL script is being run is a calculation field set as "text" because the phoNumber is a phone number set as text (800-555-1212) which is the field I am calling and will be the result when successful.

               

              Is there perhaps a common or novice mistake I am making in regards to dealing with numbers vs text when making these statements / SQL calls?

              • 4. Re: Trying to figure out ExecuteSQL
                beverly

                If you put EvaluationError() around the ExecuteSQL() what do you get?

                 

                Also try quoting the field name in case it is reserved word.

                 

                \"phoID\"

                 

                 

                -- sent from myPhone --

                Beverly Voth

                --

                • 5. Re: Trying to figure out ExecuteSQL
                  raykennedy

                  Thanks for the suggestion on the EvaluationError(). I didn't know about that function to troubleshoot. I did as you asked on on the phoID field I received "8310" which when I look it up it says this... "There is an error in the syntax of the query." I don't really understand as the only thing I am changing is value in the parameter field, the TABLE.FIELD NAME and attempted to quotes,  no quotes etc. Only thing that is consistent is any field that has a number type doesn't seem to work.

                  The other field phoArea which is also a number type field comes out to 8309. I could not yet find out what that code is. Again, the only thing that change was the value in that parameter, quotes and no quotes and the TABLE.FIELD.NAME.

                   

                  I did also attempt to quote the field name to make sure these are no reserved words and that didn't affect the error code.

                   

                  The fields that are text when testing the SQL statement send an error of  0 which means there is nothing wrong and it is working.

                   

                  I appreciate the help, just kind of lost on what could be causing this.

                  • 6. Re: Trying to figure out ExecuteSQL
                    user19752

                    If you use ExecuteSQL function in field calculation, check  the result type is "text".

                    • 7. Re: Trying to figure out ExecuteSQL
                      electon

                      Or try to evaluate it in the DataViewer, this could give you a more descriptive message.

                      It needs to be wrapped in a Let() statement.

                       

                      Let (

                        sql = ExecuteSQL ( "SELECT phoNumber FROM phone WHERE phoID=?" ; "" ; "" ; 11 );

                        ""

                      )

                       

                      BTW if you're querying a single table you don't need to include table name with the field name.

                       

                       

                       

                      In this example I changed phoNumber to phonNumber. The error description appears after you hit "Monitor".

                      Screen Shot 2015-09-20 at 10.33.36.png

                      • 8. Re: Trying to figure out ExecuteSQL
                        raykennedy

                        OK, I have no idea what the fix was. I just replaced the fields and closed out of the database and now it is working. I must have had some odd character or it was a fluke or somehow fixed a mistake without knowing it. In any case, your help was appreciated. Thanks for all the input.

                         

                        I now have a very strong handle on troubleshooting these in the future and became very familiar with the syntax.

                         

                        The more I use FM the more I love it.