1 2 3 Previous Next 44 Replies Latest reply on Jan 12, 2016 8:34 AM by wimdecorte

    ExecuteSQL Returns no Data, no error

    kmtenor

      Hello.

       

      We have built our own Maintenance Management System in Filemaker Pro (Server 13, Go and Pro Advanced 13 Windows clients).  This database manages work orders for three sawmills and about 35 Millwrights.  There are a total of 18,100 records in the Work Order Header table at the moment (current and archived Work Orders).  We have a dashboard screen which is comprised of multiple ExecuteSQL queries that evaluate the Work Order Header table entries, and is driven dynamically by data selected by end users.  For instance, they can look at the data on this dashboard for their facility and one specific department within their facility, simply by selecting drop-downs.

       

      Yesterday, the ExecuteSQL queries that generate the dashboard started failing for one particular facility and department combination.  So far, all others are fine. Troubleshooting has narrowed down the error to possibly one particular record, but there's nothing remarkable or obviously broken about that record.

       

      The record in question was found by repeatedly running queries that worked, using the record creation date as a constraint.  As we moved the date constraint back from "wo_entered_on >= '2/26/2015'" to "wo_entered_on >= '2/25/2015'", the query stopped working.  In ExecuteSQL, there is no error displayed - just a ? where the data should be.  Using the technique outlined here, we also do not get an error - just blank.

       

      So, we turned to ODBC to see what we would get, and the results got very interesting, though no more clear.  Again, we got records when the wo_entered_on was set to 2/26/2015, but as soon as we moved to 2/25/2015, we got the following from the ODBC client (SquirrelSQL, using the JDBC driver):

       

      Error: [FileMaker][FileMaker JDBC] FileMaker error

      SQLState:  08007

      ErrorCode: 27034

       

      That error code is way beyond anything I have found in any Filemaker reference or elsewhere online.  We are trying to avoid deleting the record, as there are related (child) records that are associated with it, as well as other records that are chained to it (it's a preventative maintenance entry, so is repeated every month).  In addition, it's not entirely clear that eliminating the one record we found that is dated 2/25 will solve the problem - though it does appear as though we can query "around" it by changing the date.  We have changed all of the fields used to limit the query (facility, department, status and wo_entered_on) to attempt to eliminate the error record from the query results, and the query still fails.  All four fields being queried are stored with full indexes in the database.

       

      Of course, deleting the record will also not answer the question "what happened?" - asking the question here is an attempt to find out what we might be overlooking.  Obviously, we don't want this to happen again - though this is the first time it has happened in over a year of using this particular solution.

       

      It's important to note that no other constraint combinations are failing for this query.  This one particular combination of facility, department and create_date is the only one producing errors.  We can query with other valid facility/department combinations (even eliminating the date constraint) and get accurate results with all of them.

       

      The query being used to test is:

       

      select

      wo_id

      from woheader

      where wo_closed_flag = 'Open' and

      wo_facility = 'Pittsfield' and

      wo_department = 'Sawmill' and

      wo_entered_on >= '2/25/2015'

       

      When the above is executed, the query fails with the error noted.  When the date is changed to 2/26/2015, we get results.  When the date constraint is eliminated, the query fails as well.

       

      This query has been replicated using FileMaker Find Mode (without the date) and produces 59 records - including the one that appears to be causing the error for SQL.  There is only one record with a "wo_entered_on" timestamp of 2/25, and when we query "around" that date, the only serial number (wo_id) that does not appear in the results belongs to the record in question.  The wo_entered_on field was only used in this query to attempt to isolate the bad record.  The queries that are failing in the production environment are not using the wo_entered_on field.  The above is the minimum amount of constraint that can be applied to isolate the records that may be having issues.

       

      Thanks in advance for any suggestions.

       

      -Kevin

        • 1. Re: ExecuteSQL Returns no Data, no error
          jveilleux

          I've found that using the following instead of a date works better (at least for Oracle):

          To_Date('02/25/2015', 'MM/DD/YYYY')

          • 2. Re: ExecuteSQL Returns no Data, no error
            alecgregory

            I'm a little surprised that the query worked at all using ExecuteSQL with a hardcoded date. Hardcoded dates and timestamps in ExecuteSQL are safest to handle with SQL date functions. So in your example try:

             

            select

            wo_id

            from woheader

            where wo_closed_flag = 'Open' and

            wo_facility = 'Pittsfield' and

            wo_department = 'Sawmill' and

            wo_entered_on >= DATE '2015-02-25'

             

            and see if that helps.

             

            More info in the FileMaker SQL guide in on page 24: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

            • 3. Re: ExecuteSQL Returns no Data, no error
              kmtenor

              Thanks, alecgregory and jveilleux - but the problem with this table does not appear to be the date constraint.

               

              I ran the query as you suggested, alecgregory (this data is inside of FileMaker itself, so Oracle's syntax wouldn't apply), and got the same results - the weird "ErrorCode 27034" coming from FileMaker in ODBC, and a lack of results from ExecuteSQL (just a "?" in the return).  In fact, if I run the query as follows:

               

              select

              wo_id

              from woheader

              where wo_closed_flag = 'Open' and

              wo_facility = 'Pittsfield' and

              wo_department = 'Sawmill'

               

              ...I consistently get the same errors.  I only used that date constraint to test the records and help find the issue - mostly because I have one query that looks at work orders created yesterday that DOES work.

               

              All of this tells me there's something wrong with some portion of the data - but what?  Do I have to take the entire database offline and run a Recover, just to fix what appears to be one record?      The way ExecuteSQL is misbehaving, along with the error message nobody seems to have record of coming from ODBC give me more than a moment's pause about what could be going on with the data in that table.

               

              My concern with deleting the one record we've identified as the likely culprit is that removing it from the indexing might simply cause the next record in line to become corrupt in the index, and eventually cause the entire thing to have to come offline.  I'm just not familiar enough with how the indexes work under the hood - and how they might become corrupted - to know.

               

              I guess I'm hoping someone might jump in who understands the underlying business of how FileMaker stores its data, so I can avoid making things worse in my attempts to fix this one set of queries.

               

              Thanks.

               

              -Kevin

              • 4. Re: ExecuteSQL Returns no Data, no error
                nicolai

                kmtenor Could you place an actual ExecuteSQL query? That might give extra clue

                 

                I though initially that only one record does not work, but you are saying there is a problem with a general query. What sort of problem? Since the simple query failing try to cut it down to see where the problem is:

                 

                select
                wo_id
                from woheader
                where wo_closed_flag = 'Open' and
                wo_facility = 'Pittsfield' and
                wo_department = 'Sawmill'
                
                
                

                 

                 

                try this and if it works start adding WHERE clauses one at a time. this way you can identify the problem column

                 

                select
                wo_id
                from woheader
                
                
                
                • 5. Re: ExecuteSQL Returns no Data, no error
                  siplus

                  I guess you have backups. Copying the whole backup of 26.2 on your machine and doing the query locally works ?

                   

                  In such cases I work on a copy: I set problematic fields to indexing off, close the database, open the database, save a compressed copy, open the compressed copy and turn the indexing on again, and before doing the whole I fsck -fy my own mac.

                  • 6. Re: ExecuteSQL Returns no Data, no error
                    kmtenor

                    With no constraints, I get all of the records in the table (18,099).  I did not expect this - I would have expected no results, except I can rationalize that the "where" clause is not invoked, so neither are the indexes.

                     

                    The other test results are as I expected:

                       With one constraint of "where wo_closed_flag = 'Open'", I get no results.

                       With one constraint of "where wo_facility = 'Pittsfield'", I get no results.

                       With one constraint of "where wo_facility = 'Casco'", I get a result set.

                       With two constraints of "where wo_closed_flag = 'Open' and wo_facility = 'Casco'" I get a result set.

                       With two constraints of "where wo_closed_flag = 'Open' and wo_facility = 'Pittsfield' I get no results.

                     

                    This is feeling more and more like an index issue.  I'm just afraid to find out how deep it goes.

                     

                    Thanks.

                     

                    -Kevin

                    • 7. Re: ExecuteSQL Returns no Data, no error
                      nicolai

                      Well, you can always blame indexes or file corruption, but I would leave it right to the end.  Let's start small -


                      where wo_closed_flag = 'Open'

                       

                      1     check that  wo_closed_flag files data type is set to text.

                       

                      2     wo_closed_flag has to be in woheader table according to your syntax

                       

                      3     try to run Filemaker Find  using layout based on   woheader and set wo_closed_flag to 'Open'. Any results?

                       

                      I do not think indexes play any role on WHERE clauses as they work with unindexed fields as well.

                      • 8. Re: ExecuteSQL Returns no Data, no error
                        beverly

                        Performing the FIND in native FMP may give results that would not appear in SQL "find". Remember that FMP uses "begins with" to find values. So, find (enter)

                             Joe Smith

                        would find

                             Joe Smith

                             Smith, Joe

                             Joella Smithson

                             etc.

                         

                        As SQL "=" would need to have the FMP native find contrained with the EXACT symbol (and possibly quoted) - find (enter)      =="Joe Smith"

                        would find

                             Joe Smith

                         

                        If the eSQL of:

                        With two constraints of "where wo_closed_flag = 'Open' and wo_facility = 'Pittsfield' I get no results.

                         

                        tells us much!

                         

                        Kevin if you try these as native FMP finds using the symbols for exact match, do you get no results as well?

                         

                        BTW, eSQL's WHERE customer LIKE 'Joe%' AND customer LIKE 'Smith%' would be similar to FMP native "begins with" above, though for a large set of records, I would NOT recommend using LIKE and the wildcards.

                         

                        beverly

                        • 9. Re: ExecuteSQL Returns no Data, no error
                          nicolai

                          Thanks Beverly Voth, as always, a valuable contribution.  kmtenor - Beverly referring to point 3 of y post. We are trying to check if there are in fact any records flagged as "Open" and if the value for this field is actually is set to "Open" or something else. My suggestion would miss the wo_closed_flag set to "Open " with the space on the end which will make a difference for the SQL query.

                          • 10. Re: ExecuteSQL Returns no Data, no error
                            user19752

                            In addition, using =="A" in FMP, both "A" and "a" are found, but SQL is case sensitive.

                            • 11. Re: ExecuteSQL Returns no Data, no error
                              nicolai

                              A very good point, so it will not match "open"!

                              • 12. Re: ExecuteSQL Returns no Data, no error
                                kmtenor

                                Thanks, @Beverly Voth and @nicolai.  I had already been through most of the troubleshooting steps you mention.  I tried "LIKE" queries with wildcards to rule out the possibility of a typo in the field, and got the same results.  I've added spaces to the end of constraint strings using "=" in the where clause, and nothing.

                                 

                                I had not tried the "Exact" operator in my FMP native query.  Unfortunately, it didn't make any difference - all iterations of the query return valid result sets in FMP native queries, both with and without the "==" operator.  And, yes, the result sets are the same size both with and without the operator.

                                 

                                Also, wo_closed_flag is a calculated field, and its result is set to text - so there's no chance that it has a space at the end.  I double-checked its settings this morning, just in case.  In my testing yesterday, I thought of both the space at the end of fields and the possibility of case-sensitivity when using SQL, and neither had any effect. 

                                 

                                I feel it bears repeating: the ExecuteSQL and ODBC queries are acting very strangely when these queries are run.  It's not so much that I get empty result sets that could be explained by typos in the query string or spaces in the data where there ought not be any.  The problem is, when I query the database in particular ways, the ExecuteSQL result is a "?" that cannot be resolved to an error, and the ODBC result for the same query is an error that doesn't appear to be documented anywhere.

                                 

                                Having said that, I found a new anomaly in the data today that just be the issue itself.

                                 

                                In attempting to figure out if the record from yesterday (wo_id=18116) was at fault, I set the ExecuteSQL to query directly for it:

                                 

                                select wo_id from woheader where wo_id = 18116

                                 

                                I got the result set you would expect: 18116.

                                 

                                Just to see what would happen, I ran the query as follows:

                                 

                                select wo_id from woheader where wo_id > 18116

                                 

                                I got a long result set, as expected, but included in it was a BLANK LINE where record number 18119 should have been.

                                 

                                When I query for wo_id = 18119 in ExecuteSQL, I get a "?" result that does not resolve to an error.  (sound familiar?).  In ODBC, it comes back as "<null>" - which is not the unknown error, but is still strange.

                                 

                                So, I looked for wo_id 18119 in a FileMaker native Find.  It returns a totally blank record that cannot be edited OR DELETED.

                                 

                                (Cue freaky slasher-movie music here)

                                 

                                I think what we have here is a zombie record.  That said, how do I get rid of it?  A quick search online for "zombie record filemaker" didn't turn up any results.  It doesn't seem as though exporting the table and re-importing it is going to solve it, since I can't delete the record in the first place.  Deleting the table and re-creating it would involve HOURS of re-creating links in the graph (with no guarantee of success).  Or will a simple Recover operation solve it?  And, of course, nothing we do to fix it will answer how it got there in the first place. 

                                 

                                For the moment, it's fine as it is - but obviously this is going to have to be fixed sometime soon, in order to keep it from spreading.

                                 

                                Thanks.

                                 

                                -Kevin

                                • 13. Re: ExecuteSQL Returns no Data, no error
                                  nicolai

                                  kmtenor There is a way to get more meaningful error in ExecuteSQL:

                                   

                                  http://www.teamdf.com/weetbicks/247/unlocking-hidden-error-messages-in-the-executesql-function

                                   

                                  "FileMaker zombie record" - I don't remember this being mentioned in FTS .

                                   

                                  You need to recover the file, it sounds like a corrupted record. You can also try Save a Copy As with a"compacted copy" option.

                                  • 14. Re: ExecuteSQL Returns no Data, no error
                                    kmtenor

                                    Thanks, @nicolai. 

                                     

                                    I had actually linked to that Weetbicks article in my original post - it's like an entire unseen world of information has been unlocked when you use that technique!  Very cool stuff.

                                     

                                    Yes, it does appear as though I will have to schedule some downtime for this database.  It's tough, because Maintenance is a 24/7 job around here.

                                     

                                    Thanks again.

                                     

                                    -Kevin

                                    1 2 3 Previous Next