6 Replies Latest reply on Jul 24, 2012 8:55 PM by fmpros

    ExecuteSQL problem

    KevinMortimer

      Hi

       

      I am getting to grips with new executeSQL function and finding it very useful but i am struggling with a very basic search. I have checked reserved words and there seems to be no conflict. I have this calculation which works fine and returns the result of John

       

      ExecuteSQL ( "SELECT nameFirst FROM contacts WHERE id = 10002" ; NULL ; NULL )

       

      but when i reverse the search it fails...so below calculation returns a ? I am expecting it to return id 10002, why is this failing?...any ideas?

       

      ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = John" ; NULL ; NULL )

        • 1. Re: ExecuteSQL problem
          wimdecorte

          you will need single quotes around John to make the syntax work: 'john'

           

          If you use the parameter function you don't have to worry about it:

           

          ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = ?" ; NULL ; NULL ; "john" )

          • 2. Re: ExecuteSQL problem
            KevinMortimer

            Hi Wim

             

            thanks for that...what is the reason or rule for the the single quotes. I have managed to get it to work but only returns a single id. If i want to get a list of all ids where firstname  = John it only returns 1 id when it should return two ids.

             

            ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = ?" ; NULL ; "¶"  ; "john" )

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL problem
              lhoong

              Kevin,

               

              Try with 'John' - within single quotes:

               

              ExecuteSQL ( "SELECT id FROM contacts WHERE nameFirst = 'John'" ; NULL ; "¶" )

               

              Lee.

              =================

              Binary Assist

              220 Gaines Oak Way

              Suwanee, GA 30024

              Voice: (678) 313-5604

              Internet: lhoong@binaryassist.com

              Website: www.binaryassist.com

               

              FileMaker Certified Developer

              • 4. Re: ExecuteSQL problem
                datastride

                Kevin,

                 

                 

                 

                Unlike a native FileMaker default Find, the ExecuteSQL search is case sensitive.

                 

                 

                 

                I pass this along in case (no pun intended) it might help solve your mystery. (I noticed “john” was all lowercase in your example.)

                 

                 

                 

                You can always use the “Upper” or “Lower” functions to force a match, at the expense of some processing overhead.

                 

                 

                 

                Peace, love & brown rice,

                 

                Morgan Jones

                 

                 

                 

                FileMaker + Web:  Design, Develop & Deploy

                 

                Certifications: FileMaker 9, 10, 11 & 12

                 

                Member: FileMaker Business Alliance

                 

                One Part Harmony <http://www.onepartharmony.com/>  

                 

                Austin, Texas • USA

                 

                512-422-0611

                • 5. Re: ExecuteSQL problem
                  KevinMortimer

                  thanks guys...case sensitive....aaah...now it all makes sense

                  • 6. Re: ExecuteSQL problem
                    fmpros

                    Kevin,

                     

                    A primary key comparator will return the name field of the intended record but when a comparator that is so ambiguous like "John" will return all records with name of John.  Reversing the terms in this SQL significantly changes the returned record set.

                     

                    William