11 Replies Latest reply on Dec 13, 2012 9:18 AM by Steve Wright

    ExecuteSQL weirdness

    pmconaway

      Here is the issue. A colleague in my office is running in ExceuteSQL problems.

       

      This SQL statement works

      Select TypeCodeID from EDRS_103

       

      It returns the value of the LT in records that have this value in TypeCodeID. There are other values that it also returns but as you will see below we are trying to eventually count the number of records that have "LT" as the value in TypeCodeID.

       

      When we try to execute this SQL Filemaker returns ? as the result.

      Select TypeCodeID from EDRS_103 where TypeCodeID = "LT" -- now we have tried the following variations on this sql statement: where TypeCodeID Like "LT" and where TypeCodeID In ("LT")

       

      we have also tried single quotes. We have also tried both uppercase and lowercase LT values. Also this query works Select TypeCodeID from EDRS_103 where RecordID = 103 -- the RecordID field is a numeric field.

       

      It seems like filemaker doesn't like a where clause dealing with a textfield. I'm also figuring that there is something messed up with our query syntax.

       

      Below you ask we have checked to make sure that the ExecuteSQL statement is properly formated. Both of us are stumped any suggestions are helpful.

       

       

      Paul

        • 1. Re: ExecuteSQL weirdness
          Steve Wright

          You would have to escape the quotes used inside of the ExecuteSQL function:

           

          ExecuteSQL ( "Select TypeCodeID from EDRS_103 where TypeCodeID = \"LT\" " ; "" ; "" ; "")

           

          or use the arguments section of the function to pass the criteria in, i.e

           

          ExecuteSQL ( "Select TypeCodeID from EDRS_103 where TypeCodeID = ? ; "" ; "" ; "LT")

           

          Here is a great article, worth reading:

          http://www.filemakerhacks.com/?p=6406

           

           

          • 2. Re: ExecuteSQL weirdness
            beverly

            Paul, the single quote (') is used around literal values (whether in the SELECT or other clauses), although you say you tried this.

             

            See if this is working for you:

                 ExecuteSQL( " SELECT TypeCodeID

                 FROM EDRS_103

                 WHERE TypeCodeID = 'LT' "; ""; "" )

             

            If you need the the count, it would be:

                 ExecuteSQL( " SELECT COUNT(TypeCodeID)

                 FROM EDRS_103

                 WHERE TypeCodeID = 'LT' "; ""; "" )

             

            Your variations:

                 WHERE TypeCodeID LIKE 'LT'

            or

                 WHERE TypeCodeID IN ('LT')

            or

                 WHERE LOWER(TypeCodeID) LIKE 'lt'

             

            Could it be something in the indexing or is the field really text or something else? If you FIND with standard FileMaker find criteria, what do you get?

             

            Beverly

            • 3. Re: ExecuteSQL weirdness
              greglane

              Hi Paul,

               

              In a SQL statement, text values must be enclosed in single quotes. Double quotes can be used to enclose identifiers, like field and table names.

               

              Are you certain TypeCodeID is defined as a text field? The query would fail if it is not.

               

              Greg

              • 4. Re: ExecuteSQL weirdness
                pmconaway

                Steve, after reading your response that got me to thinking. Here is what I tried.

                 

                I opened up the dataviewer debugging window and entered the following executesql Statement

                 

                ExecuteSQL ("Select Count(TypeCodeID) from EDRS_103 where TypeCodeID = 'LT' ";"";"")

                 

                that worked. My question now is why didn't it work before. Let me explain the "before" situation

                 

                We created a table in this solution it has 2 fields

                 

                Querytext  -- text field

                Results  -- calculation field  ( = ExecuteSQL(Querytext;"";"")  - result is a text field

                 

                We then created a layout that allows type in and test sql queryies easily. The original SQL statements I posted came directly from the querytext in this table. So now I'm trying to figure out why I get a ? result in this situation when it works in the data viewer?

                 

                I also want to thank David Ramsey who demoed this solution at our local filemaker pro usergroup meeting.

                • 5. Re: ExecuteSQL weirdness
                  pmconaway

                  Beverly thanks for the detailed response. Please look at my response to Steve Wright. I have determined that the query works but now I'm trying to figure out why my testing solution doesn't.

                   

                  Paul

                  • 6. Re: ExecuteSQL weirdness
                    pmconaway

                    Greg, yes, we checked that. My colleague here is new to SQL. I usually run him through a bunch of scenarios which for me are basic debugging issues. One them was the one you mentioned we also ran the "Select TypeCodeID from EDRS_103" and scanned the results for LT just to make sure there was a record with that value. Also I had him check the field definitions in Filemaker.

                     

                    Paul

                    • 7. Re: ExecuteSQL weirdness
                      beverly

                      Paul, point your colleague to <http://www.filemakerhacks.com/?p=6406> The demo file uses a technique that is slightly different than your own.

                       

                      You said:

                       

                      Querytext  -- text field

                       

                      Results  -- calculation field  ( = ExecuteSQL(Querytext;"";"")  - result is a text field

                       

                      Perhaps the result needs to be = ExecuteSQL ( quote(QueryText);"";"" ) ??

                       

                      Beverly

                      • 8. Re: ExecuteSQL weirdness
                        Steve Wright

                        Hi Paul,

                         

                        Sorry about the first quoted statement, I got carried away with escaping quotes in the wrong places.
                        I only intend on using the parameters and failed to realise the error until Beverly mentioned it below

                         

                         

                        Anyway, out of curiosity, I just created a text field and a calculation field matching yours in a sample file of mine.  I then entered into the Querytext field:

                        SELECT sum(total_Gross) FROM doc_LineItems WHERE doc_ID = 'ABC1'

                         

                        and got the result I was expecting.

                         

                        I also use a custom function for such queries in the calculation engine to ensure field references wont break if a field is renamed etc, thanks to  GFN() here http://www.filemakerhacks.com/?p=4924

                         

                        The output of which is 

                        SELECT sum("total_Gross") FROM doc_LineItems WHERE "doc_ID" = 'ABC1'

                         

                        Which also produces the same results consistently.

                         

                         

                        Are you typing these or copy / pasting from somewhere ?
                        I have not tried but could it be to do with magic quotes (or whatever they are called) if pasting in?

                        • 9. Re: ExecuteSQL weirdness
                          pmconaway

                          Steve, great questions. I will have to ask the guy I work with. My guess is that he is pasting from somewhere else. Maybe seedcode's executesql tool. We will try the same query by typing it in directly. I will also look at the custom function. I like that idea. While my query is different I would expect the same type results you recieved. I couldn't determine what was wrong about the query. Maybe it is one of those "invisible" characters.

                           

                          Paul

                          • 10. Re: ExecuteSQL weirdness
                            pmconaway

                            Thanks Beverly will do.

                             

                            Paul

                            • 11. Re: ExecuteSQL weirdness
                              Steve Wright

                              For what its worth, I just tried the following, which aside from field and table names is more representative of the query you where using.

                               

                              SELECT count(doc_ID) FROM doc_LineItems WHERE doc_ID = 'LT' 

                               

                              I do think theres more to it than first meets the eye perhaps it may be worth hacking together a quick sample file if it continues to give problems.