9 Replies Latest reply on May 12, 2015 6:16 AM by TSGal

    TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work

    larryw

      Summary

      TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      OSX Mavericks

      Description of the issue

      Attempting to use DATEVAL and TIMESTAMPVAL in Select clause of select statement within EXECUTESQL function. Neither are recognized as executesql returns a '?'.

      Example query:
      select dateval('2012-01-01')
      from z_ten

      Where z_ten is a table contains 10 rows.

      In contrast:
      select current_date, current_timestamp, current_time
      from z_ten

      does execute correctly

      Steps to reproduce the problem

      Enter the above two queries in a simple test harness.

      Expected result

      The value '2012-01-01' repeated ten times

      Actual result

      ?

      Workaround

      None

      dateval.png

        • 1. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
          TSGal

               Lawrence Winkler:

               Thank you for your post.

               I am unable to replicate the issue.  Here is what I did:

               1. I set TEST.fmp12 for ODBC access, and in the ODBC Manager, I configured a User DSN (TSGal) and linked it to TEST.fmp12

               2. I opened a new file in FileMaker Pro, and created the following script:

               Execute SQL [ No dialog ; DSN: TSGal ; SQL Text: SELECT DATEVAL('2012-01-01') FROM TEST ]
               Show Custom Dialog [ Get ( LastError ) ]

               ----

               3. I executed the script and LastError showed 0 (zero).

               4. I changed the Execute SQL statement to:  SELECT TIMESTAMPVAL ('2012-01-01 12:00:00') FROM TEST ]

               5. I executed the script and LastError showed 0 (zero).

               Let me know what I'm doing differently than you so I can replicate the issue.

               TSGal
               FileMaker, Inc.

          • 2. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
            larryw

                 No ODBC is being accessed in my example of the non-working code. The table z_ten being referenced is in a local filemaker file. The SELECT statement I used should function against Filemaker as well as ODBC/JDBC tables. 

                  

                  

            • 3. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
              TSGal

                   Lawrence Winkler:

                   Thank you for the additional information.  I'm still unable to replicate the issue.  Here is what I have done:

                   1. In my database file, I have created two tables, "People" and "Payments", both linked together by the common field ID.

                   2. In the Payments table, I also included the Date of Payment and Amount.  I entered 5 records.

                   3. In the People layout, I created a portal to show the five records.

                   4. I created a text field for the SQL statement.

                   5. I created a Calculation field, "SQL Calculation", return Text, with the formula:   ExecuteSQL ( SQL ; "," ; "¶" )

                   6. Into the SQL text field, I entered:   SELECT DATEVAL('2012-01-01'),Amount FROM Payments

                   See the attached screenshot for the result.

                   7. I repeated the step by leaving off Amount from the SQL text field, and it only showed the five static date values.

                   8. I then replaced SQL field with:   SELECT TIMESTAMPVAL('2012-01-01 12:00:00') FROM Payments

                   9. The calculation displayed five instances of:   2012-01-01 12:00:00

                   Let me know what else to try so I can replicate the issue.

                   TSGal
                   FileMaker, Inc.

              • 4. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
                larryw

                     I have been inventing and testing many hypotheses to determine why my code is not working and yours is, from changing capitalization to changing field names to changing table names, and referencing different tables, etc. 

                     I hard-coded into the RESULT calculated text field

                     ExecuteSQL("select dateval('2012-01-01') from z_ten", ",", "¶")

                     This hard-coded code worked correctly! And it worked correctly if a hard-coded the select statement across two lines, placing a carriage return between the select and from clauses.

                     Going back to my original parameterized ExecuteSQL code, I then tried the following (see attached image)

                     select dateval(?) from z_ten

                     and entered 2012-01-01 as the parameter. This WORKED!

                     It seems that the parse of the expression 

                     dateval('2012-01-01')   

                     (using single quotes) might be being mishandled by my version of Filemaker or by some configuration setting. The field aSelect, which contains the text of the query, has the correct and expected value of the select statement in it. Is it possible that my ExecuteSQL implementation is mishandling the single quotes? I looked into the Filemaker's File Options menu, and turned off Smart Quotes, then exited and restarted Filemaker and my Filemaker application, then edited one of the problematic select statements. 

                     Now, the select statements are working!

                     The problem is that FileMaker is not handling Smart Quotes correctly.

                • 5. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
                  TSGal

                       Lawrence Winkler:

                       Smart quotes were not on my radar.  Nice catch.

                       I have forwarded this information to our Development and Testing departments so they can consider translating smart quotes to (standard?) quotes in a future release.  When I receive any feedback, I will let you know.

                       TSGal
                       FileMaker, Inc.

                  • 6. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
                    TSGal

                         Lawrence Winkler:

                         Our Testing department is already aware of this issue as it was originally reported with ExecuteSQL function.  The information has been sent to Development for additional review.

                         I will continue to keep you updated as information becomes available to me.

                         TSGal
                         FileMaker, Inc.

                    • 7. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
                      philmodjunk

                           And will this happen in WIndows?

                           An entry in the Known Bugs List has been linked to this Issue Report. Any Comments/Questions/Suggested Corrections should be posted here or in a new thread. Please do not post such comments to the Known Bugs List thread.

                      • 8. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
                        TSGal

                             PhilModJunk:

                             This issue only occurs under Mac OS X.

                             TSGal
                             FileMaker, Inc.

                        • 9. Re: TIMESTAMPVAL and DATEVAL functions in SELECT clause in executesql do not work
                          TSGal

                          Lawrence Winkler:

                          This issue has been fixed in FileMaker Pro 14.0.1.

                          TSGal
                          FileMaker, Inc.