1 2 3 Previous Next 37 Replies Latest reply on Apr 20, 2016 1:56 PM by disabled_morkus

    ExecuteSQL WHERE Clause with two UPPER clauses

    taylorsharpe

      I am working on a FMPA 14 with a file on FMS 14 and doing an Execute SQL where there are two UPPER clauses in the WHERE statement as below.  This results in a "?".  However, if I take out one of the UPPER clauses, then it works.  I tried one with UPPER and one with LOWER and that didn't work either.  So it looks like in the WHERE part of the SQL statement, you can only use UPPER or LOWER once and not more than once.  It sure seems like a bug to me.  My work around was to make a calc field in the People table of the last name that made an UPPER via a FileMaker calc stored field.  Anyone with a better suggestion or workaround?

       

      Let ( [

      F1 = WebCheckIn::NameLast ;

      F2 = WebCheckIn::NameFirst ;

      F3 = "SELECT

                     ID

                FROM

                     People

                WHERE

                     UPPER ( \"Name First\" ) LIKE ? and

                     UPPER ( \"Name Last\" ) LIKE ? " ;

      F4 = ExecuteSQL ( F3 ; ¶ ; ¶ ; "%" & Upper ( F2 ) & "%" ;  Upper ( F1 ) & "%" )

      ] ; F4 )

        • 2. Re: ExecuteSQL WHERE Clause with two UPPER clauses
          electon

          Maybe you can capture the error in the data viewer?

           

          Let ( [

          F1 = WebCheckIn::NameLast ;

          F2 = WebCheckIn::NameFirst ;

          F3 = "SELECT

                         ID

                    FROM

                         People

                    WHERE

                         UPPER ( \"Name First\" ) LIKE ? and

                         UPPER ( \"Name Last\" ) LIKE ? " ;

          F4 = ExecuteSQL ( F3 ; ¶ ; ¶ ; "%" & Upper ( F2 ) & "%" ;  Upper ( F1 ) & "%" )

          ] ;

          If ( F4 = "?" ; "" ; F4 )

          )


          Hit the "Monitor" button.

          • 3. Re: ExecuteSQL WHERE Clause with two UPPER clauses

            Agreed, I would take the query and get it working in the data viewer first. If you still get the maddening, unhelpful, totally useless "?", it's probably time for the next step like using a SQL plug-in that gives you reasonable (that is, some) error information.

             

            I usually rely on a separate Java environment's data viewer for that so I get SQL "intellisense" as I type, get error information in real time, etc., but I understand there are plug-ins for FM that can help.

             

            - m

            • 4. Re: ExecuteSQL WHERE Clause with two UPPER clauses
              taylorsharpe

              Thanks everyone... Yes, I was working on it in the data viewer and getting the "?".  I tried this on some other searches in this same database and it failed.  HOWEVER, I went to another database and tried searches with WHERE and multiple UPPERs and it worked.  Hmmmm.... sounding like some file corruption in the fields I was searching on. 

               

              Thanks for testing and letting me know you guys weren't haven't problems. 

              • 5. Re: ExecuteSQL WHERE Clause with two UPPER clauses
                erolst

                Taylor Sharpe wrote:

                Thanks for testing and letting me know you guys weren't haven't problems.

                 

                To paraphrase the FC Liverpool anthem:

                 

                You'll never code alone!

                • 6. Re: ExecuteSQL WHERE Clause with two UPPER clauses

                  I really doubt you have field corruption, but it's possible I guess.

                   

                  In every case I've ever had the "?" and then moved over to my Java console to check out the SQL, I quickly found the issue in a minute or less with a real error message.

                   

                  The data viewer is virtually useless in these situations -- unless you have all day to experiment and ask the data viewer: "Here, do you like this variation of the other 1,000 queries I've asked you to evaluate? No, how about this one?", etc.  To wit, the data viewer is SQL brain dead: It doesn't help you complete the SQL using fields in your database, it doesn't give you actual (useful) error messages, it forces you to use the ExecuteSQL for any kind of data work, etc.

                   

                  If you're really doing SQL, get a better SQL tool (FM plugin or whatever).

                   

                  Beverley listed some good SQL plug-ins recently.

                   

                  My two cents.

                   

                  - m

                  • 7. Re: ExecuteSQL WHERE Clause with two UPPER clauses
                    taylorsharpe

                    FYI, there are other reasons why I suspect data corruption beyond this SQL issue having to do with some bad things done to the database.  So there is a little more to the story that I had not previously mentioned  But I would normally agree with you that a single select problem rarely means data corruption.

                     

                    I wish we had a better data viewer console and I'm always trusted working with arrays that you can only see a few lines of data on in the data viewer.  It would be nice to have something like SequelPro of Navicat to connect to FM to test SQL, etc.  So I appreciate your comments, Mokus.

                     

                    If you want to get error codes in the data viewer, there are tricks like this one:

                     

                    FileMaker Custom Functions | Displaying Function sql.debug

                     

                     

                    PS:  My favorite and most used SQL plugin is MBS.  And it does so much more than just SQL. 

                    • 8. Re: ExecuteSQL WHERE Clause with two UPPER clauses
                      wimdecorte

                      One of things I do is to also go to the extra step to abstract out the field and table names with some of the available CFs.  And if you do this ExecuteSQL() calls in a script then set the sql query into a variable so you can inspect it while debugging.

                       

                      It may help in debugging this kind of syntax errors...

                      • 9. Re: ExecuteSQL WHERE Clause with two UPPER clauses

                        No prob, Taylor. Glad to help.

                         

                        Using the remote data console via JDBC against the FM database is exactly what you're describing above. Live field hints as you type (via dynamic drop down), real errors, easy exporting of results, etc. No "ExecuteSQL", just SQL, etc.

                         

                        I had posted those screenshots on the forum, but I just looked and didn't see it. Fortunately, I saved a screenshot of what the remote (but live) FMP connection idea can do for you:

                         

                         

                        ----

                         

                        In any case, I was going to offer that if you posted a FMP application with representative (but sanitized, of course) data, I would do the SQL using the remote Java IDE.  Since the query wouldn't be in a program, I'd remove the replaceable parameters and do the SQL directly.  Just verify that the query fails for you with that sample data.

                         

                        Just an offer.

                         

                        I'd post my results.

                         

                        Have a good one.

                         

                        - m

                        • 10. Re: ExecuteSQL WHERE Clause with two UPPER clauses

                          Sorry, the entire screenshot didn't get posted. My mistake.

                           

                          Here 'tis:

                           

                          FileMaker SQL:JDBC Example.png

                          • 11. Re: ExecuteSQL WHERE Clause with two UPPER clauses
                            taylorsharpe

                            I've tried, well not hard, but tried to get Squirrel to work with FileMaker via the JDBC driver, but didn't get it working.  Supposedly RazorSQL works with it too, but was reluctant to shell out $99 for it.  FYI, I'm doing my work on a Mac, which has less tools than Windows for SQL Tools. 

                            • 12. Re: ExecuteSQL WHERE Clause with two UPPER clauses
                              taylorsharpe

                              Morkus, as long as you are talking this, it sure would be nice to have temporary SQL tables... to perform a SQL on a temporary table.  Right now I create arrays in FileMaker, but if I need to search the array, I have to loop through it which is a pain compared to making a SQL statement.  Oh well, a dream. 

                              • 13. Re: ExecuteSQL WHERE Clause with two UPPER clauses

                                Arrays in FileMaker? I didn't think FileMaker supported any real data structures other than "table" or maybe a virtual list. Perhaps that's what you mean?

                                 

                                I fully agree that the imperative style (procedural code) isn't as powerful as SQL's declarative model. Java 8 also supports the declarative model in code now! You can issue commands on collections that do many, often linked, operations. All without writing class code.

                                 

                                I'm a mac guy too so all the tools I use are on the mac. I've yet to figure out why FM plug-ins and tools are so wickedly expensive relative to other environments. I'm assuming, but could be wrong, that FM is a niche product with not that many users so the plug-in vendors need to charge more to, well, eat.

                                 

                                Hopefully, FM will add some decent SQL tools (Data Viewer, ...) in 15 (or 16, or ....).

                                 

                                Thanks for your replies.

                                 

                                - m

                                • 14. Re: ExecuteSQL WHERE Clause with two UPPER clauses

                                  Didn't know about RazorSQL. Just played with it a bit. Seems like a nice tool. The GUI doesn't seem quite as Mac-polished as my IDE, but the functionality looks good. Defintely a tool I would consider if I didn't already have a way to do SQL productively on FM.

                                   

                                  I didn't test it if offered SQL support as you enter queries (field name suggestions, and such).

                                   

                                  I noticed that when I deleted RazorSQL, and even after Hazel removed program remnants, there was s still a Razor directory and a couple other files I needed to remove manually. No big deal.

                                   

                                  I've bookmarked RazorSQL.

                                   

                                  Thanks Taylor.

                                   

                                  - m

                                  1 2 3 Previous Next